Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008

    Unanswered: Connecting to an SQL2000 database using ADODB and ADODC in VB6

    Hi all,
    Of late I have been trying to use VB6 to connect to an sql2000 database but i am findin it complicated. I have tried to do so to an MS.Access db and this was the code i used.

    Private Sub Form_Load()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset

    con.ConnectionString = "datasource = " & App.Path & "\database.mdb;" & _
    "provider = Microsoft.jet.Oledb.4.0;"

    'rs.Open "Customers", con, adOpenKeyset, adLockOptimistic

    Set rs = Nothing
    Set con = Nothing

    End Sub

    Private Sub cmdsave_Click()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset

    rs!customerid = Txtcustomerid.Text
    rs!customername = Txtcustomername.Text
    rs!Phone = Txtphone.Text
    rs!Region = Txtregion.Text
    rs!Address = Txtaddress.Text

    Set rs = Nothing
    Set con = Nothing

    End Sub

    I would like to know how i can use the same kind of code to connect to an sql2000 database. And in case there is any problem with the above code please assist

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    You have a scope problem. (Once you get your scope issues straightened out, the same code can be used to connect to SQL Server databases with just a change of the connection string.) Please review the scope installment in this standards/practices tutorial at our VB sister site, XtremeVBTalk.Com.

    Dim rs and con at the form level, not at the individual subroutine level. Remember, when you define an object or variable at a subroutine level, the object is only available within that sub... In your examples, the connection and subroutine are declared, instantiated, opened, closed, and released from memory, all within the sub in question... Nothing defined within one sub will ever be available outside that sub. But, if you define a variable/object at the form level, it is accessible from any sub/function within the form.

    Since you appear to want to use the ado objects in several subs...
    • Declare them at the form level
    • Instantiate them and open them (the connection, at least) in the form_load event
    • close them & release them in the form_unload event.

    Secondly. If at all possible, it is usually highly desirable to not use ODBC/DSNs for the connection - instead, use OleDB. Look at the FAQ in the first thread in this VB forum; the third post discusses building connection strings, and the OleDB connection string builder uses the Windows API to build your connection string & place it in the clipboard.

    I can also recommend this ADO Tutorial at the same site.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts