Solved

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

Posted on 2013-11-29
14
685 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
  • 5
  • 4
  • 2
  • +1
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
Here's the one I use for Jet/ACE.   It supports apps with multiple BE's.
Relink.zip
0
 

Author Comment

by:ou81aswell
Comment Utility
Thanks guys. Is there a way to configure this via ODBC Data Source Administrator?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ou81aswell
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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.
Comment Utility
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
Comment Utility
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
Comment Utility
Understood but I'm not sure who to award points to.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now