• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

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

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
ou81aswell
Asked:
ou81aswell
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
PatHartmanCommented:
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
 
PatHartmanCommented:
Here's the one I use for Jet/ACE.   It supports apps with multiple BE's.
Relink.zip
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ou81aswellAuthor Commented:
Thanks guys. Is there a way to configure this via ODBC Data Source Administrator?
0
 
PatHartmanCommented:
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
 
Jim P.Commented:
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
 
ou81aswellAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
PatHartmanCommented:
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
 
Jim P.Commented:
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
 
ou81aswellAuthor Commented:
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
 
ou81aswellAuthor Commented:
Understood but I'm not sure who to award points to.
0
 
PatHartmanCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now