Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Configure Access ODBC datasource so user can select the underlying mdb / accdb file

Posted on 2013-11-29
14
Medium Priority
?
739 Views
Last Modified: 2013-12-07
Is it possible to configure an Access ODBC data source such that when the user opens it they get a file open dialog box so they can point to the desired accdb file? Currently, we create Access ODBC data sources that have the path name of the files hard-coded into them.

Thanks.
0
Comment
Question by:ou81aswell
[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
  • 5
  • 4
  • 2
  • +1
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39686386
see the codes from this link

http://access.mvps.org/access/tables/tbl0012.htm

the codes needs to be modified to suit your requirements.

post back if you need more help in altering the codes.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39686470
Sorry - ignore this post.  I missed the .accdb part of your request.  This is a SQL Server solution.  I'm leaving it in case someone else might find it useful.

Here is a sample form and table I used in one application.  We had two different databases (POMS and Replicate) and three environments (test, QA, and Prod) for each.  This form was used to switch between them.  You can modify the form to be the form that opens when the db opens and then after relinking, open your menu.
RelinkSQLServer.accdb
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39686473
Here's the one I use for Jet/ACE.   It supports apps with multiple BE's.
Relink.zip
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!

 

Author Comment

by:ou81aswell
ID: 39687566
Thanks guys. Is there a way to configure this via ODBC Data Source Administrator?
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39687642
You said you wanted the user to be able to point to a BE when the app opens.  What do you want to configure?  Do you want to predefine a list of BE's.  To do that create a table for the Jet/ACE version similar to what I used for the ODBC version and rather than opening a file dialog box to search the file system for the BE, use a combobox to present the predefined choices.

I have never tried to read the User DSN's but there  is probably an API that will do it.  The problem with that is you would need to use a strict naming convention so that your code could pick out the valid options since not all DSN's would be valid.  The table method will be easier to manage and you won't have to figure out if there is an API you can use and how to use it.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39687765
This article: Creating DSNs for SQL Server: In depth on how to make one explains how to make a SQL DSN on the fly. It should be adaptable to do it for an Access one.
0
 

Author Comment

by:ou81aswell
ID: 39688437
Sorry guys. I think I've composed my question in a confusing manner.

I don't want to create a data source on the fly. I want to go to ODBC Data Source Administrator and create a new user (or system) data source using the MS Access driver but I don't want to have to specify the name of the accdb file (I'd be happy to specify a path).

Then, when the user selects that DSN from the list of DSN's that I present them, I'd like the driver to automatically take them to a common file open dialog (when I open the data source) where they can point to the actual access database file.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39688458
don't know why you need to create a DSN for access back end. You can select the back end directly with a file dialog.

try this codes

Function selectBE()
Dim fd As Object
Set fd = Application.FileDialog(3)
With fd
.Filters.Clear
.Filters.Add "Access db", "*.accdb,*.mdb"

.Title = "Please select Back End"

.InitialFileName = Application.CurrentProject.Path
If .Show Then
    selectBE = .SelectedItems(1)
End If
End With
End Function
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 39688665
You can't create a dummy DSN.  Each DSN points to a specific database and aside from that, you don't create DSN's for Access (Jet/ACE) databases since Access accesses them natively and not through the ODBC driver.  If your FE is not Access then you need to change the order in which you do things.  First, the user chooses a database and then you choose which DSN to use to get to it.   Again, I suggest you use tables that you can use to manage the connection.  Put the table into a separate database that you always connect to.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39688812
And Access stores the path in the database. Go to Tools --> Options and on the View tab check on the Hidden and System check boxes.

You will see a table MSysObjects. That is the list of all the rest of the objects in the DB. Any linked objects will have the Connect and/or Database columns populated. So you not only have to select the new database, but re-link the tables as well.

I would suggest that you go with a consistent DB name and use the %USERPROFILE% to change the location.
0
 

Author Comment

by:ou81aswell
ID: 39702776
I'd like to abandon this question. I was hoping the driver could be persuaded to throw up a file open dialog without having to hard-code the path of the access database file in the data source.

Thanks.
0
 

Author Comment

by:ou81aswell
ID: 39703637
Understood but I'm not sure who to award points to.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39703643
You can split them up among those who you feel helped or you can say that no answer was provided and not give any points or you can give all the points to one person.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

647 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