How to import data from Access Table to SQL Server using SQL queries

[skimura]

I would like to write a client application that reads the data from an Access table to an SQL Server using DAO and Visual Basic. How can I do this?

Silvia

 

[AmiDenise]

Sylvia,

Do you want to update the SQL table with the data in the Access table? Do you want to create a VB app that reads data from Access & SQL? What versions of Access and SQL are you using or considering using?

A little more information would make it much easier to provide pointers.

Let me know if I can help in any way. Good Luck!
Ami, MCP
AmiDenise@yahoo.com

 

[skimura]

I would like to create a VB app that reads data from Access 2000 or 97 and updates an SQL table using this data. The version of the SQL Server is 2000 or 7.5.

thanks,
Silvia K.

 

[shmiller]

Are you familiar with ADO? That would be the easiest way.

dim cnAccess as new adodb.connection
dim cnSQL as new adodb.connection
dim rsAccess as new adodb.recordset
dim rsSQL as new adodb.recordset
cnAccess.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=;User Id=admin;Password=;"
cnSQL.open "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI; Persist Security Info=True;" &_
"Initial Catalog=;Data Source="
'or whatever your connection strings are
rsAccess.Open "select ..",cnAccess,adOpenStatic, & _
adLockOptimistic
rsSQL.Open "select ..",cnSQL,adOpenDynamic,adLockOptimistic
While Not rsAccess.EOF
rsSQL.AddNew
rsSQL!fieldname1 = rsAccess!fieldname1
rsSQL!fieldname2 = rsAccess!fieldname2
rsSQL.Update
rsAccess.MoveNext
Loop

This is one easy way to do it. If you use stored procedures for your SQLServer inserts, you would get rid of the rsSQL and do it something like this:
While Not rsAccess.EOF
cnSQL.Execute "exec procStoredProcName " & rsAccess!field
rsAccess.MoveNext
Loop