How can User1 and User2 update the same Access database and save?

[Guest]

We have two computers User 1 updates records and posts. Those records are
maintained and held in the database. User 2 updates and posts but the tables
do not hold and post causing User 2 to go to User 1's computer to re-enter.
Does anyone have any ideas how to resolve this conflict?

 

[Bill Mosca, MS Access MVP]

All things being equal, the last update should be the one that wins. If both
are trying to update the same record, the second one should get a message
about the record being locked.

If this is consistantly happening (User2 can't update on his machine while
User1 is in the database) then I would suspect User1 has the database opened
exclusively.

 

[Douglas J. Steele]

What exactly happens when User 2 updates? Is there any error message?

If not, are you sure User 2 is updating the correct database?

 

[George Nicholson]

User 2 updates and posts but the tables
"Do not hold and post" means what, exactly? Neither User can see them? User
1 can't see them but user2 can? Something else?

If User2 makes changes that seem to "hold" on their copy but User1 can't see
those changes, consider the possibility that the 2 users are connecting to
(and updating) entirely different back-end data files.


HTH,

 

[Guest]

Doug,
Thanks for replying. User 2 (me) updates the records from my system. User
1 (admin) then validates my udates, but when she goes in to validate the
system shows that NO updates have been performed. Do I have to adjust
something in Access in order to have my updates "take" from my system or do I
have to keep going to her computer?

 

[Guest]

George,
Thanks for replying. "Do not hold or post" means that when I update from my
computer, my admin does not see the updates on her computer in the Access
tables. I show the updates on my system, she does not on hers. Ideas? I am
not the most fluent with Access, so be gentle, please.

 

[Guest]

Bill,
Thanks for the reply. User 2 (admin) is not in the database
simulataneously. I update, then she goes in to validate, the records to not
reflect that any of the updates were posted by me. Now what? No genius
here.....

 

[Douglas J. Steele]

There's no setting in Access you'd need to fuss with, so the issue is either
there's something wrong with how the application queries for updates (does
it perhaps use the UserID in the query somehow?), or, as I suggested
earlier, you're not both using the same database.

 

[Joan Wild]

We are assuming you are using a split database. User1 has a frontend mdb on
their computer; User2 has a copy of the frontend on their computer. There
is a 'backend' mdb on a server that these two frontends are linked to.

It appears that the two frontends are not linked to the same backend. Go to
each computer and open a table in design view; go to View, Properties. Look
at the 'Description' property for the table. That will tell you the
location of the backend. Repeat on the other computer. I'll bet that they
are pointing to two different backends.

 

[George Nicholson]

If User2 sees the updates that they have made but User 1 then logs in and
does not see those updates, the Users are simply not looking at the same
data file.

Is this a split database? That means there is one file that lives on each
user's local machine that holds Forms, queries, code and one "common" file
that lives on a server holding data tables. When the app opens on a given
user's machine, it connects to the "common" data on the server. It sounds
like that isn't what is happening. It sounds like you are each looking at
your own separate set of data.

HTH,

 

[Guest]

Thanks Joan,
So far the most useful information, appreciate your help, let you know how
it works.

 

[Guest]

Thanks George,
So how do I check and correct if this is the case?

 

[Joan Wild]

OK. If you do find that they are pointing to different backends, you need
to decide which is the 'true' backend. You can use Tools, Database
Utilities, Linked Table Manager to refresh the links to point to the correct
backend (be sure to put a check at the bottom of the dialog to locate the
backend).

Once you have things working, you should consider backing up that surplus
backend, and then deleting it. You should have only one 'live' backend.
The only reason you might have/want two is if you want to use one for
testing purposes.

 

[Guest]

Another way to determine all linked JET (.mdb) and Excel (.xls) files is to
create a new query and use the SQL (Structured Query Language) statement
shown below. To do this, create a new query. Dismiss the Add Table dialog
without adding any tables. In query design view, click on View > SQL View (or
click on the black SQL toolbar icon). You should see the word SELECT
highlighted. Delete this SQL keyword. Copy the SQL statement shown below and
paste it into the SQL view:

SELECT Left(Database,255) AS [Linked Databases],
Count(MsysObjects.Database) AS [Number of Tables]
FROM MsysObjects
WHERE (MsysObjects.Type)<>9
GROUP BY Left(Database,255)
HAVING Left(Database,255) Is Not Null;

Notes:
1. Grouping on the first 255 characters is required for Access 97, but not
later versions, since [Database] is a memo data type. (Contributed by Doug
Steele).

2. The criteria, WHERE (MsysObjects.Type)<>9, is used to filter out any
table constraints created with ADO. (Contributed by Dirk Goldgar).

