Access 2000 Conversion to SQL Server Back End Issue with .Index &.Seek

[James D Smooth]

I am migrating the data and queries from an Access 2000 database over
to SQL Server, and am trying to save time by not rebuilding all of the
business logic that has been built into Macros, Forms, and Reports.
While executing one of the scripts it comes back with the following
error.

Run-time error '3251'
Operation is not supported for this type of object

A snipet of the code is:

MapFromDetail.Index = "MAP_FROM_AK"
MapFromDetail.Seek "=", FromResult

Is there any way that I can add another reference into my VB Library,
or are there other commands that can be used instead?

 

[James D Smooth]

It appears to be directly tied to the dbOpenDynaset Option that is
required for SQL Server Linked tables. I've tried other options
without success.

Set MapFromDetail = AccessDB.OpenRecordset("dbo_MAP_FROM_DETAIL",
dbOpenDynaset, dbSeeChanges)
Set MapToDetail = AccessDB.OpenRecordset("dbo_MAP_TO_DETAIL",
dbOpenDynaset, dbSeeChanges)

 

[Albert D. Kallal]

Actually there's no conceptual equivalent on SQL server at all.

In the olden days when you're talking about punch cards, or an actual
physical file sitting on your hard ride, you could "seek" to a particular
location in that file. The seek requires a physical address type system for
the hard drive.

When you move to client server, you might be connecting to a database server
halfway across the world, and if you have four people entering data into
that database, the position of the data on the hard drive is not a concept
we can deal with anymore (nor is even the position of the data on that disk
drive even a relevant concept anymore).

In the old days when you're using dBase, or file based systems in which your
software had a actual physical access to the actual file on the hard drive,
you could "seek". Now, the whole computing industry has thrown out that
concept (this is also why access does not have the concept of a record
number, or old style PC database systems such as debased did in fact have
the concept of a record number).

You'll simply have to replace your code.

MapFromDetail.Index = "MAP_FROM_AK"
MapFromDetail.Seek "=", FromResult

I would suggest to use something like:

strSql = "select * from theTable where MAP_FROM_AK = " & FromResult
set rst = currentdb.OpenrecordSet(strSql)

somting = rst!NameOfField
somting = rst!nameOfField

You can't "seek" to a location in the file, but you can "send" a request to
the data engine to retrieve your particular data you want. Following the
above type style of coding, you'll likely only have to replace the seek
command, and the rest of the recordset code that retrieves the values should
work as before unchanged.