how to save data from ms acess front end to ms sql backend?

Question

how to save data from ms acess front end to ms sql backend?

Answer 1

It is nearly always better to use linked tables. This makes life much easier because you can update the SQL Server tables and views in more or less the same way that you would update an Access table. Another good way is to use pass-through queries : http://support.microsoft.com/kb/303968

That being said, here are a few rough notes.

The easiest way to get a useful ODBC string is to link a table using the wizard, you can then look it up using CurrentDB.TableDefs("NameOfLinkedTable").Connect

You can use the string like so:

Dim db As Database
Set db = CurrentDb

strConnect = "ODBC;Description=Test;DRIVER=SQL Server;" _ 
           & "SERVER=ServerAddress;Trusted_Connection=Yes;DATABASE=DBName"

strSQL = "INSERT INTO [" & strConnect & "].[SomeSQLServerTable] (ID, AText) " _
       & "SELECT a.ID, a.Descr FROM SomeAccessTable As a " _
       & "LEFT JOIN [" & strConnect & "].[SomeSQLServerTable] s " _
       & "ON s.ID=a.ID " _
       & "WHERE s.ID Is Null"

db.Execute strSQL, dbFailOnError
Debug.Print db.RecordsAffected

You can also update with ADO. There is a great deal of information to be found here: http://msdn.microsoft.com/en-us/library/ms130978.aspx You can get connection strings here: http://www.connectionstrings.com/

And some of the odder things you can do:

Dim cn As New ADODB.Connection
Dim RecsAffected As Long
Dim scn As String, sSQL As String

''Using SQL Server connection native client
scn = "Provider=SQLNCLI10;Server=ServerAddress;" _
    & "Database=DBName; Trusted_Connection=yes;"

cn.Open scn

sSQL = "INSERT INTO NewSQLServerTable " _
     & "SELECT * FROM OPENROWSET " _
     & "('Microsoft.ACE.OLEDB.12.0','C:\docs\ltd.mdb';'Admin';, " _
     & "'SELECT * FROM OldAccessTable')"

cn.Execute sSQL, RecsAffected

Debug.Print RecsAffected

Or

Dim cn As New ADODB.Connection
Dim RecsAffected As Long
Dim scn As String, sSQL As String

''Using Jet connection
Set cn = CurrentProject.Connection

strConnect = "ODBC;Description=Test;DRIVER=SQL Server;" _
           & "SERVER=ServerAddress;Trusted_Connection=Yes;DATABASE=DBName"

sSQL = "INSERT INTO [" & strConnect & "].NewSQLServerTable " _
     & "SELECT * FROM OldAccessTable"

cn.Execute sSQL, RecsAffected


''Using Jet & an external mdb
sSQL = "INSERT INTO [" & strConnect & "].NewSQLServerTable " _
     & "SELECT * FROM OldAccessTable IN " _
     & "'C:\docs\ltd.mdb'"

cn.Execute sSQL, RecsAffected

Debug.Print RecsAffected