Accessing Synergy data with ADO
If you are writing an application in a language that can use Microsoft ActiveX Data Objects (ADO)—such as Visual Basic, ASP scripting languages, and so forth—you can access a Synergy database via ADO. Note the following:
- Your Synergy database must be prepared for ODBC access—i.e., you must have generated a system catalog, created a connect file, and so forth. Note that for client/server configurations, you’ll need a DSN for the Synergy database, but for stand-alone configurations, the DSN is optional.
- The application must have a valid connection string.
For information on using ADO.NET, see Accessing Synergy data in a .NET environment.
Creating connection strings that include a DSN
To access Synergy data using ADO, you need a connection string that includes all the information needed to make the connection. The following is the syntax for a connection string that uses a DSN:
DSN=dsn;[UID=[user];][PWD=[pwd]][;DBQ=[connect]];]
where dsn is a DSN, user is a username, pwd is the password for the user, and connect is the name of a connect file for the Synergy database.
Note the following:
- If you are connecting to a Synergy database on a remote server, the server information must be in the DSN.
- If you use the DBQ keyword in a connection string for Crystal Reports, you must prefix DBQ with <CRWDC> (for example, “<CRWDC>DBQ=sodbc_sa”).
The following ADO example uses Visual Basic. In this example, a DSN (MyDSN) is used to make a connection to the sample Synergy database.
<% DIM sConnStr sConnStr = "DSN=MyDSN;UID=DBADMIN;PWD=MANAGER;" DIM SQLQuery SQLQuery = "SELECT * FROM Customers" Set OBJdbConn = Server.CreateObject("ADODB.Connection") OBJdbConn.Open sConnStr Set RsCustomerList = OBJdbConn.Execute(SQLQuery) %>
You can connect to a local Synergy database without using a DSN. DSN-less connection strings are not supported for remote connections.
Use the following syntax for the connection string:
DRIVER=xfODBC;UID=[user][;PWD=[pwd]][;DBQ=[connect];]
or
DRIVER=xfODBC;UID=[user]/[pwd][/connect];
where user is the username, pwd is the password for the user, and connect is the name of the connect file for the database. If you don’t supply a username, password, or connect file name, and one is needed for the connection, the user will be prompted to supply the missing information.
With DSN-less connections, you cannot override the defaults for settings listed in step 5 of Adding a user or system DSN (settings such as the maximum number of columns that can be returned for a query).
The following example is a line of Visual Basic code that connects to the sample Synergy database without using a DSN:
conn.Open "DRIVER=xfODBC;UID=DBADMIN/MANAGER/sodbc_sa;"