How To Set Up ODBC Data Sources When Distributing Apps

This article was previously published under Q123008
This article has been archived. It is offered "as is" and will no longer be updated.
This article discusses the following four methods for setting up an ODBCdata source on a computer:
  • ODBC Setup
  • RegisterDatabase
  • Blind Copy of INI files

Required Files

The following files must be distributed with your application if you useODBC. When using the Setup Wizard to create distribution disks, ensurethat the necessary files are included in the file list. All of the fileslisted should be installed in the \WINDOWS\SYSTEM directory.

Optional files (SQL Server or Oracle) are denoted with an asterisk (*).
File                  Description----------------------------------------------------------------------ODBC.DLL              The ODBC Driver Manager. This DLL is called by the                      Microsoft Jet database engine when performing ODBC                      operations. The Driver Manager handles loading the                      correct ODBC driver and dispatching ODBC function                      calls to the driver.ODBCINST.DLL          The ODBC Driver Installation library. This DLL                      contains Driver installation specific functions.                      The ODBC Administrator (ODBCADM.EXE) calls functions                      exported from this DLL when installing ODBC                      drivers. You may also call functions in this DLL                      to automate driver installation.ODBCADM.EXE           The ODBC Administrator program. This program                      allows a user to install ODBC drivers and                      set up or modify Data Sources.ODBCINST.HLP          The ODBC Administrator help file.COMMDLG.DLL           The Common Dialog DLL. This DLL is used by the                      ODBC Administrator program.CTL3D.DLL             The 3D Control DLL. This DLL is used by the ODBC                      Administrator program. If you are using ODBC.DLL                      version 1.05 or greater, you need to distribute                      CTL3DV2.DLL.PDSODBC.DLL           Crystal Reports Physical Server DLL for ODBC. This                      DLL is required only if your application uses Crystal                      Reports to access an ODBC data source.<driver>.DLL          The ODBC driver(s) that the application will use                      to connect to specific Data Sources.                      SQL Server:  SQLSRVR.DLL*                      Oracle 6:    SQORA.DLL*<netlib>.DLL          The network library file(s). This file is used                      to access the Data Source when using a specific                      network protocol.                      Named Pipes: DBNMP3.DLL*                      TCP/IP (Sybase SQL Server): WDBNOVTC.DLL*                      IPX/SPX (Sybase SQL Server): WDBNOVSP.DLL*                      SQL*Net Interface: ORA6WIN.DLL*INSTCAT.SQL*          SQL Server Catalog Stored Procedures script.DRVSSRVR.HLP*         SQL Server ODBC Driver help file.ORASETUP.DLL*         Oracle ODBC Driver setup functions.DRVORACL.HLP*         Oracle ODBC Driver help file.ORACLE.TXT*           Oracle ODBC Setup "read me" file.ODBC.INI              Initialization file containing information                      about specific Data Sources. The DSN parameter                      in the Connect property of the data control or                      the OpenDatabase statement corresponds to an                      entry in the ODBC.INI. This file must also be                      created or modified on the client computer.ODBCINST.INI          The Initialization file that contains                      information about installed ODBC drivers. The                      RegisterDatabase statement and ODBC Administrator                      use the information contained in this file to                      set up Data Sources. Entries in ODBCINST.INI                      are created either by running an ODBC driver                      setup or through the ODBC API. This file must                      also be either created or modified on the client                      computer.

Four Methods to Get DSN information into ODBC.INI and ODBCINST.INI

The .INI files store information about the ODBC driver(s) and the ODBC DataSources. As a result, they are variable -- a user's may already have theminstalled in the \WINDOWS directory. If a developer were to blindlycopy ODBC.INI and ODBCINST.INI onto the user's computer, the new files mayoverwrite existing Data Sources.

Below are four methods you can use to get DSN information into the user'sODBC.INI and ODBCINST.INI files.

ODBC Setup

To install an ODBC Driver and establish an ODBC Data Source, the VisualBasic online Help documentation recommends that you copy the entirecontents of the \VB\ODBC directory to an additional distribution disk.

