Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to access database?

    I'm a newbie and spent some time looking at tutorials but didn't understand anything.

    How do access database from Visual Basic 6? I have MS Access 2000. I learned that 2000 is not compatible with VB6. What options/solutions do I have?

    My intention is to search the database. I want to put a textbox and a submit/command button and search fields.
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2006
    Posts
    33
    You can use ADO to connect to an access database in VB6 - make sure your computer has the latest version of MDAC installed and add a reference to "Microsoft ActiveX Data Objects 2.x Library".

    Basic ADO Code to connect to an Access database and read data from a table goes something like this:
    Code:
        Dim connectionString As String
        Dim query As String
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim rs As New ADODB.Recordset
        
        query = "SELECT MyCol from MyTable"
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;User Id=admin;Password=;"
        
        cn.connectionString = connectionString
        cn.Open
        cmd.ActiveConnection = cn
        cmd.CommandText = query
    
        Set rs = cmd.Execute
        
        While rs.EOF <> True
            MsgBox (rs(0))
            rs.MoveNext
        Wend
            
        rs.Close
        cn.Close
    This code will work in VB6 and also office applications. If you use late binding (using createobject syntax) it will also work in vbscript files.
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    What does it mean:
    Compile error:
    User-defined type not defined
    Arrow points to:
    Code:
    Private Sub Form_Load()
        Dim connectionString As String
        Dim query As String
        Dim cn As New ADODB.Connection
    Highlighted section
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    Join Date
    Apr 2006
    Posts
    33
    Quote Originally Posted by gilgalbiblewhee
    What does it mean:


    Arrow points to:
    Code:
    Private Sub Form_Load()
        Dim connectionString As String
        Dim query As String
        Dim cn As New ADODB.Connection
    Highlighted section
    You need to add a reference to the "Microsoft ActiveX Data Objects 2.x Library". I think this is under tools, references (I don't have VB6 installed on my PC anymore)

    You can also use late binding - early binding is prefered, but you could use something like this without adding the reference:

    Code:
        Dim connectionString As String
        Dim query As String
        Dim cn
        Dim cmd
        Dim rs
        
        Set cn = CreateObject("ADODB.Connection")
        Set cmd = CreateObject("ADODB.Command")
        Set rs = CreateObject("ADODB.Recordset")
        
        query = "SELECT MyCol from MyTable"
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;User Id=admin;Password=;"
        
        cn.connectionString = connectionString
        cn.Open
        cmd.ActiveConnection = cn
        cmd.CommandText = query
    
        Set rs = cmd.Execute
        
        While rs.EOF <> True
            MsgBox (rs(0))
            rs.MoveNext
        Wend
            
        rs.Close
        cn.Close
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  5. #5
    Join Date
    Jul 2004
    Posts
    494
    I was waiting for your answer having my Outlook but no email came. But I checked and you had answered. Weird.

    So if I use the late binding then I don't need to use MDAC reference?
    Code:
       Set cn = CreateObject("ADODB.Connection")
    Compile error:
    Invalid outside procedure
    Is this the correct way of presenting it (do I have to use sub and end sub)?
    Code:
        Dim connectionString As String
        Dim query As String
        Dim cn
        Dim cmd
        Dim rs
        
        Set cn = CreateObject("ADODB.Connection")
        Set cmd = CreateObject("ADODB.Command")
        Set rs = CreateObject("ADODB.Recordset")
        
        query = "SELECT text_data from bible"
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\wheelofgod\kjv.mdb;User Id=admin;Password=;"
        
        cn.connectionString = connectionString
        cn.Open
        cmd.ActiveConnection = cn
        cmd.CommandText = query
    
        Set rs = cmd.Execute
        
        While rs.EOF <> True
            MsgBox (rs(0))
            rs.MoveNext
        Wend
            
        rs.Close
        cn.Close
    Compare bible texts (and other tools):
    TheWheelofGod

  6. #6
    Join Date
    Jul 2004
    Posts
    494
    Quote Originally Posted by Wiseman82
    You need to add a reference to the "Microsoft ActiveX Data Objects 2.x Library". I think this is under tools, references (I don't have VB6 installed on my PC anymore)
    Does it come with the software?
    Compare bible texts (and other tools):
    TheWheelofGod

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    In the visual basic IDE, THe Projects Meny, References Sub-Menu. Scroll down to Microsoft ActiveX Data Objects 2.x Library and click the Checkbox for the latest version.

    Also, ref this ADO Tutorial
    Lou
    使大吃一惊
    "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


  8. #8
    Join Date
    Jul 2004
    Posts
    494
    Quote Originally Posted by loquin
    In the visual basic IDE, THe Projects Meny, References Sub-Menu. Scroll down to Microsoft ActiveX Data Objects 2.x Library and click the Checkbox for the latest version.

    Also, ref this ADO Tutorial
    Aah ok. The latest I have is Microsoft ActiveX Data Objects 2.1. And I'm going to look at the ADO tutorial.

    If I download the latest MDAC which directory do I extract in?
    Compare bible texts (and other tools):
    TheWheelofGod

  9. #9
    Join Date
    Jul 2004
    Posts
    494
    Ok I got it. The reason why it was showing the error was because I hadn't placed the code inside the:
    Code:
    Private Sub Form_Load()
    
        Dim connectionString As String
        Dim query As String
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim rs As New ADODB.Recordset
        Dim Text1
        
        'Text1 = Request("Text1")
        
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\kjv.mdb;User Id=admin;Password=;"
        query = "SELECT * from bible 'WHERE Text1 LIKE " & Text1
        
        cn.connectionString = connectionString
        cn.Open
        cmd.ActiveConnection = cn
        cmd.CommandText = query
    
        Set rs = cmd.Execute
        
        
        While rs.EOF <> True
            MsgBox (rs("book_title") & " " & rs("chapter") & ":" & rs("verse") & " " & rs("text_data"))
            rs.MoveNext
            
        Wend
    
        rs.Close
        cn.Close
    End Sub
    How does the code take a string and search through the database?
    Last edited by gilgalbiblewhee; 06-08-06 at 23:25.
    Compare bible texts (and other tools):
    TheWheelofGod

  10. #10
    Join Date
    Jun 2006
    Posts
    5
    The correct syntax is

    query = "SELECT * from bible WHERE Text1 LIKE " & Text1 & "%"

    instead of

    query = "SELECT * from bible 'WHERE Text1 LIKE " & Text1

    Bye, Brainjar

  11. #11
    Join Date
    Jun 2006
    Posts
    5
    The correct syntax is, if field name to match contents is 'book'

    query = "SELECT * from bible WHERE book LIKE " & Text1.Text & "%"
    instead of

    query = "SELECT * from bible 'WHERE Text1 LIKE " & Text1

    'cause I think you make a little confusion.

    Bye, Brainjar

  12. #12
    Join Date
    Jul 2004
    Posts
    494
    True. I had put the single quote to block that part of the script so thanks for the tip. But let me ask, how can I put a cancel/close commands to stop the running of the messagebox? (the records are thousands of records long).
    Code:
        While rs.EOF <> True
            MsgBox (rs("book_title") & " " & rs("chapter") & ":" & rs("verse") & " " & rs("text_data"))
            rs.MoveNext
            
        Wend
    Or do you have a better suggestion? Instead of coding msgBox what way can I put a table of records?
    Compare bible texts (and other tools):
    TheWheelofGod

  13. #13
    Join Date
    Jun 2006
    Posts
    2
    Why not use a Datagrid?

  14. #14
    Join Date
    Jul 2004
    Posts
    494
    That's a great idea.
    I have:
    Code:
    Private Sub MSFlexGrid1_Click()
         MSFlexGrid1.FormatString = "Book /" & _
            "Chapter /" & _
            "Verse /" & _
            "Text"
             
        Dim connectionString As String
        Dim query As String
        Dim cn As New ADODB.Connection
        Dim cmd As New ADODB.Command
        Dim rs As New ADODB.Recordset
        Dim Text1
         
          
          Data1.DatabaseName = App.Path & "\kjv.mdb"
         
          'Data1.RecordSource = "select * from bible order by id"
    
    Set cmd.ActiveConnection = cn
    cmd.CommandText = "select * from bible order by id"
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
    
         
        For x = 1 To MSFlexGrid1.Rows - 1
            Do Until rs.EOF = True
                MSFlexGrid1.TextMatrix(x, 0) = rs("book_title")
                MSFlexGrid1.TextMatrix(x, 1) = rs("chapter")
                MSFlexGrid1.TextMatrix(x, 2) = rs("verse")
                MSFlexGrid1.TextMatrix(x, 3) = rs("text_data")
                rs.MoveNext
            Loop
        Next x
         
       End Sub
    But it says:
    Run-time error '3709':
    The application requested an operation on an object with a reference to a closed or invalid Connection object.
    Also the Book/Chapter/Verse/Text are all placed under one field.
    Compare bible texts (and other tools):
    TheWheelofGod

Posting Permissions

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