ACC: Connecting to SYBASE SQL Server from an ODBC Application

• Microsoft Access versions 1.1, 2.0
• Microsoft Visual Basic version 3.0 for Windows
• Microsoft FoxPro version 2.5 for Windows

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

You can connect to a SYBASE SQL Server computer from an ODBC application (such as Microsoft Access, Microsoft Visual Basic, or Microsoft FoxPro for Windows) using the ODBC SQL Server driver. This article describes how to set up the ODBC data sources.

If you are already connected to SYBASE SQL Server from Microsoft Windows DB-Library applications, no other components are necessary.

MORE INFORMATION

The key to both Microsoft SQL Server and SYBASE SQL Server connectivity is a module called the Net-Library. This module consists of two interfaces: one with the network and one with the application. The network interface is customized to support a particular network; however, the application interface remains the same.

Because ODBC, DB-Library, APT-SQL, APT-Library, and so forth communicate with the Net-Library, these utilities can be written independent of the network. The ODBC SQL Server driver, in particular, can be used to connect either to a Microsoft SQL Server computer or to a SYBASE SQL Server computer (on a UNIX or VMS system), as long as there is a Net-Library for whatever network you are using.

The following paragraphs discuss how to connect to a SYBASE SQL Server from an ODBC application. The remainder of the article assumes that you are connected to a SQL Server called "Mysqlsvr." This server should be set up as an ODBC data source, using the ODBC Control Panel utility, as follows:

1. Open ODBC Control Panel and click Add.

2. Select SQL Server from the list of installed drivers and click OK.

3. In the ODBC SQL Server Setup dialog box, type a data source name and a description (optional).

   Note that a single server can function as multiple ODBC data sources, since each database in the server can be a data source. The data source name need not be the same as the server name.

4. Enter the true name of the server, then enter the network address.

   Because a SYBASE SQL Server on UNIX uses sockets and not named pipes, the network address must contains the IP address and the port identification number. This string is in the form "ip_address,port" (for example, "11.1.14.40,3180").

   For a SYBASE SQL Server on a VAX system, this string should be in the form "<node address>,<process_id>", where <node address> is the DECnet node address of the server and <process_id> is the process identification number to use for the connection (for example, "1.997,141").

   The Network Library field should contain the name of the Net-Library you need to use. This is dependent on what network you are using. For example, if you are using the FTP Software product PCTCP, the Net-Library name is "wdbftptc".

5. Click Options to specify a database name or a language name.

6. Click OK to go back to the first screen.

7. Run the INSTCAT.SQL script file.

   NOTE: This is a file containing SQL statements that will create certain stored procedures needed to process ODBC calls. Without these procedures, Microsoft Access cannot attach a SQL Server table and Visual Basic is not able to function correctly.

   At the UNIX command line or the MS-DOS prompt, type the following:

         isql -S<servername> -Usa -P<sa-password> -i<path>\instcat.sql
   

   <data-source-name>=SQL Server

   data-source-name=<net-library-name>,<network address>

REFERENCES

For more information about setting up Microsoft Access to use SQL databases, see Microsoft Access "User's Guide," version 2.0, Chapter 1, "Setting Up Microsoft Access," page 8

For more information about installing stored procedures using the Microsoft Access Help menu, make sure you have set up a SQL Server datasource using the ODBC Administrator in the Windows Control Panel. Then, search for "Sybase SQL Server," and then "Installing ODBC Drivers and setting up Data Sources." In the Installing ODBC Drivers And Setting Up Data Sources Help window click the "SQL Server Driver" hotlink. In the SQL Server Driver Help window click the "Installing the Catalog Stored Procedures" hotlink.