Access 2002: Setting Indexes doesn't work - General VBA Questions

[Carl]

Some background - I had begun developing an applicaion
with Access 2000 and had a few simple modules
functioning. Then I upgraded to Office XP Pro and, with
Access 2002 found what had once been working wasn't
anymore. My dilema is opening databases and associated
indexes.

Based on a book I bought ("Access 2002 VBA Handbook")
I've entered the following code to open a TransactionFile
table:

Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "tblTransactionFile", cnn, adOpenDynamic
rst.Index = "Master"

When I get to the rst.Index line I get the error "Current
Provider does not support necessary interface for index
functionality".

I know there's probably all kinds of problems I'm bumping
up against - not the least of which is the changes
between Access 2000 and Access 2002.

Thanks
Carl

 

[CSmith]

Hi,

Try using adOpenKeyset for your cursor, as I did in "Tip# 33" on my site.

Good luck!

 

[Guest]

I tried what you suggestion but got the same error.
Could not find your tip #33 - only a link to buy your
tips.

Carl

 

[CSmith]

You must have missed something or clicked somewhere else because all you
have to do is click on the "Tips" link from the main page and then click on
#33. Oh, and it's free!

You might want to post all of your code function too, instead of just a code
snipet.

 

[Carl]

Thank you for the update - I did find the tips. After
keying in the code per your example I did get a different
error on the .Open line:
"The database has been placed in a state by
user 'Admin' on machine 'CARL-HOME' that prevents it from
being opened or loaded."

The code I have entered is (note, the error occurs at
the .open line):

Dim rst As ADODB.Recordset, cnn As ADODB.Connection,
mNull As Boolean
Dim mNewFees As Currency, mNewDues As Currency,
mNewMisc As Currency
Dim YTDGrs As Currency, mFees As Currency, mAcct As
Long, mYear As Long

Text6.Value = "Recalculating Current Transaction
File..."
DoCmd.RepaintObject acForm, "frmRecalcTran"

Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "F:\Data\Access\Local251
\Local251.mdb"
.Open (<<--ERROR OCCURS HERE)
End With

With rst
.Index = "PrimaryKey"
.CursorLocation = adUseClient
.Open "tblTransactionFile", cnn, adOpenKeyset,
adLockOptimistic
End With

I understand this may be pretty basic stuff for Access
2002 but I had written code under Access 2000 which
worked. Now I am just trying to figure out what has
changed. I do appreciate the assist.

Carl

 

[CSmith]

You're welcome!

From a quick glance, your code looks solid.

Question for you: Do you have read / write permission to the F: drive? If
not, that might be your hurdle. You know how Access likes to create it's
little working files everywhere.

 

[Guest]

Drive F is where I store all my data - I'm the only one
who uses the computer and have set myself up as Admin
with ALL authority. The one area I have no clue about is
the provider information - it could be that is not
correct. Was this new in Access 2002?

And, yes, I have run into Access' little quirks about
permissions and have always seemed to have jump thru
hurdles to get around it.

Thanks for the help.

Carl

 

[Carl]

Well, don't what I did, but security is no longer an
issue - the module, with tables & indexes is working fine.

Thanks for the help.

Carl White