mysql backend

[Michael J. Malinsky]

I know that VBA can be used to read/write info from/to a mysql database
to/from an Excel worksheet using a Sub routine and putting the results of a
query into a recordset then pasting the recordset into a cell on the sheet.
What I'm wondering is if a UDF can be used in a similar manner. The query
uses a table with unique values, so having a result with more than one value
would not occur. This is the code I have that does what I want, but I'd
like this to be in a UDF. I've tried and failed and would greatly
appreciate any assistance.

Private Sub test()

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long
Dim lString As String
Dim iRow As Integer
Dim iCol As Integer

myconn.Open "DSN=MySQL EPACE"
mySQL = "SELECT number FROM master where id = " & Range("C2").Value
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

myrows = myrs.RecordCount
iRow = 1
iCol = 1
x = 0

Worksheets("Sheet2").Range("A2").Select

With myrs
If .BOF Then GoTo NODATAHERE

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1

'COLLECT DATA
.MoveFirst
While Not .EOF
For Each AField In .Fields
Cells(iRow, iCol) = .Fields(x).Value
x = x + 1
iCol = iCol + 1
Next
iRow = iRow + 1
x = 0
iCol = 1
.MoveNext
Wend

End With

NODATAHERE:

myrs.Close
myconn.Close

End Sub

TIA

 

[Dick Kusleika]

Michael

It depends on what you want to return. Do you want to get one field from
one row, or are you planning on using an array formula to return all of the
fields? If you macro works now, then your almost there. You need to define
what arguments you're going to pass to the UDF. For instance, you might do
this

Function GetMySql(strWhere as String, lngField as Long) as Variant

then change this line

mySQL = "SELECT number FROM master where id = " & Range("C2").Value

to

mySQL = "SELECT number FROM master where id = " & strWhere

Then you don't need to loop through all the fields because you're simply
returning one value to one cell. Instead, you would have something like

myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value

You should only need a MoveFirst, because there should only be one record in
your recordset.

Now to fill a whole table, you might use the UDF like this

=GetMySql($A1,Column())

With your unique Id in A and your table starting in B2, you can copy this
formula as far left and down as you need. I think this may be particularly
slow however.

Post back if that doesn't hit the mark.

 

[Michael J. Malinsky]

Dick,

Thanks for the response, but I'm still a bit confused. I've made the
changes you suggested, but I'm not sure why you are having me define two
variables , strWhere and lngField). I'm assuming strWhere would be the
value I want to use for the value of id, but I don't know where the lngField
fits in. Here is the code I have based on your recommendations. I am
getting an #VALUE error when using the formula:

=GetMySql(B1)

Function GetMySql(strWhere As String, lngField As Long) As Variant

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

myrows = myrs.RecordCount
iRow = 1
iCol = 1
x = 0

With myrs
If .BOF Then GoTo NODATAHERE

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1
End With

'COLLECT DATA
myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value


NODATAHERE:

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

 

[Dick Kusleika]

Michael

The lngField argument is the Field you want to return. A UDF returns one
value to the cell in which it is entered. The problems that you are
experiencing are 1) you aren't passing enough arguments to the UDF and 2)
you're trying to write to cells. With a UDF, you can't change the values of
any cells, rather the value you assign to the function name is put into the
cell into which the function was entered. Think of it in terms of the
built-in worksheet functions in Excel. You can't enter a build-in function
(like SUM) in one cell and have it change the value in another cell - it's
the same with UDF's. So this part

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1
End With

doesn't work with a UDF because you're changing the Excel environment,
namely Cells(iRow, iCol).Value, when a UDF can only return a value.

The first thing you need to do is evaluate whether or not a UDF is really
what you want. If you want something that is going to produce column
headers, then you probably don't want a UDF. But, if you want to change
(from your example) B1 and have it show a different result (presumably from
a different record) then a UDF is probably a good fit.

Based on my limited knowledge of what you're trying to accomplish, I offer
the following suggestions;

Use a Worksheet_Change event so that when you change a particular cell, the
Sub runs and returns different results.
Use two UDFs: one for column headings and one for data.
Use an array-entered UDF (or two) to get all the data in the cells at once.

Tell me why you want to use a UDF and maybe we can find the best way to get
to the same result.

 

[Michael J. Malinsky]

Dick,

I have a VERY basic understanding of UDFs so I wasn't aware of the
restrictions you mentioned. My goal is to allow someone to enter a formula
such as:

=GetMySql(1)

