none
Advice on Sharing Linked DBF Files RRS feed

  • Question

  • I can think of two options.

    1. Run a local copy of the Access "front end" database on each "client" computer, linked to a common set of DBF files on a file server.

    2. Host a single Access front end database on the file server (linked now to local DBF files) and have each client computer run it over the network.

    There are only 2 "clients" by the way, and only one of them will be active at any time, the other is a standby system.

    Can anyone offer any options as to which would work better?

    Does Access lock DBF files that are being accessed for inserts or updates? If so when are the files released?

    I have seen discussions about these to options when using front end/back end MS Access databases but I am not sure whether the same would apply when the back end is a set of DBF files. I don't think they support multi-user access to tables as well as native MS Access or SQL Server.

    There are plans for move away from DBF files but I need to work with them in the meantime.


    R Campbell

    Tuesday, January 10, 2017 3:09 AM

Answers

  • Can anyone offer any options as to which would work better?

    As a general rule, you still want to deploy a copy of the front end to each user. The reason for this is if a workstation shuts down, or freezes up, then the copy of this front end that was not closed etc. could be damaged, but the copy that the other user has will not be damaged. So from a reliability point of view it still always better to give each user a copy of the application. So the fact of some linked dBase table, or some Access linked table(s) changes nothing in regards to this “split” suggestion and that each user is provided a copy of the front end.

    So in near all cases, placing the front end with the linked tables on each workstation will work better and be more reliable.

    Does Access lock DBF files that are being accessed for inserts or updates? If so when are the files released?

    Access (to my knowledge) will always lock the row and two users cannot edit one row at the same time. This behaviour is somewhat different then the Access default for linked access tables. For Access tables two users “can” start editing the row at the same time unless you specify that the form lock that row. And if you don’t specify anything, then the second user will receive the command and dreaded message of

    “Another user has changed the record”.

    So for dBase files, the DEFAULT is to lock the row and prevent other users from making changes the very instant the user dirties (starts editing) the record (quite sure you can't change this option).

    So linked dbase tables, Access locks are immediate placed on that row and the second user is not allowed to even start to edit (dirty) the record. Both users however are free to browse and view records - this locking only occurs when editing starts by one user.

    Note that Access 2013 lost this feature, but Access 2010 and 2016 to my knowledge has support for dBase files.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Marked as answer by Dick Campbell Tuesday, January 10, 2017 10:37 AM
    Tuesday, January 10, 2017 3:59 AM

All replies

  • You're asking about things that will not happen in your situation, which is "only one of them will be active at any time".

    "which would work better"? Define "better". Assuming you mean performance, I'm not sure it matters much, but for sure try it both ways in your environment.


    -Tom. Microsoft Access MVP

    Tuesday, January 10, 2017 3:55 AM
  • Can anyone offer any options as to which would work better?

    As a general rule, you still want to deploy a copy of the front end to each user. The reason for this is if a workstation shuts down, or freezes up, then the copy of this front end that was not closed etc. could be damaged, but the copy that the other user has will not be damaged. So from a reliability point of view it still always better to give each user a copy of the application. So the fact of some linked dBase table, or some Access linked table(s) changes nothing in regards to this “split” suggestion and that each user is provided a copy of the front end.

    So in near all cases, placing the front end with the linked tables on each workstation will work better and be more reliable.

    Does Access lock DBF files that are being accessed for inserts or updates? If so when are the files released?

    Access (to my knowledge) will always lock the row and two users cannot edit one row at the same time. This behaviour is somewhat different then the Access default for linked access tables. For Access tables two users “can” start editing the row at the same time unless you specify that the form lock that row. And if you don’t specify anything, then the second user will receive the command and dreaded message of

    “Another user has changed the record”.

    So for dBase files, the DEFAULT is to lock the row and prevent other users from making changes the very instant the user dirties (starts editing) the record (quite sure you can't change this option).

    So linked dbase tables, Access locks are immediate placed on that row and the second user is not allowed to even start to edit (dirty) the record. Both users however are free to browse and view records - this locking only occurs when editing starts by one user.

    Note that Access 2013 lost this feature, but Access 2010 and 2016 to my knowledge has support for dBase files.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    • Marked as answer by Dick Campbell Tuesday, January 10, 2017 10:37 AM
    Tuesday, January 10, 2017 3:59 AM
  • Thanks for both replies. My instinct was to have a local copy of the front end on each client, linked to the DBFs on a common file server. I thought that I should ask the question just the same. I was not at all sure that Access would lock at row level for DBFs. I was concerned that entire DBF files could by locked. I am still not sure how row locks can work with two front end databases. Having said that, it would not be normal,in this application, for editing to be simultaneous from both front ends. The demise of DBF support will help justify moving away from them sooner rather than later.

    R Campbell

    Tuesday, January 10, 2017 10:48 AM