You can now switch back to the more familiar design view, if you want to, by
clicking on View > Design View. Save the query and run it. When run from both
PC's, the results (recordset) should be identical.

Once you get this immediate problem solved, you may want to have a look at
the following article that I wrote:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

The second paragraph of this article includes a link that discusses the
benefits of splitting a database into a front-end (FE) and back-end (BE)
files.

Note: On the issue of not seeing records, have you ruled out that it is not
simply caused by a need to requery the form's recordset? An easy way to test
for this possibility is for one user to add or modify a record. The second
user likely will not see this change, until after they close and re-open the
form. If this is the solution to your issue, then you can add a command
button on the form, with some simple VBA code, to requery on demand.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[George Nicholson]

The easiest way would be Tools>DatabaseUtilities>LinkedTableManager and then
use the resulting dialog to "reset" one of your front ends so that it uses
the same data file as the other. (You might need to hold down shift while
opening the db to see this option).

However, depending on your set up, there could be a number of reasons why
you can't do it the easy way, among them:
- The app was built by a developer who has crippled certain functions
and/or toolbars
- You are using a runtime version of Access and the developer hasn't
provided this, or a similar, functionality
- your installation of Retail Access didn't include this option by default
(not sure about this one, my memory is hazy but is vaguely telling me it was
an issue once upon a time. If you are using Access 2000-2003, you can ignore
this, afaik.)

If you can't get to the Linked Table Manager, we'd need to know a bit more
about what your setup does allow you to do (reset built-in toolbars?, view
design modes for tables? view code in Visual Basic Editor?) before we could
tell you the easiest way to fix (and, if your database is badly designed &
locked down tight, it might require the original developer to fix it).

BTW, if you are using a single-file database on a shared server rather than
a split database (which might be acceptable given the 2 user usage you
described), check the shortcuts that you each use to see if you are both
opening the same file on the network. Correct the shortcuts if they are
pointing to different files.

HTH,

 

[Guest]

Thanks Tom I will give it a shot and take a look at your article.

Another way to determine all linked JET (.mdb) and Excel (.xls) files is to
create a new query and use the SQL (Structured Query Language) statement
shown below. To do this, create a new query. Dismiss the Add Table dialog
without adding any tables. In query design view, click on View > SQL View (or
click on the black SQL toolbar icon). You should see the word SELECT
highlighted. Delete this SQL keyword. Copy the SQL statement shown below and
paste it into the SQL view:

SELECT Left(Database,255) AS [Linked Databases],
Count(MsysObjects.Database) AS [Number of Tables]
FROM MsysObjects
WHERE (MsysObjects.Type)<>9
GROUP BY Left(Database,255)
HAVING Left(Database,255) Is Not Null;

Notes:
1. Grouping on the first 255 characters is required for Access 97, but not
later versions, since [Database] is a memo data type. (Contributed by Doug
Steele).

2. The criteria, WHERE (MsysObjects.Type)<>9, is used to filter out any
table constraints created with ADO. (Contributed by Dirk Goldgar).

You can now switch back to the more familiar design view, if you want to, by
clicking on View > Design View. Save the query and run it. When run from both
PC's, the results (recordset) should be identical.

Once you get this immediate problem solved, you may want to have a look at
the following article that I wrote:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

The second paragraph of this article includes a link that discusses the
benefits of splitting a database into a front-end (FE) and back-end (BE)
files.

Note: On the issue of not seeing records, have you ruled out that it is not
simply caused by a need to requery the form's recordset? An easy way to test
for this possibility is for one user to add or modify a record. The second
user likely will not see this change, until after they close and re-open the
form. If this is the solution to your issue, then you can add a command
button on the form, with some simple VBA code, to requery on demand.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

We are assuming you are using a split database. User1 has a frontend mdb on
their computer; User2 has a copy of the frontend on their computer. There
is a 'backend' mdb on a server that these two frontends are linked to.

It appears that the two frontends are not linked to the same backend. Go to
each computer and open a table in design view; go to View, Properties. Look
at the 'Description' property for the table. That will tell you the
location of the backend. Repeat on the other computer. I'll bet that they
are pointing to two different backends.

 

[Guest]

Thanks George, we'll take a look and see if this doesn't resolve our issue.
Appreciate your suggestions.

 

[Jamie Collins]

WHERE (MsysObjects.Type)<>9, is used to filter out any
table constraints created withADO. (Contributed by Dirk Goldgar).

He probably meant, CHECK constraints created while in ANSI-92 Query
Mode.

Jamie.

--