473,703 Members | 4,024 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL ODBC 3.51 driver Very Slow!

DJJ
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.

I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very good.

Is there anyway to tweak the DSN so it pulls the data faster?

Thanks...

David J
Jul 23 '05 #1
11 17563
DJJ wrote:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.


I never had problems with the speed of access and MySQL, did you already
check the option 'return only matching rows' This is needed for Access
(see http://dev.mysql.com/doc/mysql/en/msaccess-setup.html)

You can also try and log what is happening, maybe the log will give you
some information (I 've never used it thought). It is possible to trace
driver calls and it is possible to log queries.

http://dev.mysql.com/doc/mysql/en/myodbc-trace.html

Also have al ook here:
http://dev.mysql.com/doc/mysql/en/optimize-access.html

It contains tips to optimize Access for the use of mySQL .

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?
Jul 23 '05 #2
"DJJ" <ge****@writme. com> wrote in message
news:RD******** *********@newss vr19.news.prodi gy.com...
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless.

I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very good.

Is there anyway to tweak the DSN so it pulls the data faster?
That driver is not slow!
I am finding that the data from
the MySQL tables takes a hell of a long time to load ...


You mention that your (3) tables are relatively small. If you are linking
to small MySQL tables, they will typically load very quickly. You need to
give us some more details about your setup. Something is not quite right
here!

Large (linked) table may indeed be slow. Access itself is probably
requireing an immense amount of data to xfer into a recordset upfront. I
preferentially use passthrough queries, bypassing the JET software. "SELECT
* FROM SomeTable;" sent directly to MySQL will tend to load an ADO recordset
quite a bit faster than a linked table. Particularly if you can use a
ForwardOnly recordset. Of course this is a one way load. You will need a
separate passthrough query to write back to the table.

Pass through queries are a very efficient way to bind a MySQL server to the
MS Office system. In any case, it is doubtful that the ODBC 3.51 driver is
the problem. Perhaps you could provide a bit more detail?

Thomas Bartkus

Jul 23 '05 #3
DJJ
Thanks very much for the info.

I did setup the following parameters but with little or no difference.

Don't Optimize Column Width

Return Matching Rows

Allow Big Results

Use Compressed Protocol

It takes 16 minutes to load and scroll to the last record of a table in
datasheet mode with 1319 records, 40 fields including a primary key.

My network speed is 100 mpbs

I also tried connecting to a copy of the same MySQL table I have locally on
my machine on a separately configured DNS and it was still not much better.
So it is definitely not the network

DJJ

"Jonathan" <jo******@heela l.nl> wrote in message
news:d7******** **@reader10.wxs .nl...
DJJ wrote:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time to load making any kind linkage with my Access data virtually useless.


I never had problems with the speed of access and MySQL, did you already
check the option 'return only matching rows' This is needed for Access
(see http://dev.mysql.com/doc/mysql/en/msaccess-setup.html)

You can also try and log what is happening, maybe the log will give you
some information (I 've never used it thought). It is possible to trace
driver calls and it is possible to log queries.

http://dev.mysql.com/doc/mysql/en/myodbc-trace.html

Also have al ook here:
http://dev.mysql.com/doc/mysql/en/optimize-access.html

It contains tips to optimize Access for the use of mySQL .

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

Jul 23 '05 #4
DJJ
What kind of info do you need?

I've tried placing an initial select statement under the odbc options to
reduce the number of fields and records but it doesn't work. This odbc is
unbelievably slow.

"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5J******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:RD******** *********@newss vr19.news.prodi gy.com...
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind

linkage
with my Access data virtually useless.

I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The network
connection is very good.

Is there anyway to tweak the DSN so it pulls the data faster?


That driver is not slow!
I am finding that the data from
the MySQL tables takes a hell of a long time to load ...


You mention that your (3) tables are relatively small. If you are linking
to small MySQL tables, they will typically load very quickly. You need to
give us some more details about your setup. Something is not quite right
here!

Large (linked) table may indeed be slow. Access itself is probably
requireing an immense amount of data to xfer into a recordset upfront. I
preferentially use passthrough queries, bypassing the JET software.

"SELECT * FROM SomeTable;" sent directly to MySQL will tend to load an ADO recordset quite a bit faster than a linked table. Particularly if you can use a
ForwardOnly recordset. Of course this is a one way load. You will need a
separate passthrough query to write back to the table.

Pass through queries are a very efficient way to bind a MySQL server to the MS Office system. In any case, it is doubtful that the ODBC 3.51 driver is the problem. Perhaps you could provide a bit more detail?

Thomas Bartkus

Jul 23 '05 #5
"DJJ" <ge****@writme. com> wrote in message
news:dy******** **********@news svr19.news.prod igy.com...
What kind of info do you need?

How big is the table?
"relatively small" doesn't tell us too much. Linked tables that contain
more than >100000 records are going to be slow. I generally work around
this by using pass through queries instead of linked tables.

Does the linked table have a primary key that DAO/ADO recognizes?
BigInt type fields won't cut it here because it is alien to these Microsoft
libraries.

Have you tried the [Test Data Source] button on the ODBC dialog box?
If it takes more than a split second to snap back with a "Success" dialog
box, then you have probably have some kind of interface parameter problem.

Where is MySQL running? On a seperate server (Linux? Windows?) or is it
running locally on the same Windows machine you are running Access?
I've tried placing an initial select statement under the odbc options to
reduce the number of fields and records but it doesn't work.
You might be better off looking inside Access for the [Advanced] tab of
Tools_Options. where you will find several ODBC settings to tweak. I
confess I haven't played much with these, opting instead to work with pass
through queries. Note that you are working with an *extra* software layer
with Microsoft JET. Linked tables are going to be slower than working
directly with MySQL for reasons that have nothing to do with ODBC. But the
good news is that you can work around this by using pass through queries.
Filling a DAO or ADO recordset with data coming from a MySQL server should
be quite fast. The speed limitation should be the pipes between your server
and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
driver shouldn't even enter the picture.
This odbc is unbelievably slow. Again I would suggest that blaming odbc will yield little in terms of
speeding up your queries. It is quite doubtful that the V3.51 MySQL driver
is the bottleneck. I have too many examples of joining tables from a
separate MySQL server with local Access tables with excellent results. They
are *fast*!

Thomas Bartkus
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5J******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:RD******** *********@newss vr19.news.prodi gy.com...
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data
from
the MySQL tables takes a hell of a long time to load making any kind

linkage
with my Access data virtually useless.

I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting out on a server and the Access database is running locally. The
network connection is very good.

Is there anyway to tweak the DSN so it pulls the data faster?


That driver is not slow!
I am finding that the data from
the MySQL tables takes a hell of a long time to load ...


You mention that your (3) tables are relatively small. If you are

linking to small MySQL tables, they will typically load very quickly. You need to give us some more details about your setup. Something is not quite right
here!

Large (linked) table may indeed be slow. Access itself is probably
requireing an immense amount of data to xfer into a recordset upfront. I preferentially use passthrough queries, bypassing the JET software.

"SELECT
* FROM SomeTable;" sent directly to MySQL will tend to load an ADO

recordset
quite a bit faster than a linked table. Particularly if you can use a
ForwardOnly recordset. Of course this is a one way load. You will need a separate passthrough query to write back to the table.

Pass through queries are a very efficient way to bind a MySQL server to

the
MS Office system. In any case, it is doubtful that the ODBC 3.51 driver

is
the problem. Perhaps you could provide a bit more detail?

Thomas Bartkus


Jul 23 '05 #6
DJJ
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:

Option Compare Database
Dim Conn As ADODB.Connectio n
Dim rsInventory As ADODB.Recordset
Dim strConn As String
Dim strSQLInventory As String

Private Sub Form_Open(Cance l As Integer)

strConn = "Provider=MSDAS QL.1;Persist Security Info=False;Data
Source=Business Mind"

Set Conn = New ADODB.Connectio n
Conn.Open strConn

Set rsInventory = New ADODB.Recordset
rsInventory.Ope n "SELECT stock_id FROM inventory", Conn, adOpenStatic,
adLockOptimisti c

Me!txtStockNum. ControlSource = "stock_id"

End Sub

Private Sub Form_Close()

rsInventory.Clo se
Conn.Close
Set rsInventory = Nothing
Set Conn = Nothing

End Sub
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5u******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:dy******** **********@news svr19.news.prod igy.com...
What kind of info do you need?

How big is the table?
"relatively small" doesn't tell us too much. Linked tables that

contain more than >100000 records are going to be slow. I generally work around
this by using pass through queries instead of linked tables.

Does the linked table have a primary key that DAO/ADO recognizes?
BigInt type fields won't cut it here because it is alien to these Microsoft libraries.

Have you tried the [Test Data Source] button on the ODBC dialog box?
If it takes more than a split second to snap back with a "Success" dialog
box, then you have probably have some kind of interface parameter problem.

Where is MySQL running? On a seperate server (Linux? Windows?) or is it
running locally on the same Windows machine you are running Access?
I've tried placing an initial select statement under the odbc options to
reduce the number of fields and records but it doesn't work.
You might be better off looking inside Access for the [Advanced] tab of
Tools_Options. where you will find several ODBC settings to tweak. I
confess I haven't played much with these, opting instead to work with pass
through queries. Note that you are working with an *extra* software layer
with Microsoft JET. Linked tables are going to be slower than working
directly with MySQL for reasons that have nothing to do with ODBC. But

the good news is that you can work around this by using pass through queries.
Filling a DAO or ADO recordset with data coming from a MySQL server should
be quite fast. The speed limitation should be the pipes between your server and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
driver shouldn't even enter the picture.
This odbc is unbelievably slow. Again I would suggest that blaming odbc will yield little in terms of
speeding up your queries. It is quite doubtful that the V3.51 MySQL

driver is the bottleneck. I have too many examples of joining tables from a
separate MySQL server with local Access tables with excellent results. They are *fast*!

Thomas Bartkus
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5J******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:RD******** *********@newss vr19.news.prodi gy.com...
> I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
> tables to a Microsoft Access 2003 database. I am finding that the data from
> the MySQL tables takes a hell of a long time to load making any kind
linkage
> with my Access data virtually useless.
>
> I have the MySQL driver setup in as a USER DSN. The MySQL data is

sitting
> out on a server and the Access database is running locally. The network > connection is very good.
>
> Is there anyway to tweak the DSN so it pulls the data faster?

That driver is not slow!

> I am finding that the data from
> the MySQL tables takes a hell of a long time to load ...

You mention that your (3) tables are relatively small. If you are linking to small MySQL tables, they will typically load very quickly. You need to
give us some more details about your setup. Something is not quite
right here!

Large (linked) table may indeed be slow. Access itself is probably
requireing an immense amount of data to xfer into a recordset upfront. I preferentially use passthrough queries, bypassing the JET software. "SELECT
* FROM SomeTable;" sent directly to MySQL will tend to load an ADO

recordset
quite a bit faster than a linked table. Particularly if you can use a
ForwardOnly recordset. Of course this is a one way load. You will
need a separate passthrough query to write back to the table.

Pass through queries are a very efficient way to bind a MySQL server

to the
MS Office system. In any case, it is doubtful that the ODBC 3.51
driver is
the problem. Perhaps you could provide a bit more detail?

Thomas Bartkus



Jul 23 '05 #7
DJJ
Added to my code a debug print

Debug.Print rsInventory!sto ck_id

And I could see the first record in the table. So the connection seems to
be working but when the control source is set to stock_id no records show.
Would you know why that is?

Thanks for all your help so far...

DJJ
"DJJ" <ge****@writme. com> wrote in message
news:6S******** *********@newss vr31.news.prodi gy.com...
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:

Option Compare Database
Dim Conn As ADODB.Connectio n
Dim rsInventory As ADODB.Recordset
Dim strConn As String
Dim strSQLInventory As String

Private Sub Form_Open(Cance l As Integer)

strConn = "Provider=MSDAS QL.1;Persist Security Info=False;Data
Source=Business Mind"

Set Conn = New ADODB.Connectio n
Conn.Open strConn

Set rsInventory = New ADODB.Recordset
rsInventory.Ope n "SELECT stock_id FROM inventory", Conn, adOpenStatic,
adLockOptimisti c

Me!txtStockNum. ControlSource = "stock_id"

End Sub

Private Sub Form_Close()

rsInventory.Clo se
Conn.Close
Set rsInventory = Nothing
Set Conn = Nothing

End Sub
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5u******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:dy******** **********@news svr19.news.prod igy.com...
What kind of info do you need?


How big is the table?
"relatively small" doesn't tell us too much. Linked tables that

contain
more than >100000 records are going to be slow. I generally work around
this by using pass through queries instead of linked tables.

Does the linked table have a primary key that DAO/ADO recognizes?
BigInt type fields won't cut it here because it is alien to these

Microsoft
libraries.

Have you tried the [Test Data Source] button on the ODBC dialog box?
If it takes more than a split second to snap back with a "Success" dialog
box, then you have probably have some kind of interface parameter problem.
Where is MySQL running? On a seperate server (Linux? Windows?) or is it
running locally on the same Windows machine you are running Access?
I've tried placing an initial select statement under the odbc options to reduce the number of fields and records but it doesn't work.


You might be better off looking inside Access for the [Advanced] tab of
Tools_Options. where you will find several ODBC settings to tweak. I
confess I haven't played much with these, opting instead to work with pass through queries. Note that you are working with an *extra* software layer with Microsoft JET. Linked tables are going to be slower than working
directly with MySQL for reasons that have nothing to do with ODBC. But

the
good news is that you can work around this by using pass through queries. Filling a DAO or ADO recordset with data coming from a MySQL server should be quite fast. The speed limitation should be the pipes between your

server
and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
driver shouldn't even enter the picture.
This odbc is unbelievably slow.

Again I would suggest that blaming odbc will yield little in terms of
speeding up your queries. It is quite doubtful that the V3.51 MySQL

driver
is the bottleneck. I have too many examples of joining tables from a
separate MySQL server with local Access tables with excellent results.

They
are *fast*!

Thomas Bartkus
"Thomas Bartkus" <to*@dtsam.co m> wrote in message
news:5J******** ************@te lcove.net...
> "DJJ" <ge****@writme. com> wrote in message
> news:RD******** *********@newss vr19.news.prodi gy.com...
> > I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
> > tables to a Microsoft Access 2003 database. I am finding that the

data
> from
> > the MySQL tables takes a hell of a long time to load making any kind > linkage
> > with my Access data virtually useless.
> >
> > I have the MySQL driver setup in as a USER DSN. The MySQL data is
sitting
> > out on a server and the Access database is running locally. The

network
> > connection is very good.
> >
> > Is there anyway to tweak the DSN so it pulls the data faster?
>
> That driver is not slow!
>
> > I am finding that the data from
> > the MySQL tables takes a hell of a long time to load ...
>
> You mention that your (3) tables are relatively small. If you are

linking
> to small MySQL tables, they will typically load very quickly. You need
to
> give us some more details about your setup. Something is not quite

right > here!
>
> Large (linked) table may indeed be slow. Access itself is probably
> requireing an immense amount of data to xfer into a recordset upfront. I
> preferentially use passthrough queries, bypassing the JET software.
"SELECT
> * FROM SomeTable;" sent directly to MySQL will tend to load an ADO
recordset
> quite a bit faster than a linked table. Particularly if you can use
a > ForwardOnly recordset. Of course this is a one way load. You will

need
a
> separate passthrough query to write back to the table.
>
> Pass through queries are a very efficient way to bind a MySQL server

to the
> MS Office system. In any case, it is doubtful that the ODBC 3.51 driver is
> the problem. Perhaps you could provide a bit more detail?
>
> Thomas Bartkus
>
>
>



Jul 23 '05 #8
"DJJ" <ge****@writme. com> wrote in message
news:6S******** *********@newss vr31.news.prodi gy.com...
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.Connectio n
Dim rsInventory As New ADODB.Recordset
Dim sql As String, strCon As String
Dim cnt As Long

strCon = "DRIVER=MyS QL ODBC 3.51 Driver;" & _
"SERVER=You r Servers IP;" & _
"DATABASE=Y our Databases name;" & _
"UID=Your Id;" & _
"PWD=Your Password" & _
"OPTION=184 35;" ' <- 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.Sou rce = sql
rsInventory.Act iveConnection = Conn

' Fastest cursor - so long as we don't need to move back & forth
(forward only!)
rsInventory.Cur sorType = adOpenForwardOn ly
rsInventory.Ope n

' 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.Mov eNext
Loop
MsgBox "We captured " & cnt & " Records", vbExclamation

' Clean it up, shut it down.
rsInventory.Clo se
Set rsInventory = Nothing

Conn.Close
Set Conn = Nothing
End Sub

=============== =============== =============
Jul 23 '05 #9
DJJ
I setup code similar to yours that still takes a while to load but it's much
faster. Once the data is loaded in my form I can scroll 1300 records very
quickly, which is great. However, when I try to edit a record I get the
message "This recordset is not updateable". Would you know how to get
around that? Here is the entire code set I'm using:

Private Sub Form_Open(Cance l As Integer)
Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connectio n

With cn
.Provider = "MSDASQL.1"
.Properties("Pe rsist Security Info").Value = "False"
.Properties("Da ta Source").Value = "MyDatabase "
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnecti on = cn
.Source = "SELECT i.stock_id as Stock_ID, i.vendor_style_ number
as StyleNum," & _
"c.name as Name, l.qty as Qty, i.description as 'Desc'," &
_
"i.cost_per_pri ce_unit as Cost, i.price_per_uni t as
Retail," & _
"i.price_2 as Wholesale FROM inventory i INNER JOIN
inventory_locat ion l " & _
"ON i.ideaxid = l.inventory INNER JOIN contacts c ON
i.vendor = c.ideaxid " & _
"WHERE c.name = 'MC2'"
.LockType = adLockOptimisti c
.CursorType = adOpenForwardOn ly
.CursorLocation = adUseClient
.Open

End With

Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

End Sub
Private Sub Form_Unload(Can cel As Integer)
Dim cn As ADODB.Connectio n
Set cn = Me.Recordset.Ac tiveConnection
cn.Close
Set cn = Nothing
End Sub
"Thomas Bartkus" <th***********@ comcast.net> wrote in message
news:Er******** ************@te lcove.net...
"DJJ" <ge****@writme. com> wrote in message
news:6S******** *********@newss vr31.news.prodi gy.com...
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.Connectio n
Dim rsInventory As New ADODB.Recordset
Dim sql As String, strCon As String
Dim cnt As Long

strCon = "DRIVER=MyS QL ODBC 3.51 Driver;" & _
"SERVER=You r Servers IP;" & _
"DATABASE=Y our Databases name;" & _
"UID=Your Id;" & _
"PWD=Your Password" & _
"OPTION=184 35;" ' <- 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.Sou rce = sql
rsInventory.Act iveConnection = Conn

' Fastest cursor - so long as we don't need to move back & forth
(forward only!)
rsInventory.Cur sorType = adOpenForwardOn ly
rsInventory.Ope n

' 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.Mov eNext
Loop
MsgBox "We captured " & cnt & " Records", vbExclamation

' Clean it up, shut it down.
rsInventory.Clo se
Set rsInventory = Nothing

Conn.Close
Set Conn = Nothing
End Sub

=============== =============== =============

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2226
by: Chetan | last post by:
I have setup an ODBC driver for my MySQL database and use a 3rd party program to connect to it. Everytime the software connects, the ODBC driver setup screen pops up. I have to click OK on it to continue. I set the parameter "Dont prompt on connect" in the options but this has not solved the issue. The system setup is Win2K SP2, MySQL 4.0.15, MySQL ODBC driver 3.51. Any help will be appreciated.
0
8319
by: Edmond Doudard | last post by:
Hi, I have just installed the MySQL ODBC driver 3.51 on an XP machine which is behind a proxy. When I configure my DSN, I can't access the MySQL server which is in the "outside" world. I get this error: Unknown MySQL Server Host 'mysqlserver.com'
2
2854
by: Claude Schneegans | last post by:
Hi, The MySQL ODBC driver returns an error when calling SQLColumns function with an empty Table parameter: Can't use wildcards in table name Is there a newer version which supports wildcards in table name? Actually I don't really need wildcards, but I need to get the list of all columns in all tables. Thanks.
0
1647
by: ElmoWatson | last post by:
I'm trying to use the Windows DataLink (udl) to set up a connection to a MySQL database. I've installed the 3.51 ODBC driver from the MySQL website - however, using a DataLink, it doesn't show up as a DataSource - How can I get it recognized there?
1
2142
by: rguti | last post by:
Anybody know when the new version of the ODBC Driver is gonna be out? I'm using 3.51.11-1, but I'm having problems transferring information from SQL Server. Thanks.
16
27918
by: MLH | last post by:
Using MS Access, I have attached to MySQL servers in other states and other countries on the other side of my router. But when I use the MySQL ODBC driver 3.51 to connect to a MySQL server on my own LAN, the driver tells me it cannot make the connection. Here are the ODBC driver connection parms: Data Source Name: (free field - name my "my linux box" will do nicely) Host/Server Name (or IP) - something like MSQLUserName@ServerName.net...
3
2392
by: Bob | last post by:
All, I have read through lots of postings regarding my concerns, but I haven't found what I am looking for. The center (non profit University) where I work collects scientific data about subjects enrolled in studies related to respiratory diseases. Both lab data and questionnaire information are collected. We use W2K as PC operating systems, MS Access XP, XP Developer, and Visual SourceSafe. Our databases use a front end – back end...
0
1134
by: luttkens | last post by:
Hi I've made an application in .Net which connects to a MySQL database via remote access. It all works fine - if the user has installed the MySql ODBC Driver on it's machine. My question is how I can include the MySQL ODBC Driver in the MSI-file (created in Visual Studio)? I want the driver to be installed automatically if the users doesn't have it. How do I do it?
1
4755
by: blekok | last post by:
I'm trying to use MS Access for the front end and MySQL as the Data source. I have this MS Access form which uses a table called hotel: http://i489.photobucket.com/albums/rr259/the_luser/db/access.png With the "location" combo box linked to a table called "location". The location combobox properties that i entered were: Row Source:
0
8674
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9262
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9018
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8970
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7876
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6595
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5923
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4690
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.