?
Solved

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

Posted on 2013-11-29
14
Medium Priority
?
734 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 38

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 38

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
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.

 

Author Comment

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

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 38

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 38

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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