mysql backend
 

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

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky

mysql backend
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky

mysql backend
 

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


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky

mysql backend
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky

mysql backend
 

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.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky

mysql backend
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

mysql backend
 

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.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

mysql backend
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

mysql backend
 

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.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
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.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.