Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: How does Access deal with programmatic changes to a shared database?

    Hello all:

    I'm creating a new Access database that will be a shared-file database residing on our company intranet on a virtual drive. It will have the following features:

    1. The database will only consist of tables.
    2. The database will be accessed only by VBA and VB.NET programs being written to communicate with it.
    3. The data in the tables will simply need to be added to, deleted and edited.
    4. The database may possibly accessed simultaneously by programs launched by multiple users.
    5. Database access is open to all users; there doesn't need to be user-level security.
    6. It would be nice if the database could not be directly accessed/opened by any user: only the accessing programs could open it programmatically.

    I am somewhere between a beginner and a moderately experienced VB/VBA programmer with no formal training, so please bear with me. In my Access database, I am concerned about multiple users launching the accessing programs [I'm writing] simultaneously and data in the database being lost or incorrectly managed when multiple programs go to access/edit the same data.

    For the database manipulation in my accessing programs, I had been using the only programming I know to deal with MS Access: DAO objects, DBEngine, OpenDatabase, TableDef, etc. with the use of SQL string statements such as SELECT, UPDATE, WHERE, INSERT INTO, and so on in the following manner:

    objDatabase.Execute strSQLStatement

    Unfortunately, it seems that I simply lose the effect the SQL statement being executed if another program is currently editing the same database records. Is there a [simple] way to line up the commands to wait their turn? I've read a little about connections and threading, but I would need more specific direction if that is the route I need to take.

    Any direction you could provide or specific VB/VBA coding you would suggest:

    - database type: MS Jet vs. OBDC,
    - database object vs. connection object,
    - general objects,
    - general methods,
    - anything else

    would be truly appreciated. Thanks in advance.

    Brent

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I wonder though if maybe one of the MSAccess Tools->Options for opening the database "exclusive" verses "shared" might be set (under Advanced tab) or "No Locks" is not selected in this tab. Can you relate what these settings are?

    The code you provided as an example is DAO. I'm not sure if ADO code might help you better as far as the table locking on you if someone has it open. I usually work with SQL Server tables (linked in an MSAccess mdb via an ODBC DSN) and I never have problems with running vba code against them even if someone has the table open. It seems wierd that your DAO code stops working if someone has the table open but I usually write in ADO so it's difficult for me to say if that makes a difference or not.
    Last edited by pkstormy; 07-15-07 at 15:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2007
    Posts
    3
    What happens when the user you described has a table open and is editing a record when your program code runs to edit that same record? Does the code wait until the user is done? Does a message pop up for the user that the record that they are trying to change has been changed by the program?

    Brent

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hi again Brent H,

    In the case of 2 different users in the same record, SQL Server does a very nice job of managing who has last edited the record. Of course, if I design some poor code (i.e. open 2 forms for editing based on the same record and have code which writes to that record also, it will produce the error you've mentioned.) There is an example of this in the MSAccess Code Bank showing how poor code can produce the error.

    But having 2 different users accessing the same database and same record (with good code), should not produce the "you are trying to change the same record." I have a lot of MSAccess databases with linked MSAccess tables and users editing the same record and still don't get this error. Even if a user has the table open and is editing a record (which goes against all rules - you should never let a user edit a table directly but let them edit data via a form instead), it should not produce an error (at least I'm pretty sure it shouldn't but I never let users edit tables directly.)

    You may want to look at your code again as I "think" this might be more of a coding issue and the way the code is designed in your other front-ends which access the data. If this continues to be a problem for you, you can always design UNBOUND forms which never have a table directly bound to a form and prevent the "changing the same data" error. But I think your problem might lay in having bound forms and opening the same record again and again in code and trying to write to the table with the form open on that same record (and other forms possibly open in that same front-end on that record as well - again, take a look at the example in the Code Bank.)

    As a rule of thumb, I try to avoid having multiple forms open (for editing!!) which open the same specific record.

    Sometimes though, it's a matter of simply issuing a "refresh" command to the form before the code to update the record.

    Question: Are you getting the error when the user edits the table directly or when a user is editing the data via a form/other code?
    Last edited by pkstormy; 07-15-07 at 17:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jul 2007
    Posts
    3
    Please keep in mind that the Access database tables will only be edited by external programs. There are no forms. In our application, the Access database tables are simply used as a central repository for information from multiple programs.

    It would seem that the problem I described of two external programs accessing and editing the same information in a database table at the same time would have been encountered before; I simply don't know how Access deals with this issue. At the moment, I am generating the problem I described by running a program to edit a record that I am currently manually editing: a field in a record in the table is in the middle of being edited by me (manually), I run my program on that record, and the change the program is trying to perform is unsuccessful because I am in the middle of editing the field. I receive no error message in the external program or from Access while I'm in it.

    Does this make sense?

    Brent

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you leaving recordsets open until a user clicks "update" or whatever?
    I bet this is causing the issue!

    Generally you want to open a recordset and display the results then immediately after CLOSE the recordset. When your user hits save you want to re-open the recordset, run an UPDATE SQL statement and CLOSE the recordset again.
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Check what georgev stated. Something has to be wrong with the code on how you're accessing the tables.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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