Seek together with Access.adp and SQL2000 and ADO

[Dieter]

Hello:

I am using an Access XP Project (.adp) with an SQL2K back end.
I would like to use VBA to directly access (with the seek-method) a record
in a table on the SQL-server by its PrimaryKey-Index e.g. productnumber
using ActiveX Data Objects ADO.

It works with tables within the same access.mdb, it also works with linked
tables from another access.mdb but it cannot bring it to work using ADO and
the seek-method (using an index e.g. primary key) together with a SQL2K at
the other side .


Is this possible?

Any suggestions would be appreciated

Dieter

 

[Lyle Fairfield]

Hello:

I am using an Access XP Project (.adp) with an SQL2K back end.
I would like to use VBA to directly access (with the seek-method) a
record in a table on the SQL-server by its PrimaryKey-Index e.g.
productnumber using ActiveX Data Objects ADO.

It works with tables within the same access.mdb, it also works with
linked tables from another access.mdb but it cannot bring it to work
using ADO and the seek-method (using an index e.g. primary key) together
with a SQL2K at the other side .


Is this possible?

Any suggestions would be appreciated

Dieter

My recollection is that the OLE DB provider for MS-JET supports Seek,
while the OLE DB provider for MS-SQL does not support Seek.

You can create temp indexes for the use with the Find Method as in:

RecordSet.Fields("YourPrimaryKeyField").Properties("Optimize")=True