asked on How can I use vba to delete connection to external tables.accdb and reconnect to them?
My ultimate goal here is during logon, the user also has a dropdown box where they can select whether to connect to the test tables or the live tables. So I will just write an if statement in vba that will look at which is selected in the drop down box when they click logon and will create the link to the tables in the corresponding external .accdb file containing the tables.
So here is some mocked up code of what I think it may look like;
Private Sub cmdLogon_Click()
if me.cboEnvironment = "Test" then
CreateConnection = "\\sfile0\Data\Inspection\
else
CreateConnection = "\\sfile0\Data\Inspection\
End if
End
**Oh and I guess I would need to delete the connection to the current tables first?
That's just my theoretical idea of what the code would look like.. I really don't have a clue what the connection vba looks like.
Thanks for your advice and assistance!
So here is some mocked up code of what I think it may look like;
Private Sub cmdLogon_Click()
if me.cboEnvironment = "Test" then
CreateConnection = "\\sfile0\Data\Inspection\
else
CreateConnection = "\\sfile0\Data\Inspection\
End if
End
**Oh and I guess I would need to delete the connection to the current tables first?
That's just my theoretical idea of what the code would look like.. I really don't have a clue what the connection vba looks like.
Thanks for your advice and assistance!
Microsoft Access
Last Comment
Jeremy Campbell
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Wow.. that seems like an awful lot of code.. Forgive for not being quite capable of digesting all of that. I may need a little more guidance. Seems like there maybe a lot of extra stuff in that code that I don't really need? I'm thinking that the tbl0009.htm link most applies to what I'm trying to do though..
are you linking to Access tables only?
ASKER
Yes. Just an external access file that has a few tables in it.
Not sure I have a full grasp of your system...
But what capriconr1 posted is a comprehensive list of useful links, you will have to take from each link what you might need.
In a general sense, any system to do things like this will be inherently complex.
Another approach would be to have both the linked and the Static tables in the same DB, just with slightly different names:
Local Tables:
tblCustomers
tblOrders
tblProducts
Linked tables:
tblCustomers_Lnk
tblOrders_Lnk
tblProducts_Lnk
Then upon the combobox selection, you would rename the tables if need be.
*However* this is precarious, as you must employ solid error handling, validation and perhaps even employ "roll backs" if anything goes wrong during the renaming process...
It may just be simpler to have two separate databases...
But again, I don't know all the details of this system...
Just some other thoughts...
JeffCoachman
But what capriconr1 posted is a comprehensive list of useful links, you will have to take from each link what you might need.
In a general sense, any system to do things like this will be inherently complex.
Another approach would be to have both the linked and the Static tables in the same DB, just with slightly different names:
Local Tables:
tblCustomers
tblOrders
tblProducts
Linked tables:
tblCustomers_Lnk
tblOrders_Lnk
tblProducts_Lnk
Then upon the combobox selection, you would rename the tables if need be.
*However* this is precarious, as you must employ solid error handling, validation and perhaps even employ "roll backs" if anything goes wrong during the renaming process...
It may just be simpler to have two separate databases...
But again, I don't know all the details of this system...
Just some other thoughts...
JeffCoachman
ASKER
Perhaps if I describe my current manual process it would help make more since?
First I highlight my three connect tables and delete them.
![delete]()
Then I go to External Data, Click on Access and put in my file Name where my tables database is stored, select Link and hit OK.
![attach]()
Last I select All and hit OK,
![selectall]()
Then my tables are linked up.
I just wanted to automate this and put an if statement in that would go to a different path for the test tables if the combobox was on 'Test'.
First I highlight my three connect tables and delete them.
Then I go to External Data, Click on Access and put in my file Name where my tables database is stored, select Link and hit OK.
Last I select All and hit OK,
Then my tables are linked up.
I just wanted to automate this and put an if statement in that would go to a different path for the test tables if the combobox was on 'Test'.
Then the links that capricorn1 posted should contain all the info you need.
I'll let him help you from here on...
Jeff
I'll let him help you from here on...
Jeff
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for that part Capricorn.. Now could you maybe give me some pointers on relinking them? I would try to get through the links you provided and figure it out but I'm a little strapped on time.. If it's too complicated then no worries. I can crunch through it at a later time.
Thanks again!
Thanks again!