"DJJ" <ge****@writme.com> wrote in message
news:6S*****************@newssvr31.news.prodigy.co m...
The table is small. The database is on a Windows 2K Server.
I tried an ADO recordset to populate a single text box on a form as per
your suggestion. It seems to run faster but the text box shows no data
(#Name?)
The SQL query was tested on using MySQL query browser. The DSN connection
tests ok. Here's the code I used:
<snip>
Okay. I am going to put my own minimalist code here that I just tested on a
small table. Part of the problem is that I can't see into the ODBC
configuration [MSDASQL] you set up. The following code addresses the MySQL
driver directly so we can see up front all the configuration settings. My
guess is that you have a few bad settings in tje [MSDASQL] definition.
I tested this in a 4500 record table. It only takes a *tiny* fraction of a
second to retrieve, loop through, and close the recordset. By far the
biggest overhead is the xfer of data over the network and the loop. The
speed of that, of course, depends on your workstation. The point is - the
MySQL/ODBC driver is *not* a speed bottleneck!
I used ADO 2.8 for this test.
FWIW - DAO is substantially faster than ADO!
I won't get into pass through queries here - but that would speed things up
too!
Try this on your database and see that you can read the data !
And if it's slow - then I have nothing left to say :-)
Thomas Bartkus
===========================================
Public Sub AdoTest()
Dim Conn As New ADODB.Connection
Dim rsInventory As New ADODB.Recordset
Dim sql As String, strCon As String
Dim cnt As Long
strCon = "DRIVER=MySQL ODBC 3.51 Driver;" & _
"SERVER=Your Servers IP;" & _
"DATABASE=Your Databases name;" & _
"UID=Your Id;" & _
"PWD=Your Password" & _
"OPTION=18435;" ' <- BigInt->Int, Compressed Protocol, Don't
optimize col width,
' return matching rows
Conn.Mode = adModeRead ' If you only want to read, then set it so!
It's faster R/O
Conn.Open strCon
sql = "SELECT stock_id FROM inventory;"
rsInventory.Source = sql
rsInventory.ActiveConnection = Conn
' Fastest cursor - so long as we don't need to move back & forth
(forward only!)
rsInventory.CursorType = adOpenForwardOnly
rsInventory.Open
' Prove that we got something.
Do While (Not rsInventory.EOF)
' You should do something useful with the data here!
' I'll just count.
cnt = cnt + 1
rsInventory.MoveNext
Loop
MsgBox "We captured " & cnt & " Records", vbExclamation
' Clean it up, shut it down.
rsInventory.Close
Set rsInventory = Nothing
Conn.Close
Set Conn = Nothing
End Sub
===========================================
