Solved: SELECT INTO from remote table question

[alimcpill]

I just can't figure this out:

I have an access application and a remote sql server. If I link to table on the server via odbc using a dsn I can do a simple

"SELECT [whatever] INTO [LocalAccessTable] FROM [LinkedSqlServerTable]"

to populate a local copy of the table. However, I often read that the OLEDB provider for sql server gives much better performance than odbc, but I can't work out how I can run a similar query to copy data from the server to a local access file using this sort of connectivity. I know I could achieve this by opening a recordset on the remote table and creating a load of sql insert statements, but it seems like a lot of overhead.

I guess what I really need to do is this:

"SELECT [whatever] INTO [LocalAccessTable] FROM [ConnectedViaAdoButNotActuallyLinkedSqlServerTable]"

Is this possible? :dunno
Hope that makes sense...

[XLGibbs]

If you are copying the whole table, the ODBC connection should work fine for moving the data into your local Access table.

Sometimes, if you are using both ODBC and the OLEDB provider, some minor performace degrading is found due to the additional overhead, but I normally only see this when using SQL Server to manipulate access tables externally using queries within the Access DB.

I am not sure if you can use..

Application("strDBConnectionString") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {SQL Server DSN}



TO do what you need...I have not tried using other than ODBC Conn Str for writing to Access from a SQL server (within Access VB)

[matthewspatrick]

In addition, if all you want to do is clone the SQL Server table into your Access file, you could use DoCmd.TransferDatabase...

Patrick

[alimcpill]

Excellent advice from the east coast, many thanks to you both.

I think the transferdatabase option is close to what I want, I hadn't even though of it.

cheers guys, thread solved...