asked on

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

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

ASKER CERTIFIED SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

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 Brown

ASKER

if an odbc link is the best way could you please tell me how to do that

SOLUTION

Richard Daneke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

thanks