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.