"Insert Into" SQL Server 6.5 from Access 97 using VB5? 
Author Message
 "Insert Into" SQL Server 6.5 from Access 97 using VB5?

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 = s_SQL & " Select AccessCORRES.* From AccessCORRES IN [MS Access

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.



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

Tue, 14 Aug 2001 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. "Insert Into" SQL Server 6.5 from Access 97 using VB5?

2. "Insert Into" SQL Server 6.5 from Access 97 using VB5?

3. Access 97 and SQL Server 6.5

4. Need help Access 97 vs SQL Server 6.5?

5. Access 97 vs SQL Server 6.5

6. Help - VB5 / SQL 6.5 / Access 97

7. Locking in VB5 and SQL Server 6.5 using stored procs

8. Using VB5 and Sql Server 6.5

9. connecting to MS SQL Server 6.5 using VB5

10. How to..Using VB5 with Sql server 6.5

11. VB5 - Access 97 - SqlServer 6.5 - non-disappearing tmp file

12. "97"-Style Toolbar OCX for VB5?


Powered by phpBB® Forum Software