Unanswered: transfer data from SQL Server query to Access Table
I am trying to insert data generated in SQL Server query to Access table. I am using VB in Macro. I have this code which doesn't work:
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim strSQL1 As String
Dim strSQL2 As String
cn.Open "Provider=SQLOLEDB;Data Source=LBNSQLS009\PROFILE7;User ID=sysmgr;Password=ssysmgr;Initial Catalog=profile7;"
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\01 - Monitoring and Reporting\13 - Monitoring Team\Arkady\RPM\Queries\WeeklyActivityReports\CopyToDatabase.mdb"
strSQLServer = "Provider=SQLOLEDB;Data Source=LBNSQLS009\PROFILE7;User ID=sysmgr;Password=ssysmgr;Initial Catalog=profile7;"
strSQL1 = "INSERT INTO User2(Test1) " & _
"SELECT person_ref FROM [" & strSQLServer & "].person"
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open strSQLTest1, cn2, adOpenKeyset, adLockPessimistic
The reason I have Recordsets is because that's i used to retrieve my queries from SQL Server. What i really want to do is to ignore the recordsets and transfer data straight within one query as above.
Is that a straight copy and paste from Access? Because strSQLTest1 is used, but not declared or set.
strSQL2 is declared bu unused.
strSQLServer is not declared.
cn is opened, but not used.
You don't want to use recordsets here - you are correct.