Here is a sample from in invoicing database I wrote that does just what you are asking.
I keep each month's invoices in a .MDB data file located in the "p:\invoices\" folder, each containing one month's information. The front end database has a linked table called "tInvoiceDetail". Depending on which month the user wishes to work with, I relink that table to the appropriate ..MDB using this procedure.
I use Access 2000. This function is depended on having the "Microsoft ADO Ext. 2.x for DLL and Security" (MSADOX.DLL) reference.
Sub RelinkTable(strFilename As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
If cat.Tables("tInvoice Detail").Properties("JET OLEDB:Link Datasource") <> ("p:\invoices\" & strFilename) Then
cat.Tables.Delete ("tInvoiceDetail")
Set tbl = New ADOX.Table
tbl.Name = "tInvoiceDetail"
tbl.ParentCatalog = cat
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = "p:\invoices\" & strFilename
tbl.Properties("Jet OLEDB:Remote Table Name") = "tInvoiceDetail"
cat.Tables.Append tbl
Set tbl = Nothing
End If
Set cat = Nothing
End Sub
