Access VBA Append all contents of Access table to online SQL table

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am using the following code to append one line to an online SQL table.
How do I append the contents all of a table within my Access database to
the same online table
Public Sub AppendTableADO()
    'Dim con As ADODB.Connection
    'Dim cmd As ADODB.Command
    Dim oSQL As String
    
    On Error GoTo EH
    
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=lesedi_hr;User ID=Graham;Password=maharg1234;"
   
    Set cmd = New ADODB.Command
    
    oSQL = "Insert Into Table1 (Col1, Col2, Col3) Select 'Cat' as Expr1, 'on a' as Expr2, 'hot tin roof' as Expr3"
    
    With cmd
        .CommandText = oSQL
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
    
    
    Set cmd = Nothing
    Set con = Nothing
    
    Exit Sub
    
EH:
    
    MsgBox Err.Description
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Murbro,
You could manually link the SQL Server table into the MS Access table and then refer to it like any other local table - maybe set up an append query and call this from code.

Tell us more about the big picture of what you are trying to achive and we may be able to give you some better overall suggestion for a generic longtern solution.

Why do you need to transfer data from the MS Access Database to SQL Server
Is this a once off or somethong more regular
SSIS job may be an option to schedule the transfer
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi

Thanks for the response. The SQL table needs to be updated by one user and used by sveral others
in different parts of the country. So the manager controlling it needs to have read and write access and all the other users just read access. All these peoples have Access databases and need to see the data in the online SQL table
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
if an odbc link is the best way could you please tell me how to do that
It could be as simple as the following code.

This depends on the table structure of your database.  If you have any spaces in table or field names it changes syntax.
oSQL = "Insert Into Table1 (Col1, Col2, Col3) Select AccessTable.Field1, AccessTable.Field2, AccessTable.Field3 FROM AccessTable;"

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial