connection via odbc takes very long time

Rafi Kfir

HI,

I use vb6 to access database. I can select between "local" data base
and "remote" data base. Both connection calls the same Access 2000
Mdb.
The Mdb contains local tables (on the hard disk) and remote links to
Oracle tables via odbc link.

The local connection (to the recordset) is fast, but the "remote"
connection takes around 7 minuits.

NOTE: Connecting to the "remote" tables, manually via Access2000, is
fast!!

I'm desperate to find a solution to that problem. Any idia will be
appriciated.

Rafi

The program:

'-------Connection---------
Set dbsConn = New ADODB.Connection
dbsConn.CursorLocation = adUseClient
dbsConn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;PersisSecuri ty
Info=False;Data Source= " & MdbPath
'MdbPath=location of Access 2000 DB
dbsConn.Open

'---------Record Set -----------------
Set dbsRSCard = New ADODB.Recordset
DoEvents
Set dbsRSTest = New ADODB.Recordset
DoEvents
dbsRSCard.Open tblCard, dbsConn, adOpenDynamic, adLockOptimistic
'rafi
DoEvents
dbsRSTest.Open tblTest, dbsConn, adOpenDynamic, adLockOptimistic
'rafi
'Local - tblCard, tblTest are local Access tables
'Remote - tblCard, tblTest are remote links to Oracle tables via
odbc

Nov 12 '05 #1

Rick Brandt

"Rafi Kfir" <ra*******@telrad.co.il> wrote in message
news:f7**************************@posting.google.c om...

HI,

I use vb6 to access database. I can select between "local" data base
and "remote" data base. Both connection calls the same Access 2000
Mdb.
The Mdb contains local tables (on the hard disk) and remote links to
Oracle tables via odbc link.

The local connection (to the recordset) is fast, but the "remote"
connection takes around 7 minuits.

NOTE: Connecting to the "remote" tables, manually via Access2000, is
fast!!

I'm desperate to find a solution to that problem. Any idia will be
appriciated.

What I suspect is that when you see fast connections to the remote tables in
Access you are opening a datasheet and seeing "some data" quickly. Access will
display the datasheet after a few pages of data are pulled across. It might
very well take several minutes to pull *all* of the data in Access and your VB6
app might being doing just that. What exactly is your process when you say
you're "connecting"?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #2

Rafi Kfir

Hi Rick

Thank you for taking the time to deal with my problem.

I measure the time it take Access to read the tables: The first one
takes 10 sec and the second one 50 sec. Via VB it takes around 7
minuits....

When I say connecting I mean that I double click the link to the
remote table which connect me to the remote table.

Any more good ideas?

Thanks,
Rafi

Nov 12 '05 #3

Rick Brandt

"Rafi Kfir" <ra*******@telrad.co.il> wrote in message
news:f7**************************@posting.google.c om...

Hi Rick

Thank you for taking the time to deal with my problem.

I measure the time it take Access to read the tables: The first one
takes 10 sec and the second one 50 sec.

I'm still not sure what that means. What process are you running on the tables
in Access? Are you runnning a process that requires the entire table be read
such as opening a RecordSet and then doing a MoveLast?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #4

Rafi Kfir

Hi Rick and Thanks again,

The processes that takes the time are line 1 and 2 below.

The time also depends on the PC. I have the same program run on two
different PCs and one does it in 4 minuites while the other in 7.5
minuits. The same PC will reach the data from Access2000 much much
faster.

I do use 'MoveLast' but at that time it doesn't take any time at
all...

The number of records is 20,000 in one table and 140,000 in the other.

Any more ideas?

Thanks
Rafi
The program:

'-------Connection---------
Set dbsConn = New ADODB.Connection
dbsConn.CursorLocation = adUseClient
dbsConn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;PersisSecuri ty
Info=False;Data Source= " & MdbPath
'MdbPath=location of Access 2000 DB
dbsConn.Open

'---------Record Set -----------------
Set dbsRSCard = New ADODB.Recordset
DoEvents
Set dbsRSTest = New ADODB.Recordset
DoEvents
1) dbsRSCard.Open tblCard, dbsConn, adOpenDynamic, adLockOptimistic
DoEvents
2) dbsRSTest.Open tblTest, dbsConn, adOpenDynamic, adLockOptimistic
'Local - tblCard, tblTest are local Access tables
'Remote - tblCard, tblTest are remote links to Oracle tables via
odbc

Nov 12 '05 #5

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would probably be a better idea to create a connection to the
Oracle dbs directly instead of going thru the Jet engine. You can use
the OLEDB provider for Oracle (MSDAORA) or a DSN-less connection
"Driver=Microsoft ODBC for Oracle";Server= <etc.>."

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7ldgIechKqOuFEgEQKtzACgmZq7rWyRUx9nBPMVjPlP2y Mtux0AoKXY
LF32ctu35RirEYVCEwCyG15v
=Pdf5
-----END PGP SIGNATURE-----
Rafi Kfir wrote:

Hi Rick and Thanks again,

The processes that takes the time are line 1 and 2 below.

The time also depends on the PC. I have the same program run on two
different PCs and one does it in 4 minuites while the other in 7.5
minuits. The same PC will reach the data from Access2000 much much
faster.

I do use 'MoveLast' but at that time it doesn't take any time at
all...

The number of records is 20,000 in one table and 140,000 in the other.

Any more ideas?

Thanks
Rafi
The program:

'-------Connection---------
Set dbsConn = New ADODB.Connection
dbsConn.CursorLocation = adUseClient
dbsConn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;PersisSecuri ty
Info=False;Data Source= " & MdbPath
'MdbPath=location of Access 2000 DB
dbsConn.Open

'---------Record Set -----------------
Set dbsRSCard = New ADODB.Recordset
DoEvents
Set dbsRSTest = New ADODB.Recordset
DoEvents
1) dbsRSCard.Open tblCard, dbsConn, adOpenDynamic, adLockOptimistic
DoEvents
2) dbsRSTest.Open tblTest, dbsConn, adOpenDynamic, adLockOptimistic
'Local - tblCard, tblTest are local Access tables
'Remote - tblCard, tblTest are remote links to Oracle tables via
odbc

Nov 12 '05 #6

Rafi Kfir

Thank You,

This was the conclusion I came by this morning after discussing this
issue with a workmate. We made some test and indeed the direct
connection is immidiate.

While working with the Jet I needed to Open a Record Set which took a
long period of time, and then do my job of adding more records. With
a connection directly to the Oracle DB I use a SQL command to
immidiately add the new record.

Thanks for your advice
Rafi

It would probably be a better idea to create a connection to the
Oracle dbs directly instead of going thru the Jet engine. You can use
the OLEDB provider for Oracle (MSDAORA) or a DSN-less connection
"Driver=Microsoft ODBC for Oracle";Server= <etc.>."

Nov 12 '05 #7