As a developer, you can specify that the disk be inserted and SETUP.EXE runfrom the floppy disk. In addition, you can prompt the user to insert theODBC floppy disk, and then use the Visual Basic Shell command to shell outto SETUP.EXE.

The Setup Wizard copies and modifies SETUP1.MAK into SETUP1A.MAK during theprocess of creating distribution disks. It builds SETUP1A.MAK intoSETUP1.EXE, compresses it, and copies it to the distribution disks. WhenSETUP.EXE is executed on the distribution disks, the files in SETUP.LSTare copied to the destination computer. SETUP1.EX_ is then uncompressed andexecuted to start copying files from the floppy disks to the destinationcomputer.

It is possible to then modify SETUP1A.MAK, rebuild SETUP1.EXE, compress it,and copy it to the distribution disks. To ensure that the compressed filesize will fit on the first distribution disk, you must pad the project withcode prior to first executing the Setup Wizard. Then you can change thecode into comments and add new code to prompt for the ODBC Setup disk. Theresulting EXE size will then still fit on the first distribution floppydisk.

Modify SETUP1.FRM in the \VB\SETUPKIT\SETUP1 directory to add the necessarycode to pad the executable. This file is copied into SETUP1A.MAK during theSetup Wizard's execution.

NOTE: Microsoft Technical Support does not support the modification of theSetup process or any of the setup files. Support is provided for the SetupWizard and the files it creates on an "as is" basis only.

Here are the steps to follow:

  1. Start Visual Basic and from the File menu, choose Open Project. Open SETUP1.MAK in the \VB\SETUPKIT\SETUP directory.
  2. Select SETUP1.FRM from the project window. Press F7 to view the code.
  3. At the end of the Form_Load procedure add the following code in the ExitSub: label part, after RestoreProgMan and before the End statement:
          Dim tmpK As String      Dim tmpS As String      Dim I As Long      tmpK = "dummy"      For I = 1 To 1000         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)         tmpS = Mid$(tmpK, 1, 2)      Next I
  4. Save the project (ALT, F, V).
  5. Run the Setup Wizard, and create the distribution disks.
Once the disks have been created, you need to go back into VisualBasic, edit SETUP1A.MAK, and add the appropriate code to prompt for theODBC Setup And Installation Disk. Follow these steps:

  1. Start Visual Basic.
  2. Open the SETUP1A.MAK project in \VB\SETUPKIT\SETUP1 (ALT, F, O).
  3. Choose SETUP1A.FRM and press F7 to view the code.
  4. In the Form_Load procedure, place an apostrophe in front of each line of the dummy code that was previously inserted as a place holder:
          'Dim tmpK As String      'Dim tmpS As String      'Dim I As Long      'tmpK = "dummy"      'For I = 1 To 1000      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      '   tmpS = Mid$(tmpK, 1, 2)      'Next I
  5. Add the following code within the Form_Load procedure immediately following the commented code:
          x% = MsgBox("Do you want to install the ODBC Drivers?", 36,         App.title)      If x% = 6 Then         If Not PromptForNextDisk(2, SourcePath$ + "ODBCADM.EX_") Then            GoTo ErrorSetup         End If         x% = Shell(SourcePath$ &amp; "setup.exe")      End If
  6. Change the disk number to 1 greater than the total number of distribution disks created. The disk number is the first parameter to the PromptForNextDisk procedure. In this example, the next disk to prompt for is 2.
  7. Save the SETUP1A.MAK project and create the executable as SETUP1.EXE in the \VB\SETUPKIT\SETUP1 directory (ALT, F, K).
  8. Shell out to an MS-DOS command prompt and change the directory to \VB\SETUPKIT\SETUP1. Execute the following at the command prompt:
  9. Place the first distribution floppy disk in the appropriate drive and copy SETUP1.EX_ to the floppy disk:
    copy SETUP1.EX_ A:\SETUP1.EX_
Now, when your distribution disks are run, the final step will be to promptfor the ODBC Setup and Installation disk. SETUP.EXE will be executed fromthis disk and the user can then install the appropriate ODBC driver andcreate the necessary Data Source. You should include instructions for thisprocess.

