Combining Multiple MDBs into one master (table only)

[MrKABC]

Hello..

I have an Access 97 frontend/backend setup, the backend contains ONE table only. There are others with the same setup on their workstations. Centralized backend is not possible due to infrastructure limitations. I have set it up so that the frontend e-mails their backend MDB files to me, and now I have a collection of MDB files. (YES, I am an idiot.)

What I want to accomplish: to programmatically import the information from the different MDB files into MY local backend table. The layout of all tables is the same. Duplicate information is not a problem.

Ideas/suggestions would be most appreciated!

 

[MrKABC]

Here is what I have so far, but I can't get it to work. REALLY could use some help here! ,=]


Private Sub btnImport_Click()

Dim wk2 As Workspace
Dim db2 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim i2 As Integer

On Error GoTo ImportError

strFileName = "AuditData.mdb"

'Open the database to be imported.
Set wk = CreateWorkspace("Import", "", "", dbUseJet)
Set db = wk.OpenDatabase(strImportDb & strFileName) 'strImportDb = C:\Auditor\Import\ and strFileName =

'Open the central database to receive the imports.
Set wk2 = CreateWorkspace("AuditData", "", "", dbUseJet)
Set db2 = wk2.OpenDatabase(strMainDB) 'strMainDB = C:\Auditor\AuditData.mdb

'It's a new record, so add the audit to their respective tables.
'Create an array to hold the SQL string which will import the records.

Dim strImport As String
strImport = "INSERT INTO Maintbl SELECT Maintbl.* FROM Maintbl IN '" & strImportDb & strFileName & "'"

'Import records into the central database.

strSQL = strImport
db2.Execute strSQL