I have a table called AccessCORRES (with anywhere between 5000 to 15000
records) in a MS Access database and a table called SQLCORRES in a SQL
Server 6.5 database. Each field name in the Access table has a corresponding
field name in the SQL Server database and is of the same type, length, etc.

Currently a VB5(SP3) application runs an update query to add each individual
record by looping through the Access table and doing an execute against the
ODBC connection conCIMS.Execute "Insert Into SQLCORRES Fields ...
Values...". So 15000 records means 15000 Insert statements.

What I need to do instead is append the contents of the Access table to the
SQL Server table using one SQL Insert Into statement. I can do it fine
inserting into another MS Access database but I'm having trouble coming up
with the proper syntax to insert into the SQL Server database.

If I run the SQL against the Jet database, I need syntax for the 'IN'
portion of the statement. i.e. what syntax replaces [SQL Server 6.5
database]? Nowhere can I find the proper syntax for inserting into an ODBC
destination. The online help does not help. Do I use some variation of the
Connect string?

  Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
  Set dbAccess = wrkJet.OpenDatabase(App.Path & "\ccm2cims.mdb", True)
  s_SQL = "INSERT INTO SQLCORRES IN [SQL Server 6.5 database]"
  s_SQL = s_SQL & " Select AccessCORRES.* From AccessCORRES"
  dbAccess.Execute s_SQL, dbFailOnError

If I run the SQL against a ODBC connection, will something like the
following be the way to go?,

  Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "sa", "sa", dbUseODBC)
  Set conCIMS = wrkODBC.OpenConnection("Connection1", dbDriverPrompt)
  s_SQL = "INSERT INTO SQLCORRES "
  s_SQL = s_SQL & " Select AccessCORRES.* From AccessCORRES IN [MS Access
database]"
  conCIMS.Execute

Any suggestions on the best way to append multiple records from a table in
an Access database to one in a SQL Server database?  Even URLs for VB FAQs
would be appreciated. I've checked about a dozen VB sites and have not seen
this item covered. I hope it is feasible.

TIA,

Rick

--
****************
Remove .spamfree from my e-mail address to respond.
Visit me at http://www.*-*-*.com/ ~racefan/.
****************

Why don't you create a link to the SQL data table in Access?  Then you can
make a connection to the Access database, and do "INSERT SQLCorres SELECT *
FROM AccessCorres"

Is it necessary to do this procedure more than once or are you merely
upgrading?

tu