Back to discussion

Getting ADODB available for MySQL

Last post was 09 Dec 2005 at 15:50
mysql , vb6 New Orleans, United States
  • 15 years ago
    In furthering one of my projects, I need to connect to a MySQL server as well as an active Excel sheet.  The excel part works great, but I am having so much trouble getting MySQL to work.  I've found SO many examples on this site that say to do something on the order of:

    Dim Variable As New ADODB.Connection
    Dim Variable As ADODB.Recordset

    The problem I have is I never have the option for ADODB.  If I do a dim or set as - the closest thing I get is ADODC, and if I do a dim or set as new - the closest thing I get is ADOX.  Now I know some options don't appear in that nice drop down window but will work if you type them out.  This is not the case.  I have setup a System DSN(ODBC Data Source) that I can manually connect to by opening Visual Data Manager under Add-ins and selecting open ODBC.  I then get a MySQL window that I can perform all my farmilar sql statements with.  

    So I have the correct driver installed and all the login information correct as I can connect, view, modify, etc.  The problem is I want to do this in code and not manually.  To try and get the ADODB function I went to references and turned on "Microsoft DAO 3.6 Object Library" as well as "Microsoft SQL Parser Object Library 1.0".  

    The error I get with:

    Dim sqlRecordSet as New ADODB.Connection

    is "User-defined type not defined."  Running it one line at a time this is the line that causes the crash.  Am I simply missing a reference, or is there a better way than ADO?  I won't be doing much to the SQL database.  I just need to connect to it and store its contents to a structed array to use in conjuction with the data from the excel form.  

    Any help would be greatly appreciated.

    -Tril

    Report abuse
  • 15 years ago
    See if you can find a reference to Microsoft MDAC 2.x (the reference on the dev machine I have immediate access to is Microsoft ActiveX Data Objects 2.7 Library) and add that, instead of the SQL Parser and DAO. I believe, although someone may correct me, that ADO and DAO are slightly different things - so, MDAC is all you need to get to the ADODB objects.
    MDAC 2.8 SP1 is the latest available MDAC release. http://www.microsoft.com/data/mdac/
    Report abuse
  • 15 years ago
    Thanks for the quick reply.  I don't have MDAC as an option - the closest I could find was Microsoft Data Access Components.  Atleast that would be MDAC abbreviated =(   but it didn't work.  I'll google that library, and see what I can find.

    EDIT =>  I was writting as you were making your edit =)  The Active X Controls did it for me.  Thanks a lot!
    Report abuse
  • 15 years ago

    I'm so close I can feel it - I'm sure I just have some syntax to work out.  I can't seem to find where to properly enter my SQL statements once I have a connection(I've verified the SQL statements with the VisData Plugin, so table and field names are correct).  I created a temp. SQL server for testing so that I don't damage the company's database(and lose my job...).  The temp SQL database has a system DSN setup like the real deal, with the name epace, login epace, password epace, and a local IP address off 192.163.100.101.  I seem to be able to make a connection using:


       Dim conRecordset As New ADODB.Connection
       Dim rsRecordset As ADODB.Recordset
       Dim strSQL As String
       Dim MasterID() As String
       Dim RecordCount As Long
       Dim x As Long


       strSQL = "SELECT ccmasterid FROM job"
       conRecordset.Open "epace", "epace", "epace"
       Set rsRecordset = conRecordset.OpenSchema(adSchemaSchemata)
       RecordCount = rsRecordset.MaxRecords
       ReDim MasterID(RecordCount)
       MsgBox (RecordCount)


    However, this obviously returns 0 for RecordCount because the SQL statement is never actually used.  If I swap out the first "epace", with strSQL I get "Run-time error, Data source name not found an no default driver specified.".  I found another example on the site so tried that - the code looks like:


       Dim rstRcordSet As New ADODB.Connection
       Dim strSQL As String
       strSQL = "SELECT ccmasterid FROM job"
       Set rstrecordset = conConnection.Execute(strSQL)
       Do While Not rstrecordset.EOF
           If Not IsNull(rstrecordset!ccmasterid) Then Text1.Text = Text1.Text & rstrecordset!ccmasterid
       Loop


    This causes a crash on the "Set rstrecordset = conConnection.Execute(strSQL)" statement with an error of "Object Required".


    Any help with this is greatly appreciated.


    -Tril

    Report abuse
  • 15 years ago
    Try (untested)
    Code:

    Dim conRecordset As New ADODB.Connection
    Dim rsRecordset As ADODB.Recordset
    Dim strSQL As String
    Dim MasterID() As String
    Dim RecordCount As Long
    Dim x As Long

    strSQL = "SELECT ccmasterid FROM job"
    conRecordset.Open "epace", "epace", "epace"

    Set rsRecordset = New ADODB.Recordset
    rsRecordset.Open strSQL, conRecordset

    Do Until rsRecordset.EOF

      'Process record

      rsRecordset.MoveNext
    Loop


    Trevor
    Report abuse
  • 15 years ago
    Thanks a lot man!  I made a few changes but have a msgbox filled with the test data, so I can do the rest from there.  It looks like:

       Dim conRecordset As New ADODB.Connection
       Dim rsRecordset As ADODB.Recordset
       Dim strSQL As String
       Dim MasterID() As String
       Dim RecordCount As Long
       Dim x As String

       strSQL = "SELECT ccmasterid FROM job"
       conRecordset.Open "epace", "epace", "epace"
       Set rsRecordset = New ADODB.Recordset
       rsRecordset.Open strSQL, conRecordset
       
       Do Until rsRecordset.EOF
           x = rsRecordset.GetString
           MsgBox (x)
           rsRecordset.MoveNext
       Loop

    -Tril
    Report abuse
  • 15 years ago
    Man thought I had it - but we are almost there.  The  .getstring returns the entire select statement as one variable delemited by a space.  This would not be so bad(I can modify strings easy), except that the string exceeds the max length that the variable can hold being declared as string.  It seems that the query is considering the entire select statement to contain one large record.  Reason for this is I had to add an 'on error' statement because the "rsRecordset.MoveNext " command kept failing.  I put a counter in there and it always broke the loop at the first run.  Also, rsRecordset.MaxRecords returns the value 1.  So:

    1) Is there a way to change the way I'm querying so that each entry will be a unique record(makes the suggested loop work. and a index as long 'should' be enough to hold all the records).

    2) Is there a string variable type that is larger than 'as String' ?  I would need it to be on the order of 7-8x larger to handle the size of the database now... and it would only grow as time goes on(average additional 150-200 entries a day).

    Thanks for all the help.

    -Tril
    Report abuse
  • 15 years ago
    Each entry is an individual record within the recordset.

    The way to tell would be to display the record count for the rsRecordset:

    rsRecordset.Open strSQL, conRecordset

    MsgBox rsRecordset.RecordCount
     
    Do Until rsRecordset.EOF

    If that is 1, then you have something wrong somewhere.

    The .GetString is also designed to return the whole recordset. Try changing your loop to:

    Do Until rsRecordset.EOF
          x = rsRecordset.Fields("ccmasterid").Value
          MsgBox (x)
          rsRecordset.MoveNext
    Loop
    Report abuse
  • 15 years ago
    rsRecordset.RecordCount returns -1, but the rsRecordset.Fields("ccmasterid").Value did work.  I just had to add a if is not null part to keep it from trying to advance past the last record.  Would be nice if the record count would give that number because it would be usefull information, but is not critical for what I'm doing.

    I really appreciate the crash course in SQL for VB  I don't get long on deadlines and they expect miracles here(Is that different from any programmers job?).

    -Tril
    Report abuse
  • 15 years ago

    EDIT => Ignore this post... I'm retarded.  The query is fast, it was the for loop displaying the results in a text box that was crawling.  I don't need that function, I was just using it to verify that I was pulling in correct data.  As soon as I removed that, and only displayed 1 record, the query was almost instant.   Thanks for the previous help.


    Just one last question here.  Everything works fine, but if I start to pull in multiple fields - the program crawls.  The SQL database I'm connecting too was not written at the company, so it has many fields we never use.  It's basically a job tracking/financing software called EPace.  If I take a look at the database, it has well over 10,000 fields in the database per record.  I think what is happening is that even though I'm only storing certain fields, it is still reading all the others that I don't use.  So is there a way to force a query to only return a subset of fields?  I thought that was the purpose of the SELECT statement.  Anyway, my code right now looks like:


       Option Explicit
       
       Private Type RecordStructure
           MasterIDNum As String
           JobPO As String
           JobDesc As String
           JobDate As String
       End Type


       Dim conRecordset As New ADODB.Connection
       Dim rsRecordset As ADODB.Recordset
       Dim strSQL As String
       Dim MasterID(10000) As RecordStructure 'going to make this dynamic if I can ever get .maxrecords to work
       Dim x As Integer
           
       strSQL = "SELECT job.ccmasterid, job.ccponum, job.ccdescription, job.ccpromisedate FROM job WHERE  job.ccmasterid>94000 ORDER BY job.ccmasterid"
           
       conRecordset.Open "epace", "epace", "epace"
       Set rsRecordset = New ADODB.Recordset


       rsRecordset.Open strSQL, conRecordset


       x = 1
       Do Until rsRecordset.EOF
           With MasterID(x)
               .MasterIDNum = rsRecordset.Fields("ccmasterid").Value 'this field will never be null
               If Not IsNull(rsRecordset.Fields("ccponum")) Then .JobPO = rsRecordset.Fields("ccponum").Value
               If Not IsNull(rsRecordset.Fields("ccdescription")) Then .JobDesc = rsRecordset.Fields("ccdescription").Value
               If Not IsNull(rsRecordset.Fields("ccpromisedate")) Then .JobDate = rsRecordset.Fields("ccpromisedate").Value
           End With
           x = x + 1
           rsRecordset.MoveNext
       Loop


    This loop does not take too long, about 45s to a full minute, but I need to pull in about 40 fields.  When I pull that many I can wait a full 10-15 mins for a transfer.  This is even with the >94000 which in my fake SQL database only has about 1000 records.  Any help is appreciated.

    Report abuse
  • 15 years ago

    Glad you're seeing some success!


    Interesting to hear about the RecordCount returning -1. This indicates that the underlying driver was unable to retrieve that information - perhaps the MySQL driver doesn't support it.


    Regardless, it sounds like you're well on your way!

    Report abuse
  • 15 years ago
    Just to confirm what you are trying to do.

    Of the fields in your query, which ones (if any) are indexed.

    It is the primary key or unique indexes that speed up database searches.
    If none of the query fields are indexed it means that the query has to search the whole table for matches which is considerably more time consuming.

    Also, the RecordCount property only gives an accurate value when the whole recordset has been traversed so you could try the following
    Code:

    Dim MasterID() As RecordStructure
    Dim x As Integer

    strSQL = "SELECT job.ccmasterid, job.ccponum, job.ccdescription, job.ccpromisedate FROM job WHERE  job.ccmasterid>94000 ORDER BY job.ccmasterid"
           
    conRecordset.Open "epace", "epace", "epace"
    Set rsRecordset = New ADODB.Recordset

    rsRecordset.Open strSQL, conRecordset

    If Not rsRecordset.EOF Then
      rsRecordset.MoveLast
      ReDim MasterID(rsRecordset.RecordCount - 1) As RecordStructure
      rsRecordset.MoveFirst
    End If

    x = 1
    Do Until rsRecordset.EOF
      'etc...


    Trevor
    Report abuse
  • 15 years ago
    Thanks for both replies.... its funny you mentioned the .movelast as I tried that before posting my previous message.  I think the problem lies in a crappy 3rd party SQL driver.  The driver I have to use is "PostgreSQL 8.1", and every search on the internet returns pages of upset users.  

    Anyway, the .movelast command returns a "Run -time Error: Rowset does not support fetching backward"  When I make a visual connection to the SQL database, luckliy there is an index.  So once I took out that display loop things sped up.  I just can't use many functions like .recordcount, .movefirst, .movelast, etc.  I believe that to be due to the driver I'm forced to use.  

    To make matters more interesting, I've been giving word that we will be purchasing and implementing a new tracking system in the next 2 months....  so all this may be for nothing.  Luckily, I always try to write my code modularly, so all I will have to change is the actual field names in the SQL query, and the connection paramaters.  Hopefully, this newer tracking system will have a better driver set.
    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

MySQL forum discussion

MySQL tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“Beware of bugs in the above code; I have only proved it correct, not tried it.” - Donald Knuth