ADO versus OBDC connection

[Bunky]

Can anyone tell me if an ADO connection string to a Sql-Server is faster than
the OBDC connection? I am a relative new comer to these different methods
and the one I am using, while it works, takes 5 - 6 minutes via the OBDC
method.

Any help would be appreciated.

 

[Rick Brandt]

Can anyone tell me if an ADO connection string to a Sql-Server is
faster than the OBDC connection? I am a relative new comer to these
different methods and the one I am using, while it works, takes 5 - 6
minutes via the OBDC method.

Any help would be appreciated.

Simply "connecting" with either one should be instantaneous. What are you
doing after making the connection?

 

[a a r o n _ k e m p f]

maybe you should try 'using a database server and doing more than one
thing at once'

that strategy works for me quite often.
Access just can't compete with a db server.

 

[Bunky]

I am connecting to an AS-400 via a link (OBDC) and querying for anywhere from
100 to 1000 records out of 265,000. The server is off-site (several thousand
miles) and we have an old T1 line to support us.

 

[a a r o n _ k e m p f]

Access doesn't support moving data over a T1 network.

move to SQL Server and use a real ETL tool.. (take a class on SSIS)
I used to use DataMirror to replicate between SQL Server and AS/400.

I am connecting to an AS-400 via a link (OBDC) and querying for anywhere from
100 to 1000 records out of 265,000.  The server is off-site (several thousand
miles) and we have an old T1 line to support us.  

Simply "connecting" with either one should be instantaneous.  What areyou
doing after making the connection?

 

[Rick Brandt]

I am connecting to an AS-400 via a link (OBDC) and querying for
anywhere from 100 to 1000 records out of 265,000. The server is
off-site (several thousand miles) and we have an old T1 line to
support us.

Don't query the link. Use a passthrough query to ensure that all processing
is done on the AS400 and only the results returned to Access. You might be
getting the lion's share of the processing on the AS400 already, but a
passthrough query guarantees that.

If it is still really slow doing that then perhaps it is just the amount of
data. Is the number of fields high? Normally 1000 records or less should
not be that bad (even over a connection like yours).

 

[a a r o n _ k e m p f]

wow-- that sounds to me like a royal ****ing pain in the ass

I'd just move to SQL Server, setup a linked server.

That way, you only need ONE machine setup to talk to AS400.
Instead of 100 different desktops.

Access MDB Linked _ANYTHING_ is too much of a pain in the butt to be
used in the real world-- for any reason

 

[Rick Brandt]

wow-- that sounds to me like a royal ****ing pain in the ass

Well if you think typing a SELECT statement is a PITA then I guess we'll
have to agree to disagree. If he wants all fields it's merely...

SELECT * FROM Library.TableName

(boy, I have to go lie down now after that)

 

[a a r o n _ k e m p f]

it's not a select statement-- you have to do about 4 steps to take a
linked table and turn it into a SQL PASSTHOUGH.

The # of steps to hook Access (JET) to a sproc.. with 3 parameters
please?

BECAUSE IT TAKES ME NO CODING IN ADP.

-Aaron