How SQLOpen works in VBA

 

SQLOpen

SQLOpen establishes a connection to a data source.This function is contained in the Xlodbc.xla add-in.

Before use of this function, one must establish a reference to the add-in by using the References command .

Syntax:

SQLOpen(ConnectionStr, OutputRef, DriverPrompt)

ConnectionStr   Required. Supplies the information required by the driver being used to connect to a data source; must follow the driver's format. ConnectionStr supplies the data source name and other information, such as user ID and passwords, that the driver requires to make a connection. You must define the data source name (DSN) used in ConnectionStr before you try to connect to it.

OutputRef   Optional. A Range object (must be a single cell) that contains the completed connection string. Use OutputRef when you want SQLOpen to return the completed connection string to a worksheet.

DriverPrompt   Optional. Specifies whether the driver dialog box is displayed and, if it is, which options are available in it. Use one of the values described in the following table. If DriverPrompt is omitted, SQLOpen uses 2 as the default.

List of Values:

 

Value	Meaning
1	The driver dialog box is always displayed.
2	The driver dialog box is displayed only if information provided by the connection string and the data source specification aren’t sufficient to complete the connection. All dialog box options are available.
3	The same as 2 except that dialog box options that aren’t required are dimmed (unavailable).
4	The driver dialog box isn’t displayed. If the connection isn’t successful, SQLOpen returns an error.

 

SQLOpen Output Values :

If successful, SQLOpen returns a unique connection ID number. Use the connection ID number with the other ODBC functions.

If SQLOpen is unable to connect using the information you provide, it returns Error 2042. Additional error information is placed in memory for use by SQLError.

Example:

This example runs a query on the Northwind database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order.

DatabaseName = "Northwind" QueryString = _     "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)" Chan = SQLOpen("DSN=" & DatabaseName) SQLExecQuery Chan, QueryString Set output = Worksheets("Sheet1").Range("A1") SQLRetrieve Chan, Output, , , True SQLClose Chan

you may like to visit below :

 How ADO(ActiveX Data Objects)DB connection and Recordset work

How to connect to Teradata and extract DB using VBA

How to connect to Cisco Information Server