Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008

    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.

    Any help will be much appriciated, thanks

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    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.

    You ever used linked tables?
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Sorry, it's not straight copy and paste. However, it is has been used right variables.

    cn is not used because I was trying to use strSQLServer variable in my query, which holds that string.

    I have used linked tables manually, but not programmatically.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts