Changing a Table Link Dynamically

[EddieN1]

I have several users who pick a text file for input into my Access database. It could reside anywhere, local drive, shared drive, etc. I'm using an Office.FileDialog process to allow them to pick the desired file, so I have the full pathname at that point. In the past, I have used FileCopy to copy the file to the file I have statically linked a table to. However, I would prefer just changing the table link to point to the selected file.

[pbaldy]

One way:

DoCmd.DeleteObject acTable, "ExcelWages"

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelWages", strPath, True

Changing the names to yours.

[EddieN1]

Almost there... the files they are linking to are text files, not spreadsheets, with an extension of .mct. Example, MC123456.mct. Note that there are no field names in the text files. The field names and definitions are in the linked tables.

[pbaldy]

TransferText might work then. Not sure about the field names. You can try using a TableDef object and changing the path, but that's untested.

http://msdn.microsoft.com/en-us/libr.../ff193791.aspx