For more information on modifying SETUP1.EXE please refer to Chapter 25,"Distributing Your Application" in the Microsoft Visual Basic Programmer'sGuide.


Visual Basic provides the RegisterDatabase statement to help in installingODBC data sources, not drivers. The RegisterDatabase statement assumes thatODBCINST.INI and ODBCINST.DLL already exist on the computer. That is, thedrivers must be installed before running RegisterDatabase. If so, thedeveloper can use RegisterDatabase to add or update an entry in theODBC.INI.

The problem with this method is that if the client computer does not haveODBC installed on the computer, the ODBCINST.INI and DLL will not exist.Also, if the ODBC driver is new to the computer, there will not be an entryfor it in ODBCINST.INI, so RegisterDatabase will fail then as well.

The following description, syntax, remarks, and example about theRegisterDatabase statement come from the Visual Basic online Help:

Makes connect information for an ODBC data source name available for use by the OpenDatabase function.
RegisterDatabase dsn, driver, silent, attributes
Remarks: The RegisterDatabase statement has the following parts:

  • DSN: A string expression that is a name used in the OpenDatabase function and refers a block of descriptive information about the data source. For example, if the data source is an ODBC remote database, it would be the name of the server.
  • DRIVER: A string expression that is the name of the ODBC driver. This is not the name of the ODBC driver DLL file. For example, "SQL Server" or "Oracle" are driver name but "SQLSRVR.DLL" is the name of a DLL file. You must have ODBC and the appropriate driver already installed.
  • SILENT: A numeric expression that is True if you do not want to display the ODBC driver dialogs that prompt for driver-specific information, or False if you do want to display the ODBC driver dialogs. If silent is True, then attributes must contain all the necessary driver-specific information or the dialog will appear anyway.
  • ATTRIBUTES: String expression that is a list of keywords to be added to the ODBC.INI file. The keywords are in a carriage-return delimited string.
      Sub Command1_Click ()         Dim att As String         Dim mydb As Database         att = "Description = SQL Server on server Texas" & Chr$(13)         att = att & "OemToAnsi=No" & Chr$(13)   ' Build keywords string.         att = att & "Server=TEXAS" & Chr$(13)         att = att & "Network=DBNMP3" & Chr$(13)         att = att & "Address=\\TEXAS\PIPE\SQL\QUERY" & Chr$(13)         att = att & "Database=Pubs" & Chr$(13)         att = att & "LastUser=Stimpy"         ' Update ODBC.INI.         RegisterDatabase "Texas", "SQL Server", True, att         Set mydb = OpenDatabase("Texas", False, False, "ODBC;")         mydb.Close      End Sub
If the database is already registered in the ODBC.INI file, the entry isupdated. If RegisterDatabase fails for any reason, no changes are made tothe ODBC.INI file and an error occurs.


This is probably the most flexible and most efficient method, but mostdevelopers are not familiar with it and do not have the ODBC SDK thatdocuments the API. Developers should get the Microsoft Software DevelopmentKit (SDK) and get the "Microsoft ODBC 2.0 Programmer's Reference and SDKGuide" from Microsoft Press.

Copy INI

If the developer is certain that an ODBC.INI and ODBCINST.INI do not existon the installation computer, they can simply copy the files. However, thedeveloper must ensure that the paths to the drivers are correct; paths arefully qualified within the .INI files. For example, the ODBC.INI file willspecify C:\WINDOWS\SYSTEM\SQLSRVR.DLL as the driver for SQL Server, so ifthe user's Windows setup is in \WIN31, the path won't work.
3.00 4.00

Article ID: 123008 - Last Review: 12/04/2015 10:43:44 - Revision: 1.2

Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 4.0 16-bit Enterprise Edition, Microsoft Visual Basic 4.0 32-Bit Enterprise Edition, Microsoft Visual Basic 3.0 Professional Edition

  • kbnosurvey kbarchive kbdatabase kbhowto KB123008