Results 1 to 7 of 7
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Table Move Causes Code Failure?

    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The short answer is that the Seek Method just does not work with Linked Tables.
    You can read a bit more here.
    Regards
    John



  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Thanks.

    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.

    Index.gif

    Recordsettype.gif
    Regards
    John



  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Thanks again! That makes it clearer.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •