How to append data from a SQL Server table to an Access table?

Question

I am trying to write a query in MS access to open a connection to a local SQL Server and then to import select tables into MS Access.

My code runs until the Cn.Execute statement. I get

Run-time error '-2471765 (80040e37)' [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name 'dbo_SQLServertable'.

I need to import additional tables so I need a code that will work when I change table names.

Private Sub Command28_Click()    
        
    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    im Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    Server_Name = "" ' Enter your server name here
    Database_Name = "Test" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
       
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"
    
    Cn.Execute "INSERT INTO Access Table SELECT dbo_SQLServerTable.* FROM dbo_SQLServerTable;"
    
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing

I made changes and I get a new error message

Run-time error '-2147216900 (80040e14)' [Microsoft][ODBC SQL Server Driver][SQL Server] Cannot Insert the value NULL into column 'DiagnosisOrdinal', table 'Office.dbo.Test' column does not allow nulls. Insert fails.

It appears that my insert statement is still referencing (or trying to reference) a table in the SQL server. 'Office' is the database name that I am pulling from.

Do I have to close the connection and then paste the data into my local Access table? Will I then have to re-open and close the connection if I want to do this for multiple tables?

I changed my execute statement from

Cn.Execute "INSERT INTO Access Table SELECT dbo_SQLServerTable.* FROM dbo_SQLServerTable;"

to

Cn.Execute "INSERT INTO Test(VisitID, Provider) SELECT VisitID, Provider FROM dbo.SQLServerTable;"

Answer 1

You don't want the table prefix in your SELECT from the SQL table. Just do SELECT * FROM dbo_SQLServerTable; Best practice, though, is not to use SELECT * but rather specify the columns in case the table schemas ever change.

Answer 2

There's a few suggestions for this particular issue

1. Pass thru queries
2. Linked Tables from SQL Server. Youll prolly have to set up a dsn file which isnt too terribly difficult.
3. Or handle it directly in SQL Server Insert into Access from SQL Server
4. ODBC connection via VBA (what youre doing and seemingly the most convoluted)

All of these approaches will work fine. I suggest linked tables so you dont duplicate data but thats a cinsderation fro you since I dont knwo the requirements of the project.

Answer 3

dbo_SQLServerTable is an Access table name, not SQL server table name. Because you already created the linked table dbo_SQLServerTable, you can use the following VBA code.

Currentproject.connection.execute "INSERT INTO MyAccessTable(fld1, fld2, fld3) SELECT fld1, fld2,fld3 FROM dbo_SQLServerTable"

There is no need to create connection object in VBA code. Currentproject.connection is always available to be referenced.

Answer 4

Least amount of code I can think of is to use a pass-through query.

Setup a PT query to the server database in question.

Then your code to create a new table in access would look like:

Sub TestImport()

  Dim strSQL     As String
  
  With CurrentDb.QueryDefs("qryPassR")
     .SQL = "select * from tblHotels"
  End With
  
  Dim strLocalTable  As String
  
  strLocalTable = "zoo"
  
  CurrentDb.Execute "select * into " & strLocalTable & " FROM qryPassR"
  
End Sub

The above of course assumes you setup the connection to sql server (one database) when you created the PT query. The above approach is nice since you don't mess around with connection strings in code.

However, given that you need (want) to specify the database (and likely server), then above becomes this:

Sub TestImport2()

  Dim strSQL           As String
  Dim strServer        As String
  Dim strDatabase      As String
  Dim strUser          As String
  Dim strPass          As String
  
  strServer = ""
  strDatabse = ""
  strUser = ""
  strPass = ""
  
  Dim strLocalTable       As String
  Dim strServerTable      As String
  
  With CurrentDb.QueryDefs("qryPassR")
     .Connect = dbCon(strServer, strDatabase, strUser, strPass)
     .SQL = "select * from " & strServerAble
  End With
  
  CurrentDb.Execute "select * into " & strLocalTable & " FROM qryPassR"
  
End Sub

The above uses a "handy" function to create your connection string.

That function is as follows:

Public Function dbCon(ServerName As String, _
                     DataBaseName As String, _
                     Optional UserID As String = "", _
                     Optional USERpw As String, _
                     Optional APP As String = "Office 2010", _
                     Optional WSID As String = "Import") As String

   ' returns a SQL server conneciton string
   
  dbCon = "ODBC;DRIVER=" & SQLDRIVER & ";" & _
          "SERVER=" & ServerName & ";" & _
          "DATABASE=" & DataBaseName & ";"
          If UserID <> "" Then
             dbCon = dbCon & "UID=" & UserID & ";" & "PWD=" & USERpw & ";"
          End If
          dbCon = dbCon & _
          "APP=" & APP & ";" & _
          "WSID=" & WSID & ";" & _
          "Network=DBMSSOCN"

End Function

Edit

Poster has asked for solution to append data into a EXISTING table.

In that case, simply change this:

  CurrentDb.Execute "select * into " & strLocalTable & " FROM qryPassR"

to

  CurrentDb.Execute "INSERT INTO " & strLocalTable & " SELECT * FROM qryPassR"