Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can I use vba to delete connection to external tables.accdb and reconnect to them?

Posted on 2012-03-14
9
Medium Priority
?
678 Views
Last Modified: 2012-03-22
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\FAIDatabase\Tables\TestTables.accdb", Connect to all Tables"
else
     CreateConnection = "\\sfile0\Data\Inspection\FAIDatabase\Tables\Tables.accdb", Connect to all Tables
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!
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37720016
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37720133
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..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37720180
are you linking to Access tables only?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:SeyerIT
ID: 37720183
Yes. Just an external access file that has a few tables in it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37720252
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
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37720502
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'.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37720548
Then the links that capricorn1 posted should contain all the info you need.

I'll let him help you from here on...


Jeff
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37720571
you have a mixture of ODBC and Access linked tables..

so to delete the Access linked tables
dim rs as dao.recordset
set rs=currentdb.openrecordset("select [name] from msysobjects where [type]=6")

if rs.eof then exit sub

do until rs.eof
        currentdb.execute "drop table [" & rs!name &"]"

rs.movenext
loop
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37721997
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!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question