Registering an ODBC Data Source

Before you can use ODBC in a Microsoft Jet workspace or in an ODBCDirect workspace, you must register the ODBC data source. Registering the data source stores information about the data source in the Windows Registry and makes this information available to applications. You can register a data source from the ODBC Data Source Administrator or from Visual Basic.

Û To register a SQL Server data source by using the ODBC Data Source Administrator

1. In Windows Control Panel, double-click the 32bit ODBC icon. In Microsoft Windows NT Workstation 3.51 and 4.0, this icon is named ODBC.
   
   
2. Click the tab for the kind of data source name (DSN) you want to define:
   • To create a DSN that is visible only to the user currently logged on to the current computer, click the User DSN tab.
     
     
   • To create a DSN that is available to any user logged on to the current workstation, click the System DSN tab.
     
     
   • To create a disk file that contains DSN information and that can be distributed with your application or accessed from a network location, click the File DSN tab. This option is only supported for the Microsoft SQL Server driver. Microsoft Jet will return an error if you try to use a .dsn file created for other drivers.
3. Click Add and then in the Create New Data Source dialog box, double-click the ODBC driver for the data source you want to access. For example, double-click SQL Server.
   
   
4. In the Data Source Name box, type a data source name. This can be any string, such as SalesDB or Pubs. The string doesn’t have to correspond to the actual name of a database or table you want to access.
   
   
5. In the Description box, type a description of the database, such as Sales Data for 1996. You can enter any text.
   
   
6. In the Server box, type the name of the network server where your data source resides. Do not include a double-backslash (\\) before the name. If you don’t know the name of the server, you must get the exact name from your network administrator. If you are using Microsoft Windows NT Workstation, the names of registered servers will be available in the drop-down list.
   
   
7. Click Options, and then type the exact name of the database you want to access in the Database Name box. For example, to specify the Microsoft SQL Server Pubs sample database, type Pubs.

Note This procedure describes the steps for registering a Microsoft SQL Server data source. The steps for registering other ODBC data sources may vary because the data source driver requires a different set of information. If the dialog box for the data source you selected has values not described in the preceding steps, click the Help button for more information.

In some cases, you may want to register the data source in Visual Basic code instead of relying on users to register it with the ODBC Data Source Administrator. To do this, use the RegisterDatabase method of the DBEngine object. The following example registers a data source named Pubs.

Function RegisterDB() As Boolean
	Dim str As String, errLoop As Error

	On Error GoTo Error_RegisterDB
	' Build keywords string.
	str = "Description=SQL Server on Server Publishers" & _
		vbCr & "OemToAnsi=No" & _
		vbCr & "Network=(Default)" & _
		vbCr & "Address=(Default)" & _
		vbCr & "Server=Publishers" & _
		vbCr & "Database=Pubs"
	' Register database.
	DBEngine.RegisterDatabase "Pubs", "SQL Server", True, str
	RegisterDB = True

Exit_RegisterDB:
	Exit Function

Error_RegisterDB:
	' Notify user of any errors that result from
	' invalid data.
	For Each errLoop In DBEngine.Errors
		MsgBox "Error number: " & errLoop.Number & _
			vbCr & errLoop.Description
	Next errLoop
	RegisterDB = False
	Resume Exit_RegisterDB
End Function