Hi guys, I'm creating an Access DB and would need some advice on how to either:
1) update/change the link of my linked table OR
2) import a linked table
I'm creating a standard front-end program for user interface.. kinda like a switchboard. From this front-end, users will choose the database that they want to work on. Each DB will be hosted in the same folder, as a separate file. Each DB consists only of tables and nothing else.
(I cannot place every database into the same file as this would overload the program.)
I've designed all the DB to use standard naming conventions (ie. every table has the same name, same fields etc.) so that the queries and forms in my front-end can refer to these fields without problem.
At the press of a button, I would like the program to link the tables to the selected DB -- if possible, unlink at the end.
Thus far, all I managed was two alternative methods:
b) DoCmd****nSQL (SELECT * INTO ...)
These alternatives aren't satisfactory for 2 reasons. One, if the Table exists then it would create a new Table called Table1 -- link will not work. Secondly, these merely import the source table into the front-end db for working, but if I make any changes then the original source DB will not be updated. This is my main concern.
A workaround that comes to mind is to do DoCmd.TransferDatabase twice, once to import and then to export back to the source file once finished. But this is difficult to implement when there are multiple users logging in, and besides it doesn't make much sense if users are merely "viewing" the recordset without making changes.
Any ideas on how to proceed?
Forget my request, I found the solution. Was staring straight at me, I just needed to change the Database Type under TransferDatabase from acTable to acLink. *duh*
Last edited by andi_kan; 03-19-10 at 04:57.
Financial guy by trade, software tinkering by hobby
PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM Windows 7 Professional 32-bit SP1 MS Office 2010