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

In addition to Sean's suggestion, check
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web"