KB#00313-Program samples demonstrating ODBC access from Visual Basic 4, 5 and 6
Title:
Program samples demonstrating ODBC access from Visual Basic 4, 5 and 6
Description:
=================================================================
Example 1:
Doesn't use a Data Access Object. Note the SQLPassThrough flag on the OpenRecordset command. This will produce the highest level of optimization possible. Also note that this example assumes that "myds" is a valid datasource as defined in the ODBC administrator, and that that "table" and "field1" refer to a valid table within the datasource and a valid field within the table.
Dim db As Database
Dim ws As Workspace
Dim rs As Recordset
rem set the first string to "" and the second to "ODBC;" to have the user select a datasource from the ODBC administrator during run-time. Set db = Workspaces(0).OpenDatabase("myds", False, False, "ODBC;Database=myds") SQL$ = "SELECT field1 FROM table WHERE field1 BETWEEN 'A' and 'B' ORDER BY field1" Set rs = db.OpenRecordset(SQL$, dbOpenDynaset, dbSQLPassThrough)
Do Until rs.EOF
List1.AddItem rs("field1")
rs.MoveNext
Loop
Changes the window's title to indicate how many records were selected Form1.Caption = "Records Found: " + Str(rs.RecordCount) db.Close
=================================================================
Example 2:
Using a Data Access Object to connect to the datasource. Note that there is no code, just the data object and potentially other data-aware controls. Again, assume that "myds" is a valid datasource
1) Include a Data object with these properties
Connect: ODBC;DSN=myds
DatabaseName: can be blank if Connect references a valid ODBC datasource
RecordSource: This can either be a table, or a valid SQL statement to produce a resultant table
2) Include data-aware controls (we'll use a textbox for this example)
Datafield: select a valid field from the RecordSource
Datasource: select the data object from step 1
=================================================================
Example 3:
This examples uses a RDO resultset in VB5 to get the information. Ensure that you've added the 'Microsoft Remote Data Object 2.0' to the project via the Project/References menu option.
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String
cn.Connect = "DRIVER=BASIS ODBC Driver 32-BIT;Bus14=No;FastConnect=Yes;ReadOnly=Yes;UserID=qatest;Config=f:\vb5_projects\Call_log_stats\Config.tpm"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt
SQL = "select * from customer where last_name in ('JONES','SMITH')"
Set rs = cn.OpenResultset(SQL, rdOpenKeyset, rdConcurReadOnly, rdAsyncEnable + rdExecDirect)
Debug.Print "Executing ";
While rs.StillExecuting
Debug.Print ".";
DoEvents
Wend
Do
Debug.Print String(50, "-") _
& "Processing Result Set " & String(50, "-")
For Each cl In rs.rdoColumns
Debug.Print cl.Name,
Next
Debug.Print
Do Until rs.EOF
For Each cl In rs.rdoColumns
Debug.Print cl.Value,
Next
rs.MoveNext
Debug.Print
Loop
Debug.Print "Row count="; rs.RowCount
Loop Until rs.EOF
=================================================================
Example 4:
This example uses RDO in VB5 to update an ODBC record. Because version 1.1 of our ODBC driver does not support cursors, it is not possible to update the data using the Recordset object; updates can only be done using SQL. Ensure that you've added the 'Microsoft Remote Data Object 2.0' to the project via the Project/References menu option.
Private Sub Command1_Click()
Dim cn As New rdoConnection
Dim rs As rdoResultset
Dim qy As New rdoQuery
cn.CursorDriver = rdUseOdbc
cn.Connect = "driver={BASIS ODBC Driver 32-BIT};dsn=�chile�;FastConnect=Yes;ReadOnly=No;Config=d:\chile\config.tpm"
cn.EstablishConnection
' s$ = "UPDATE CUSTOMER SET FIRST_NAME='John' where CUST_NUM='000001'"
' rdotest.Execute s$, rdExecDirect
' rtnval = MsgBox("update complete", vbInformation)
With qy
.Name = "TestQuery"
.SQL = "Select * from Customer"
.RowsetSize = 1
Set .ActiveConnection = cn
Set rs = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
Debug.Print rs.Updatable
End With
rs.MoveFirst
Do Until rs.EOF
'rs.Edit
'rs!First = rs!First + " 2"
'rs.rdoColumns.Item("FIRST").Value = rs.rdoColumns.Item("FIRST").Value & " 2"
s$ = "update customer set first_name = '1' where first_name='" + rs!First + "'"
cn.Execute s$, rdExecDirect
'rs.Update
rs.MoveNext
Loop
End
rs.MoveFirst
Do Until rs.EOF
Debug.Print rs.rdoColumns.Item("FIRST").Value
rs.MoveNext
Loop
rs.Close
qy.Close
cn.Close
End Sub
=================================================================
Example 5:
This example uses ADO in VB6 to display all of the records in the BASIS Chile Company demo database that shipes with the 2.0x version of the BASIS ODBC Driver. Ensure that you've added the Microsoft ActiveX Data Objects to the project via the Project/References menu option. Note that you can also create a fully functioning example with ADO via Visual Basic's VB Application Wizard.
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=BASIS Chile Company Database;uid=;pwd=;"
Set adoRS = New Recordset
adoRS.Open "select * from CUSTOMER Order by CUST_NUM", db, adOpenStatic, adLockOptimistic
Do Until adoRS.EOF = True
Debug.Print Trim$(adoRS!Cust_Num) + " " + Trim$(adoRS!First_Name) + " " + Trim$(adoRS!Last_Name)
adoRS.MoveNext
Loop
adoRS.Close
db.Close
Last Modified: 12/09/1998 Product: BASIS ODBC Driver Operating System: Windows
BASIS structures five components of their technology into the BBx Generations.
