Getting ADODB available for MySQL
-
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
-
15 years agoby chrisdickson
Chris Dickson
Woking, England, United KingdomJoined 15 years agoSee 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/ -
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! -
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 -
15 years agoby TrevorG
Trevor Gandon
Medway Towns (Chatham), England, United KingdomJoined 15 years agoTry (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 -
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 -
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 -
15 years agoby chrisdickson
Chris Dickson
Woking, England, United KingdomJoined 15 years agoEach 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 -
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 -
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. -
15 years agoby chrisdickson
Chris Dickson
Woking, England, United KingdomJoined 15 years agoGlad 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! -
15 years agoby TrevorG
Trevor Gandon
Medway Towns (Chatham), England, United KingdomJoined 15 years agoJust 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 -
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.
Post a reply
MySQL forum discussion
-
mysql database
by olivea19 (3 replies)
-
Using PHP and IIS to Create a Discussion Forum
by kellyj481 (48 replies)
-
How to access a MySQL database with .NET
by wzt198531 (10 replies)
-
MySQL Tutorial
by jodibahu (8 replies)
-
how create multilanguage website in php using unicode database as mysql?
by osmancarik (2 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).