Help with Linking Tables

[Al Bourque]

I am trying to come up with a way to duplicate an old DAO technique using ADO. I want to get a large amount of data (through a VB App) from a SQL Server or Oracle DB into an MS Access DB. In the past, I used DAO and performed a query like
"Select into x in "c:\Accessdb.mdb"". This would create the destination table in the AccessDB and quickly copy the data. I'm trying to perform a function similar to this using ADO, but I'm not sure how to do it. I though of programmatically linking my source tables in my AccessDB, but I can't see how to do that as well.

[darre1]

just as a workaround you could export the data to excel then import it into access. no fuss?

[Animate_This]

Just solved a similar problem myself, try this -

place the following in your Declarations section

Public g_sDbPath As String 'Source path of database to copy to

Public g_sOBJSource As String 'Source path of database to copy from
Public Conn As New ADODB.Connection

Place the following in your sub routine

With Conn
.Provider = "Microsoft.jet.oledb.4.0"
.Mode = adModeReadWrite
.ConnectionString = "data source=" & g_sOBJSource
.Open
End With


strSQL = "SELECT * Into [new-tablename] In ' " & g_sDbPath & " ' FROM [old-tablename];"
Conn.Execute strSQL


Hope this helps...