Problem with .index

[diamondsc]

I have a MS Access 2000 front end app connecting to a SQL Server database back end. The problem I'm having is selecting an index on a table in the back end. Here is the code:


Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bill As DAO.Recordset
Dim cn As DAO.Connection

Set ws = CreateWorkspace("", "admin", "", dbUseODBC)
Set cn = ws.OpenConnection("", , , "ODBC;DSN=ESource&quot
Set db = cn.Database
Set bill = db.OpenRecordset("Billing", dbOpenDynamic)

bill.Index = "PK_Billing"
bill.MoveFirst
MsgBox "record " & bill![Record Number]

db.Close

I get runtime error 3251 "Operation is not supported for this type of object" on the bill.Index = "PK_Billing" code.

This is the first time I've tried the Access front end with SQL back end. The documentation I have found so far indicates this should work. Can I not select an index in this way on a SQL Server table? If not, how is indexing handled?

Thanks.

 

[zemp]

DAO is designed to work with the Jet engine that is used by Access. I do not think that it will work with the SQL server engine. In order to use the same configuration you will probably have to recode your app to use ADO data access objects instead.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[ttrotter]

I believe that zemp is correct. DAO is designed to work with the Jet engine (Access). I would suggest opening an additional recordset using ADO. Example,

Set rs = New ADODB.Connection
rs.ConnectionString = "driver=" & DATA_DRIVER & ";server=" & DATA_SERVER & ";database=" & mydatabaseName & ";trusted_connection=No;uid=" & username & ";pwd=" & password & ";Network Library=DBMSSOCN; "
rs.ConnectionTimeout = DATA_TIMEOUT
rs.Open

I would suggest looking up the syntax of Connectionstring. Your particular implementation will probably not look like this.

 

[Golom]

As the other responders have stated "... only with JET ...", however the specification of ".index" is relevent only if you are using the ".seek" method. If you just want the returned records sorted by the field (or fields) involved in the "PK_Billing" index, you can

   Set bill = db.OpenRecordset("Select * From Billing Order By PK_Billing;", dbOpenDynamic)

Assuming that "PK_Billing" is the name of a field that is indexed by the "PK_Billing" index.

If you do go the ADO route, you will need to check the

   rs.Supports(adIndex)

property of the ADODB Recordset. Not all data providers support the .Index property.