Table Move Causes Code Failure?

Topic

New Reply

Hello All,

I was fixing some code the other day and received an error, which I managed to fix, which I still don’t understand why the change in question caused the error.

Version Access 2003.

The table tblFees was located in the Front End DB and I moved it to the Back End DB and fixed the associated linking. When I ran the code to generate the Email Billings for our Annual HOA dues this code bombed. Note: it’s been working for 5 years.

Code:

 
 '***** Check to see if Rates for that year are present in tblFees *****
   
   Set rst = dbName.OpenRecordset("tblFees")
   rst.Index = "PrimaryKey"
   rst.Seek "=", dtBillDt
   
   If rst.NoMatch Then

The rst.Index line generated a 3251 error.

Changing it to this made it work again.

Code:

  '***** Check to see if Rates for that year are present in tblFees *****
   
   Set rst = dbName.OpenRecordset("tblFees", dbOpenDynaset)
   rst.FindFirst "BillingDate = #" & Format(dtBillDt, "mm-dd-yy") & "#"
    
   If rst.NoMatch Then

I’d really like to know why this change was necessary, the stuff I found googling helped me get it fixed but didn’t help at all in understanding the problem. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

The short answer is that the Seek Method just does not work with Linked Tables.
You can read a bit more here.

Reply | Quote

John,

Thanks. :cheers:

Anybody happen to know the “Long” answer. I’d really like to know. I do suspect that since a linked table can be things like Excel worksheets has a lot to do with it. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I don’t know whether this helps you (or me)?
The Seek method is very fast because it uses an Index. The FindFirst method just works through the Recordset until it finds something.

So the first line of your Seek code specified the Index to use:

rst.Index = “PrimaryKey”

The Index Property of a Recordset is only available for Table Type Recordsets.

30387-Index

30388-Recordsettype

Reply | Quote

John,

Thanks again! That makes it clearer. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You can still use the Seek method, but you have to open the table directly in the backend rather than referencing the Linked table. In your open statement, you used:

Set rst = dbName.OpenRecordset(“tblFees”)

If you instead opened “dbName” so it pointed directly at the backend database, you could then use the Seek method.

Reply | Quote

I’ve never used Seek. Seek would be presumably be faster than FindFirst if the former is based on an index. But then there is the time to open the direct link to the backend.

I must remember this in future and check where search times are critical.

I’m glad the initial query was raised.

Reply | Quote