Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I have a FEdb/BEdb application. The BE consists of 1 db containing all application master tables and a series of client specific db's . The FE always has the master tables db attached and only one client db at a time. It was designed this way as the client db's can be nearly one gig each and there are over 1000 clients. This allows the user to put several client dbs on his laptop along with the master table db and FE and take them on them out of the office with him. The FE has a tab control with two tabs, one which has all the forms for the master tables and one for attached client data forms .



    Clients will frequently call the user with a change to their dollar balance for a particular year. As it can take upwards of a minute or two to change the attached client tables from one client to another over his network, depending on network load, such a change becomes a drag on productivity.



    I thought a simple solution would be to connect to the particular client db without attaching it .

    Dim dbSrvr As DAO.Database

    Set dbSrvr = OpenDatabase(pAssocPath) where pAssocPath is the fully qualified file name for the client database.



    Create and execute an update query

    stSQL="UPDATE A_tblBalances SET A_tblBalances.CurrentAssessment = 1000 WHERE (((A_tblBalances.SYear)=2010));"

    dbSrvr.Execute stSQL



    I created a command button, in one of the forms on the master tables tab, to capture the user data and execute the above query.



    While the query does update the client data, an error " . duplicate values in the index, primary key, or relationship " is raised when one subsequently clicks on the tab containing the attached client data forms .



    Thus far I've not been able to trap the error. The message seems odd as existing data is only be updated, there are new record entries nor any relationship changes. Also the update is to an unattached table so I'm mystified as to why the error is raised after the fact in the FE.



    Appreciate any and all thoughts on this.

    Marty


  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I don't know why you are getting that error. You might want to make sure you add these lines:

    dbSrvr.close
    set dbSrvr = nothing

    Another way to execute a query against a table in another db is to use the IN predicate. Your form the string as follows:

    stSQL="UPDATE A_tblBalances IN " & pAssocPath & " SET A_tblBalances.CurrentAssessment = 1000 WHERE (((A_tblBalances.SYear)=2010));"
    currentdb.execute stSQL

    You might have to enclose the path in quotes, in which case you'd use: ...& chr(34) & pAssocPath & chr(34) & ...

    Separately, how are you relinking to each db? Are you deleting the linked tables in the FE and then relinking? If so, a faster way is to modify the .Connect property of each table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks much for your response. I do have the Close and Set statements at the of the routine and I do use the refresh connection method to relink the tables. In the past I've put some debug.print statements (table name and time ) in the refresh connection loop. Nothing odd jumped out in a review of the results, it just seems to take that long to refresh 75 tables.

    I'll give the IN statement method a try and let you know if anything changes.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    No change when using the IN clasue in the SQL. However, I think I've stumbled onto the problem, db locks ... clientdb.laccdb

    In my testing I would use one client db as the target of the test and then flip between two client dbs, i.e. 1) attach client A and modifiy data in client B, 2) reattach client B verify changes made and db OK, 3) reattach client A and modifiy client B data ... this is when the error would now appeaar. What I stumbled across is even though I refreshed the client tables with client A connection, Access has kept a lock on the client B db. In further testing, it looks like most the time the client B db would be released if I first went through the steps of attaching two other client dbs before making a change to unattached client B. It also looks like if I reattch three client db consecutively, I don't have the problem. My guess the difference between two and three client db attachments is in the time it takes for Acces to release the locks.

    Is there some way to force the release of a db lock via code? My search has not found anything, but I'm hopeful I just wasn't using the right terms.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    As far as maybe speeding-up the relinking, I'd use the same "Set dbSrvr =..." you mentioned when you executed the query, but also use it just before the linking code. I think this allows Access open the BE database and find a table just a tad faster.
    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
  •