Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I've just run into an old problem where the autonumer is out-of-sync. In 2003 mdb's I've used the code from Microsoft's Article ID: 287756 with out any problems. In attempting to apply the code to a 2007 linked accdb, I'm getting an error (-2147467259: Invalid argument) on the statement which assigns the new value.

    Set col = Cat.Tables(stTbl).Columns(stCol)

    col.Properties("Seed") = lngSeed
    Cat.Tables(stTbl).Columns.Refresh

    If col.Properties("Seed") = lngSeed Then
    SetNewSeed = True
    Else
    SetNewSeed = False
    End If

    Walking through the code, the value of col.Properties("Seed") is the old/invalid value. There must be something I'm missing with a linked accdb table when attempting to assign a new value.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you run the code in the "backend" database containing the table instead of in a "frontend" database containing a link to the table?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Code works fine on a non-liked table. But with over 800 back-end databases (a design decision to easily allow portions to be taken into the field), embedding the code in each one would be a daunting task. While on-the-fly repair is the most desirable solution, I'm looking into looping through all the BE's progrmatically and testing each table in each database for for out-of-sync seed numbers.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='mcowen' post='795495' date='29-Sep-2009 08:52']Code works fine on a non-liked table. But with over 800 back-end databases (a design decision to easily allow portions to be taken into the field), embedding the code in each one would be a daunting task. While on-the-fly repair is the most desirable solution, I'm looking into looping through all the BE's progrmatically and testing each table in each database for for out-of-sync seed numbers.[/quote]
    Does KB Article 884185 shed any light on the issue? It isn't clear to me how Jet 4.0 SP8 gets involved with ACCDB tables, but I can do some research if that would be helpful... I believe the issue is that you are actually changing the design of the table as far as Access is concerned, but you don't really have the ability to do that for a linked table. Have you considered the possibility of going to a random autonumber as you can do with replication? You scenario sounds much like a replication problem.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Tired the code from the article, doesn't work on a linked table. You've probably hit the nail on the head, Access thinks its a design change. So I'll plunge ahead with a back-end batch sweep approach to identify any other seed problems and then set up a manual proceedure for the user to follow to correct them.

  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
    [quote name='mcowen' post='795521' date='29-Sep-2009 12:36']Tired the code from the article, doesn't work on a linked table. You've probably hit the nail on the head, Access thinks its a design change. So I'll plunge ahead with a back-end batch sweep approach to identify any other seed problems and then set up a manual proceedure for the user to follow to correct them.[/quote]
    I'm not real good at ADO, but you should be able to run code in the frontend that directly opens and works with a table in the backend. Of course, you need to know which database, and where it is, but you should be able to get this from the Connect property of the linked table in the frontent.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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