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

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

Post by RaceFa » Sun, 28 Feb 1999 04:00:00



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

 
 
 

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

Post by Rick Sawtel » Sun, 28 Feb 1999 04:00:00


The quick and dirty is to link your SQL Server table to your access
database.  Then you can treat it as if it were an access database.

Another method is to continue using the VB program, but wrap the whole thing
up in a transaction.  (This is slow and ugly though, but does all of the
inserts in the copy buffer and then writes them to SQL Server once.)

Anyhow, the answer to your question lies here:   Take a look at these query
properties in the Access Online Help.

DestConnectStr, DestinationDB, DestinationTable Properties

Anyhow, try the following as part of your insert into statement.  Make sure
you create a DSN and specify a database name:

s_SQL = "INSERT INTO SQLCORRES IN
[ODBC;UID=sa;PWD=sa;DATABASE=xxxxxxx;DSN=xxxxxxx;]"
s_SQL = s_SQL & " Select AccessCORRES.* From AccessCORRES"
dbAccess.Execute s_SQL, dbFailOnError

Rick Sawtell MCP, MCT, MCSD


 
 
 

1. "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 = "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.magma.ca/~racefan/.
****************

2. qqqqqqqqqqqq

3. FRAGMENTATION QUESTION (Tables with more than 16 MM pages)

4. Dynamicly open Access 2000 database

5. SQL Server 7 and linked tables in Access 97 ( "#S_C_H"-Problem )

6. Is it normall ?

7. Import Access 97 data into SQL Server 6.5 using Transac-SQL

8. MS ACCESS 97/SQL server 6.5: deadlock problems when accessing server tables

9. Help - VB5 / SQL 6.5 / Access 97

10. "HELP"-Error 3112 while compacting in Access 97