Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    18

    Unanswered: Copy from local table to SQL database via DSN less connection

    What is the VBA script to copy selected records from my local Access database to SQL database via DSN less connection. I have all the connections set but can't seem to figure out how to make the following script work:

    INSERT INTO remoteTable (VALUE1, VALUE2, VALUE3)
    SELECT LOCAL1, LOCAL2, LOCAL3 FROM localTable WHERE LOCAL3 is True

    Or, is there a better way of doing it. Thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Copy the following code into notepad and save the file with a .vbs extension:

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    'To connect to an Access table (although best to use the UNC path.)
    MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HelpForOthers\CustomersXPSample.mdb"

    'To connect to an ODBC SQL Server table
    MyConn.open = "DRIVER={SQL Server};"_
    & "SERVER=SQLSERVER;"_
    & "DATABASE=PKTime;"_
    & "OPTION=35;"

    Set RS = MyConn.Execute("UPDATE tblUsers SET LoginStatusField ='" & "LoggedIn" & "' WHERE LoginIDField ='" & varUserName & "'")
    ' or put whatever SQL code in place of the statement above
    'ex: set RS = MyConn.Execute("DELETE * from MyTable")
    'ex: set RS = MyConn.Execute("INSERT INTO MyTable (Field1, Field2) SELECT MyAccessLinkedOracleTable.Field1, 'MyAccessLinkedOracleTable.Field2
    'FROM MyAccessLinkedOracleTable")

    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing

    retval = msgbox("Table Updated")

    If the SQL Server table is linked into MSAccess, you can just connect to the Access mdb and use that as MyConn.Open. Otherwise, to connect directly to the SQL Server table, use the other MyConn.Open.
    Last edited by pkstormy; 05-06-08 at 15:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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