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

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.

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

Here's the one I use for Jet/ACE.   It supports apps with multiple BE's.
Relink.zip

Thanks guys. Is there a way to configure this via ODBC Data Source Administrator?

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.

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.

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.

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

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.

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.

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.

Understood but I'm not sure who to award points to.