Dynamically creating access to a database using ADO controls in VB6

Source: Internet
Author: User
Tags odbc odbc connection table name
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:

    1. Open ADO to the database connection, initialize the ADO recordset set.
    2. 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.
    3. 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.




Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.