Craig Hatmaker   0 pts.  |   Nov 5 2009  7:51PM GMT

Here is a different approach.

Rather than merge the tables in Access, use XL as a multiuser data entry program to post to a single Access Table somewhere on the network.

This approach requires VBA macros in XL.

1) Create a button to for the user to click and post the data.

2) Assign a Macro to the button that reads the XL range and formats the entries into an SQL INSERT Statement (example:)

sSQL = “INSERT INTO myAccessTable EmployeID, CustomerID, Address VALUES (’” & cell(1, 1) & “‘, ‘” & cell(1,2) & ‘”, ” & cell(1,3) & “‘ ”

3) Call the routine below with the SQL string and a connection string pointing to your access data.

Public Function SQLExec(sSQL As String, sConnect As String) As Variant

‘ SQLExec: Execute and SQL Statement

‘Prerequisites:
‘ Include “Microsoft ActiveXData Objects 2.0 Library” or higher reference

‘ Parameters: sSQL - a string containing an SQL command to execute
‘ sConnect - a connection string
‘ Example: bResult = SQLExec(”CREATE INDEX index_name ON table_name (column_name)”, _
‘ “ODBC;DSN=AS400;Driver={iSeries Access ODBC Driver}”)

‘ Date Init Modification
‘ 01/01/01 CWH Initial Programming

On Error GoTo ErrHandler
SQLExec = “Failure” ‘Assume Something went wrong

Dim cn As ADODB.Connection
Dim errLoop As ADODB.Error

On Error GoTo ErrHandler
Debug.Print “Start:”, Time, sSQL
Set cn = New ADODB.Connection
cn.Properties(”Prompt”) = adPromptComplete
cn.Open sConnect, “”, “”
cn.Execute sSQL
Debug.Print “End:”, Time

‘ Application.StatusBar = cn.RecordsAffected & ” Records affected.”
If cn.Errors.Count = 0 Then
SQLExec = Success
Else
SQLExec = cn.Errors(0).Description
End If

ErrHandler:

On Error Resume Next
If Err.Number <> 0 Then
SQLExec = “Failure”
If Err.Number <> 0 Then
MsgBox “SQLExec - Error#” & Err.Number & vbCrLf & Err.Description, _
vbCritical, “Error”, Err.HelpFile, Err.HelpContext
Else
For Each errLoop In cn.Errors
MsgBox “Error number: ” & errLoop.Number & vbCr & _
errLoop.Description, vbCritical, “Error”, errLoop.HelpFile, errLoop.HelpContext
Next errLoop
End If
End If
cn.Close
On Error GoTo 0

End Function