Dynamically Search for Database

Posted on 2006-03-21
Last Modified: 2010-05-01
I have a vb6 application that uses a file .dsn to locate the database on the server:

DRIVER=Driver do Microsoft Access (*.mdb)
FIL=MS Access

It has been requested of me to change this or use a different method for locating the database. The reason for the request is because the user wants to be able to avoid errors being thrown if the server changes, etc. The person requesting the change wants to eliminate the "hard coding" so the application won't be dependant upon this directory path. Is it possible to create something in an install package to where it points to the location of the database? Is this possible?
Question by:Gary2397
    LVL 4

    Expert Comment

    you don't need to use an ODBC
    Using ADO you can use a connection string that can be stored in an INI or CFG file for retrieval by the program.
    Then, nothing needs to be hard-coded, but the INI file does need to be maintainable

    Author Comment

    This sounds like what I am looking for. Will this allow the user to specify the location of the database on installation? I don't have any experience with INI files, is there a good tutorial anywhere.
    LVL 1

    Accepted Solution

    Personally, INI is the "old way". It works fine in simple cases, but for a more extensible solution, XML is better.
    You can try this link:
    LVL 1

    Expert Comment

    This link has a more VB6-centric approach.
    LVL 1

    Expert Comment

    ...and if you just want to use an INI, then you'll want to use these Win32 APIs (link to MSDN info included):

    'Windows API/Global Declarations for :.INI read/write routines

    Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

    Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
    LVL 1

    Expert Comment

    Finally, to extend on the original comment by g_johnson, here's some info on ADO in case you need it. Incidentally, I apologize for the multi-post response. I should have composed my thoughts better before dumping this all on you.



    Hopefully that should get you oriented in the right direction.
    LVL 16

    Expert Comment

    Is the connection string stored on the local machine, as opposed to a network resource?  If so, you could easily store to the registry with:

        SaveSetting("MyApp", "MyKey", "ConnectionString", "{Provider=...}"

        szConnStr = GetSetting("MyApp", "MyKey", "ConnectionString", "this is the default value")


    LVL 9

    Expert Comment

    by:Naveen Swamy
    basically u need to have a external file or settings from where you need to load the database, this would be a very good option, if given to the user.

    Ini is an old aproach
    Registry is good but should be used with caution
    XML is a good option, thinking future (its pretty easy to, i learnt basics of xml in 2 hours)
    LVL 16

    Expert Comment

    Alternatively, why don't you host your DSN file remotely?  That way, the client app uses the setting to find the DSN and then connects to it; as long as you keep the DSN updated, you can change the database location as much as you want, without affecting the client app and has the advantage that the user does not need to enter the connection string manually (less chance of mistakes) and does not need to know a database username/password.

    You could store the DSN location in the registry or a settings file an even allow the user to browse to the DSN using the Common Dialog control.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    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…
    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…

    746 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

    18 Experts available now in Live!

    Get 1:1 Help Now