HOWTO: Set Up ODBC Data Sources When Distributing Apps

----------------------------------------------------------------------------
----
The information in this article applies to:
Microsoft Visual Basic Professional Edition, 16-bit, for Windows, version
4.0
Microsoft Visual Basic Professional Edition, 32-bit, for Windows, version
4.0
Microsoft Visual Basic Enterprise Edition, 16-bit, for Windows, version 4.0
Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0
Microsoft Visual Basic Professional Edition for Windows, version 3.0

----------------------------------------------------------------------------
----


SUMMARY
This article discusses the following four methods for setting up an ODBC
data source on a computer:



ODBC Setup


RegisterDatabase


ODBC API


Blind Copy of INI files





MORE INFORMATION

Required Files
The following files must be distributed with your application if you use
ODBC. When using the Setup Wizard to create distribution disks, ensure that
the necessary files are included in the file list. All of the files listed
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 Data
Sources. As a result, they are variable -- a user's may already have them
installed in the \WINDOWS directory. If a developer were to blindly copy
ODBC.INI and ODBCINST.INI onto the user's computer, the new files may
overwrite existing Data Sources.

Below are four methods you can use to get DSN information into the user's
ODBC.INI and ODBCINST.INI files.
ODBC Setup
To install an ODBC Driver and establish an ODBC Data Source, the Visual
Basic online Help documentation recommends that you copy the entire contents
of the \VB\ODBC directory to an additional distribution disk.

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

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

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 file
size will fit on the first distribution disk, you must pad the project with
code prior to first executing the Setup Wizard. Then you can change the code
into comments and add new code to prompt for the ODBC Setup disk. The
resulting EXE size will then still fit on the first distribution floppy
disk.

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

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

Here are the steps to follow:


Start Visual Basic and from the File menu, choose Open Project. Open
SETUP1.MAK in the \VB\SETUPKIT\SETUP directory.


Select SETUP1.FRM from the project window. Press F7 to view the code.


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



Save the project (ALT, F, V).


Run the Setup Wizard, and create the distribution disks.


Once the disks have been created, you need to go back into Visual Basic,
edit SETUP1A.MAK, and add the appropriate code to prompt for the ODBC Setup
And Installation Disk. Follow these steps:


Start Visual Basic.


Open the SETUP1A.MAK project in \VB\SETUPKIT\SETUP1 (ALT, F, O).


Choose SETUP1A.FRM and press F7 to view the code.


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



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



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.


Save the SETUP1A.MAK project and create the executable as SETUP1.EXE in the
\VB\SETUPKIT\SETUP1 directory (ALT, F, K).


Shell out to an MS-DOS command prompt and change the directory to
\VB\SETUPKIT\SETUP1. Execute the following at the command prompt:
\VB\SETUPKIT\KITFILES\COMPRESS -r SETUP1.EXE



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 prompt
for the ODBC Setup and Installation disk. SETUP.EXE will be executed from
this disk and the user can then install the appropriate ODBC driver and
create the necessary Data Source. You should include instructions for this
process.

For more information on modifying SETUP1.EXE please refer to Chapter 25,
"Distributing Your Application" in the Microsoft Visual Basic Programmer's
Guide.
RegisterDatabase
Visual Basic provides the RegisterDatabase statement to help in installing
ODBC data sources, not drivers. The RegisterDatabase statement assumes that
ODBCINST.INI and ODBCINST.DLL already exist on the computer. That is, the
drivers must be installed before running RegisterDatabase. If so, the
developer can use RegisterDatabase to add or update an entry in the
ODBC.INI.

The problem with this method is that if the client computer does not have
ODBC 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 entry
for it in ODBCINST.INI, so RegisterDatabase will fail then as well.

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

Description:
Makes connect information for an ODBC data source name available for use by
the OpenDatabase function.
Syntax:
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.


Example:
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 is
updated. If RegisterDatabase fails for any reason, no changes are made to
the ODBC.INI file and an error occurs.
ODBC API
This is probably the most flexible and most efficient method, but most
developers are not familiar with it and do not have the ODBC SDK that
documents the API. Developers should get the Microsoft Software Development
Kit (SDK) and get the "Microsoft ODBC 2.0 Programmer's Reference and SDK
Guide" from Microsoft Press.
Copy INI
If the developer is certain that an ODBC.INI and ODBCINST.INI do not exist
on the installation computer, they can simply copy the files. However, the
developer must ensure that the paths to the drivers are correct; paths are
fully qualified within the .INI files. For example, the ODBC.INI file will
specify C:\WINDOWS\SYSTEM\SQLSRVR.DLL as the driver for SQL Server, so if
the user's Windows setup is in \WIN31, the path won't work.

Additional query words: 3.00 4.00

Keywords : kbDatabase kbODBC kbVBp400
Issue type : kbhowto
Technology : kbVBSearch kbAudDeveloper kbPTNotAssigned kbExchange400
kbZNotKeyword2 kbVB400Search kbVB16bitSearch kbVFP300