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.