Article ID: 124218
Article Last Modified on 10/11/2006
This function Does the following
----------------------------------------------------------------------
SQLBind Specifies storage for a result column
SQLClose Closes a data source connection
SQLError Returns detailed error information
SQLExecQuery Sends a query to a data source
SQLGetSchema Gets information about a connected data source
SQLOpen Establishes a connection to a data source
SQLRetrieve Retrieves query results
SQLRetrieveToFile Retrieves query results and places them in a file
SQLRequest Connects with an external data source, performs
the query, and returns the result set
Sub UsingSQLOpen()
Dim Chan As Variant
' Establish a connection to any data source and place
' the connection string to the selected data source in
' the active cell.
Chan = SQLOpen("", ActiveCell)
' Close the connection to the selected data source.
SQLClose (Chan)
End Sub
Sub RetrieveData()
Dim Chan As Variant
' Establish a connection to the NWind data source.
Chan = SQLOpen("DSN=Nwind")
' Execute a query to retrieve the ORDER_ID and CUSTMR_ID
' from the Orders table where EMPLOY_ID is 555.
SQLExecQuery Chan, _
"SELECT Order_id, Custmr_id FROM Orders.dbf WHERE " & _
"Employ_id='555'"
' Return the data to cell A1 on the active sheet.
SQLRetrieve Chan, ActiveSheet.Range("A1"), , , True
' Close the connection to the NWind data source.
SQLClose (Chan)
End Sub
Sub XLODBCErrHandler()
Dim ErrMsgs As Variant
Dim ErrCode As Variant
' Call SQLError to return error values to the variant
' ErrMsgs.
ErrMsgs = SQLError()
' Display each item in the ErrMsgs variant array.
For Each ErrCode In ErrMsgs
MsgBox ErrCode
Next
End Sub
Sub ProduceError()
Dim Chan As Variant
' Establish a connection to a nonexistent data source.
Chan = SQLOpen("DSN=Just Testing")
' If there was a problem establishing a connection,
' SQLOpen will return an error to the variable Chan. If
' Chan is an error then call the procedure
' XLODBCErrHandler to display the error message.
If IsError(Chan) Then
XLODBCErrHandler
Exit Sub
End If
' Close the connection if one was established.
SQLClose (Chan)
End Sub
Sub GetTableNames()
Dim Chan As Variant
Dim TableNames, TName As Variant
Dim DatabaseName As Variant
' Establish a connection to the NWind data source.
Chan = SQLOpen("DSN=NWind")
' Get the name of the database on the connection
DatabaseName = SQLGetSchema(Chan, 7)
' Get the table names for the database on the
' connection.
TableNames = SQLGetSchema(Chan, 4, DatabaseName & ".")
' Display the name of each table.
For Each TName In TableNames
MsgBox TName
Next TName
' Close the connection to the NWind data source.
SQLClose (Chan)
End Sub
Sub GetFieldNames()
Dim Chan As Variant
Dim Fields As Variant
Dim i As Integer
' Establish a connection to the NWind data source.
Chan = SQLOpen("DSN=NWind")
' Get the field names for the ORDERS table.
Fields = SQLGetSchema(Chan, 5, "ORDERS")
' Fields is a two-dimension Variant array; each row
' contains field name and the field type. Display an
' alert message for each row to show each field name.
For i = 1 To UBound(Fields)
MsgBox Fields(i, 1)
Next i
' Close the connection.
SQLClose (Chan)
End Sub
Sub UsingSQLBind()
Dim Chan As Variant
Dim NumRows, NumCols As Variant
' Establish a connection to the NWind data source.
Chan = SQLOpen("DSN=NWind")
' Run a query to the Orders table to retrieve the
' fields: Order_ID, Custmr_ID, Order_Amt.
NumCols = SQLExecQuery(Chan, _
"SELECT Order_ID, Custmr_ID, " & _
"Order_Amt FROM Orders.Dbf")
' Bind the first column (Order_ID) to cell A1 of the
' Activesheet, Bind the second column (Custmr_ID) to
' cell D1 of the Activesheet, Bind the third column
' (Order_Amt) to cell B1 of the active sheet.
SQLBind Chan, 1, Range("A1")
SQLBind Chan, 2, Range("D1")
SQLBind Chan, 3, Range("B1")
' Return the data and the field names.
NumRows = SQLRetrieve(Chan, , , , True)
' Close the connection to the NWind data source.
SQLClose (Chan)
End Sub
Sub ReturnDataToArray()
Dim EmpNames As Variant
' Query the EMPLOYEE table in the NWind data source for
' the first and last name of each employee and return
' the result set to the variant array EmpNames.
EmpNames = SQLRequest("DSN=NWind", _
"Select Last_Name, First_Name from EMPLOYEE")
' For each row in the array Empnames, display the first
' and last name of the employee.
For i = 1 To UBound(EmpNames, 1)
MsgBox EmpNames(i, 1) & "," & EmpNames(i, 2)
Next
End Sub
Function StringToArray(Query As String) As Variant
Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String
' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.
NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String
' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.
For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i
' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.
StringToArray = Temp
End Function
Sub ExecuteLongSQL()
Dim Chan As Variant
Dim LongSQL As String
Dim NumRows, NumCols As Variant
' Set LongSQL to a long query (> 127 characters).
' The following statement should be entered as one
' single line.
LongSQL = "SELECT CUSTMR_ID, EMPLOY_ID, ORDER_AMT, " &_
"ORDER_DATE, ORDER_ID FROM ORDERS WHERE EMPLOY_ID=" _
& "'555' AND ORDER_AMT>=100"
' Establish a connection to the data source NWind.
Chan = SQLOpen("DSN=NWind")
' Execute the long query statement by passing
' SQLExecQuery, a variant array which is returned from
' the function StringToArray.
NumCols = SQLExecQuery(Chan, StringToArray(LongSQL))
' Return the data to the Active cell on the Active
' sheet.
NumRows = SQLRetrieve(Chan, ActiveCell)
' Close the channel established to NWind.
SQLClose Chan
End Sub
Sub ExecuteStoredProcedure()
Dim Chan As Variant
'Open a channel to a SQL Server data source
Chan = SQLOpen("DSN=SQLServer")
'Execute the stored procedure "sp_who" and return the
'results to the activecell on the active sheet.
SQLExecQuery Chan, "Execute sp_who"
SQLRetrieve Chan, ActiveCell
'Terminate the channel
SQLClose Chan
End Sub
114992 GP Fault in XLODBC.DLL Using SQLExecQuery or SQLRequest
Additional query words: 5.00a 5.00c 7.00a greater larger odbc xl97 xl vba XL
Keywords: kbdtacode kbhowto kbprogramming kbualink97 KB124218