MS Access 2010 - SQL Server Error Code 3251 - Operation is not supported. .

[Petran]

I have an MS Access 2010 database, split between a Back End Database, holding the Tables and a Front End Database holding the Screens, Queries and Code Modules

I have recently acquired and installed SQL Express on my computer.
I have successfully converted the MS Access BE Database (Tables) to SQL and linked these tables to my MS Access FE database using an ODBC Connection.

The tables is visible in the MS Access FE and I can open the tables and see the records.

In my (existing) code I use:

tblPropertyMaster.Index = "PrimaryIndex"
tblPropertyMaster.Seek "=", fldTownship_Abbr, fldErfNo, fldPortion

where tblPropertyMaster is a (converted) table in the SQL Express Database and the fields, fldTownship_abbr, fldErfNo and fldPortion, are the Primary Key on the table.

The code fails on the tblPropertyMaster.Index ="PrimaryIndex" with the error:

Run Time Error 3251
Operation is not supported for this type of operation

I have search the MS Access Blogs, been in touch with Microsoft (no feedback yet) but have not had any real feedback.

I am sure that there is an easy fix for this as there must be someone out there using the same logic/code. . . .

 

[pbaldy]

Presuming you are opening a recordset, I'd open it on an SQL statement that restricted it to the desired records, not the entire table. It will be more efficient anyway.