You may find it easier to take the advice of the MS Access users and
perform an import or 'append query' (I think they mean INSERT INTO) in
MS Access.

However, it is possible to perform the INSERT INTO from the Excel
workbook. You must take care to avoid the memory leak bug that occurs
when one queries an open workbook. The example code below avoids this
problem by saving the worksheet to a temporary workbook and querying
the (closed) temp workbook:

Option Explicit

Sub UpdateRemoteWorkbook()

Dim wb As Excel.Workbook
Dim ConLocal As Object
Dim strConXL As String
Dim strConDB As String
Dim strPathXL As String
Dim strSql1 As String
Dim lngRowsAffected As Long

' Amend the following constants to suit
Const XL_WORKBOOK_TEMP As String = "" & _
"delete_me.xls"

Const XL_SHEET As String = "" & _
"MySheet"

Const DATABASE_PATH_FILENAME As String = "" & _
"C:\MyDatabase.mdb"

Const DATABASE_TABLE As String = "" & _
"MyTable"

' Do NOT amend the following constants
Const CONN_STRING_LOCAL As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

Const CONN_STRING_SERVER As String = "" & _
"[database=<PATH_FILENAME;]"

' Build connection strings
strPathXL = ThisWorkbook.Path & _
Application.PathSeparator

strConXL = CONN_STRING_LOCAL
strConXL = Replace(strConXL, _
"<PATH", strPathXL)
strConXL = Replace(strConXL, _
"<FILENAME", XL_WORKBOOK_TEMP)

strConDB = CONN_STRING_SERVER
strConDB = Replace(strConDB, _
"<PATH_FILENAME", DATABASE_PATH_FILENAME)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "INSERT INTO " & strConDB
strSql1 = strSql1 & "." & DATABASE_TABLE
strSql1 = strSql1 & " SELECT * FROM [" & XL_SHEET & "$]"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPathXL & XL_WORKBOOK_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(XL_SHEET). _
Copy .Worksheets(1)
.SaveAs strPathXL & XL_WORKBOOK_TEMP
.Close
End With

' Open connection to temp workbook
Set ConLocal = CreateObject("ADODB.Connection")
With ConLocal
.ConnectionString = strConXL
.Open
.Execute strSql1, lngRowsAffected
End With

ConLocal.Close

Debug.Print lngRowsAffected

End Sub

Jamie.

--