where 1 is the id portion of my query, and in that same cell return the
result, just like the sum function works. I'm not trying to change other
cells and I don't need column headers or anything else, just the result of
the sql query. So if I enter the above formula in A1, A1 will contain, for
example, 20,000. No more, no less. Eventually I'd like to get to a
situation where a user could use a UserForm to do some stuff which would
cause the formula to be put in a certail cell and retreive the result from
the mysql database (I think I can do this part once I get the UDF figured
out).

I understand what the lngField argument is meant to be, but I still don't
understand why I need to provide that argument when I will always be
returning the same field. Could this be hard-coded into the UDF somehow so
I don't have to provide it as an argument?

I don't know if a Worksheet_Change event will work considering that a
particular cell will not be changed (for example, the user would not be
entering a "1" on a worksheet so there would be no value to run through a
sub). I don't need column headings so a second UDF would not be needed. I
also don't need (nor want) a range of data to be returned, so I don't think
an array-entered UDF would be the ticket either.

I hope this helps explain my situation and I again thank you for your
assistance.

 

[Dick Kusleika]

Michael

I have a VERY basic understanding of UDFs so I wasn't aware of the
restrictions you mentioned. My goal is to allow someone to enter a formula
such as:

=GetMySql(1)

where 1 is the id portion of my query, and in that same cell return the
result, just like the sum function works. I'm not trying to change other
cells and I don't need column headers or anything else, just the result of
the sql query. So if I enter the above formula in A1, A1 will contain, for
example, 20,000. No more, no less. Eventually I'd like to get to a
situation where a user could use a UserForm to do some stuff which would
cause the formula to be put in a certail cell and retreive the result from
the mysql database (I think I can do this part once I get the UDF figured
out).

OK, I think I have a better understanding now. I think a UDF is proper for
this application.

I understand what the lngField argument is meant to be, but I still don't
understand why I need to provide that argument when I will always be
returning the same field. Could this be hard-coded into the UDF somehow so
I don't have to provide it as an argument?

You are absolutely correct. You don't need that argument if you are always
returning the same field.

I don't know if a Worksheet_Change event will work considering that a
particular cell will not be changed (for example, the user would not be
entering a "1" on a worksheet so there would be no value to run through a
sub). I don't need column headings so a second UDF would not be needed. I
also don't need (nor want) a range of data to be returned, so I don't think
an array-entered UDF would be the ticket either.

I agree - ignore those suggestions.

I don't have MySQL on this machine, so this is untested pseudocode, but I
envision the UDF looking like this:


Function GetMySql(strWhere As String) As Variant

'strWhere may not be a string. You'll need to experiment with
'data types if string doesn't work

Dim myconn As New ADODB.Connection
Dim myrs As Recordset
Dim mySQL As String

myconn.Open "DSN=MySQL EPACE"
mySQL = "SELECT number FROM master where id = " & strWhere
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

With myrs
.MoveFirst
If Not (.BOF And .EOF) Then
GetMySql = .Fields(1).Value
Else
GetMySql = 0
End If
End With

myrs.Close
myconn.Close

End Function

Give that a try and we'll modify it from there if doesn't work like you
want.

 

[Michael J. Malinsky]

Dick,

I got it to work!!! Thanks for all your help. I had to make a change to
your untested psuedocode by changing this:

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

to this:

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere & " AND value
= 1"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open

I realized I had to expand the mySQL statement to better suit my needs. I
don't know why the other changes were necessary and some part of me doesn't
care, but curiosity has gotten the better of me so if you have any clue, I'd
appreciate the input. I put a break in your code and it seemed to be
bombing on either the myrs.Source or Set myrs lines. It seems to be working
for now, though.

Again, I greatly appreciate all of your help.
Mike.

 

[Dick Kusleika]

Mike

Well I'm glad you got it to work. I'm not an expert on ADO, so I can't say
why those changes were necessary. I actually was just following your lead
assuming you knew what you were doing in that area. When I access a
recordset in ADO, I do this

Set mycn = New ADODB.Connection

mycn.Open stConn

Set myRS = mycn.Execute(mySQL)

where stConn is a DNS string. That gives you one more option to add to any
confusion that you had. I just picked up that Execute from someone else and
never really tried anything else. I presume you get more control over what
type of recordset you have when you use your method, but for my simple
applications, this has always worked for me.

 

[Michael J. Malinsky]

Confusion galore...I have no clue what I'm doing, but I'm learning. I got
my code from one of the newsgroups, maybe the ado newsgroup, and I ran with
it (well maybe not ran).

I have three different projects going in XL right now that require VBA (and
ADO in this case) knowledge. I need to learn to stick with one thing until
I finish it! I started working on one project sporadically last summer,
then got busy, then at the beginning of this summer I decided I didn't like
it so I scrapped it and started over since I learned new stuff in between.

There's a lot to be said for trial and error.

Again, thanks for your help.

Mike.