ado| Create | dynamic | access | control | data | database
Using the ADO control can easily access the ODBC database in VB6, but by directly placing adods control to get ADO data connection is troublesome, we can create a common data module in the VB project, the initialization of the ADO control, establish the connection, close the connection and so on the operation all writes to the function, This allows you to share calls to this ADO connection in other modules of the project.
A complete ADO invocation operation is divided into the following steps:
- Open ADO to the database connection, initialize the ADO recordset set.
- Execute the query by writing to the SQL statement and return the query result recordset set, or you can perform the corresponding database operation by writing to the SQL statement.
- Releases the recordset set and closes the database connection.
It should be noted that each dynamically created ADO can only be invoked by one procedure at a time, and if multiple table parallel operations are required, multiple dynamic ADO may need to be established in a common data module.
The following are the relevant code:
'-----------------------------------------------------------------
' The following code is stored in the engineering module named My.bas
Public CONN as Adodb.connection ' defines the ADO Connection object
Public RS as Adodb.recordset ' defines an ADO Recordset object
'****************************
' Open a database connection
'****************************
Function Connopen ()
Dim ASTR as String
Set CONN = New adodb.connection
ASTR = Getdatabasepath ' mdb file database path
CONN. ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & ASTR &; Persist Security Info=false "
' This example's ODBC connection is a JET4.0 direct to MDB file connection, and if you use an ODBC data source you can use the following connection string:
' Provider=msdasql.1; Password= ""; Persist Security info=true;data source= data source name; Initial catalog= data Table library name
CONN. Open
Set RS = New Adodb.recordset
With RS do
ActiveConnection = CONN
CursorType = adOpenDynamic
LockType = adLockOptimistic
End With
End Function
' ****************************
' closes the database
' ****************************
Function connclose ()
Set RS = Nothing
CONN. Close
Set CONN = no
end Function
'**********************************************************
' Get the database path
' This example database is stored in the DBS subdirectory under the program directory, named Db1.mdb
'**********************************************************
Public Function Getdatabasepath () as String
Dim spath as String
If right$ (App.Path, 1) = "\" Then
spath = App.Path + "Dbs\"
Else
spath = App.Path + "\dbs\"
End If
Getdatabasepath = spath + "Db1.mdb"
End Function
' End of My.bas
'-----------------------------------------------------------------
The following example code is used for My.bas:
'-----------------------------------------------------------------
' Use the RS object to execute the SELECT statement
' tablename and fieldname the table name and field name respectively
' query results are saved in array s
Private Sub Runselectcommand ()
Dim S () as String
Dim i as Integer
i=0
Call My. Connopen
My. Rs. Open ' SELECT * from tablename '
while not RS. EOF
i=i+1
If not IsNull (my. Rs! FieldName) Then s (i) =cstr (my. Rs! FieldName)
RS. MoveNext
wend
call me. Connclose
End Sub
' Use the Conn object to execute the Update/delete/insert statement
' SQL statement in variable sSQL
Private Sub Runsqlcommand ()
Dim sSQL as String
Call My. Connopen
My. CONN. Execute sSQL
Call My. Connclose
End Sub
' For the DataGrid and DataReport these controls that need to be DataSource can do the following
' Open the data set of RS using the SELECT statement
Set OBJ. Datasource=my. Rs
'---------------------------------------------------------------------
This method is very useful for developing a simple and small mis system, can also be used in reports and datasheets, where a dynamic ADO object cannot perform two tasks at the same time when multiple-table operations and function nesting calls, and the subsequent SQL statement overwrites the SQL statement that was written first, and when it returns to the previous procedure, An error occurs because the field is not found. So if you might want to do a multiple-table operation, you can try to define several conn and Rs objects.