suggestion to copy data from Sql Server into Access database

[sal21]

scenario: I have a Sql Server in Rome and a shared server dir (with the access database) on a Server in New York. I have permission on sever dir and databse. The database table on sql Server have exactlly the same structure of Access table database. (the table on Sql Server have 200.000 records and 48 columns). Now i want to recopy all records from Rome(sql server) to NewYork (Access database). Wath is the best way in terms of time to recopy all records fom sql server to access database. note: I want to use in my app the ADO drive and vb6 classic. naturally other way are welcome:-)

[Lost User]

sal21 wrote:

Wath is the best way in terms of time to recopy all records fom sql server to access database.

One option is to Replicate[^] it :)

I are Troll :suss:

[sal21]

Hi Eddy.... tks for tip but sure not for me i know only VB6... Can you post a code to use with vb6 classic? Tks for patience. Sal.

[sal21]

Hi Eddy.... tks for tip but sure not for me i know only VB6... Can you post a code to use with vb6 classic? Tks for patience. Sal.

[Lost User]

sal21 wrote:

Hi Eddy.... tks for tip but sure not for me i know only VB6... Can you post a code to use with vb6 classic?

Replication is done over SQL Server, and it doesn't speak VB6, only SQL. You should be able to talk to your Access-database using VB6 and ADO without any problems. Alternatively, you could write some code in VB6 to do the import manually; creating an ADODB.Connection to SQL and writing it in your Access database. I'm going to guess that that's the code that you're interested in?

I are Troll :suss:

[sal21]

I have tested this way but the code run very, very slow... Tell me if thei prob is your and post me a vb6 code:-)

[Lost User]

sal21 wrote:

I have tested this way but the code run very, very slow...

sal21 wrote:

(the table on Sql Server have 200.000 records and 48 columns).

SQL Server and Microsoft Access don't store their data in "exactly" the same way. Moving 200k records takes time. How long did it take? Replicating to a linked server is the easy and painless way. Yes, it's possible to do it manually. Yes, you can read the data from SQL Server, and update it in Microsoft Access. That it's possible doesn't make it a good idea though. Do you only need a read-only copy, or is it required to update the SQL Server-data with the changes in the Access-database? If you're satisfied with a read-only copy, then you could simply copy each table into an empty Access database. Should indeed be faster than replication. Your best option would then be to execute an select into or something similar, from your SQL Server into your linked server. That would at least be a lot easier than reading data from connection 1 and writing it into connection 2.

sal21 wrote:

Tell me if thei prob is your and post me a vb6 code

I can't write any specific VB6-code without seeing the physical server. I'll require a plane-ticket from Maastricht to Rome and lot's of coffee :cool:

I are Troll :suss: