Transfer data from SQL server to Access
Hi,
You could use different approaches to do that. First is to use DTS COM
model, which comes with SQL Server. Second is to use OLEDB provider for Jet.
Here is an small example with Jet
Dim loConnection As ADODB.Connection
Dim lcSQL As String
Set loConnection = New ADODB.Connection
loConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=place path to mdb file here;"
lcSQL = "SELECT * INTO Products_Backup FROM [odbc;Driver={SQL
Server};Server=(local);"
lcSQL = lcSQL & "Database=northwind;Uid=sa;Pwd=;].Products"
loConnection.Execute lcSQL, , adExecuteNoRecords
loConnection.Close
--
Val Mazur
Microsoft MVP
Quote:
> Dear NG,
> I am using Access 97 SR-2 and ADO 2.0 and I want to create a new table in
> Access and insert in this new table data from a SQL Server.
> With the help of ADO I get the data from the SQL server, but how can I
> insert this data into an Access table.
> Please see the example of my code below! The code is running in Access,
> because Access will be the front-end.
> Thank you very much in advance for any help!
> With best regards,
> Sabine
> Example:
> Sub MainTestAccessToSQL()
> Dim adocon As Object
> Dim adocmd As Object
> Dim adoparam As Object
> Dim ado_set As Object
> Dim Gb As String
> Dim PPT As String
> Dim Kat As String
> Dim LfdNr As String
> Dim Ver As String
> Dim Rev As String
> Dim bLogin As Boolean
> Dim DokID As String
> bLogin = False
> If iLogInSQLServer(adocon, adocmd, adoparam) = False Then
> MsgBox "Could not login"
> GoTo Ende
> Else
> bLogin = True
> End If
> Gb = "3"
> PPT = "OPSC_TEI"
> Kat = "SDE"
> LfdNr = "1"
> Ver = "1"
> Rev = "0"
> Set ado_set = CreateObject("ADODB.Recordset")
> ado_set.Open "SELECT * FROM OPDS", adocon
> If Not ado_set.EOF Then
> MsgBox ado_set!titel
> End If
> '### How to insert the data of ado_set into an Access table?
> Ende:
> If bLogin = True Then
> Call iLogOutSQLServer(adocon, adocmd, adoparam)
> End If
> End Sub
