Need help with Database

[TotallyConfused]

I hope someone can help or give me some guidance. There are two users who use
the same database. We each have our own copy on our PCs. I merge the table by
using the following in a query to update the fields. Field by field.

Nz([tblTRACKING].[CoName],[tblTRACKING1].[CoName])
Nz([tblTRACKING].[CoName],[tblTRACKING].[CoName])

However, the fields have increased and I was wondering if there is an easier
way to do this? I cannot split db with back end. The share drive where this
would be housed makes the db run very very slow. Our server is on the East
Coast and the users are in the West Coast. The other problem with sharing db
is that the users (2) would be hitting on same data.

Would like to know if there is a way to be able to update all the fields that
need updating at once. Fields consist of checkboxes, dates and text.

Any help will be greatly appreciated. Thank you.

 

[Klatuu]

Is there any other way you can do this? Keeping the two in sync is going to
be a nightmare. You could use an append query, but then how are you getting
the data from one PC to the other to do this?

All your assumptions about what you can and cannot do are incorrect. Access
is a multi user database, so you and the other user could be working in the
same tables. You will hit some conflicts if you are working on the same
record, but handling that should be a normal part of any database design.

You server speed may be an issue, but having all your forms, reports,
queries, etc. in the same mdb with the date over the server will be the main
problem for performance. In that configuration, you are doubling your
network traffic. I would suggest you at least spend a little time testing
response time. Make a backup copy of you mdb so you can go back to the old
way if this doesn't improve response time.

Split the database, put the Backend (it will have the name of your current
mdb with _be added to it) on the server.
Link your Front End ( the original mdb name) to the back end on the server.
Now have a go at it and see what the response it like.
It wouldn't hurt to have a conversation with whomever is responsible for
your network to see if they can indentify speed problems.
Do some investigtion on performance issues with Access. For example, put
the back end on the server as close the root of the Share folder as Possible.
For example if the Share is something like:
\\MyServer\AShareFolder

And it is, for example, mapped to Drive F: Then put it in F:\
Not in F:\MyCompany\MyDepartment\MySection\MyGroup\Application Files\Access\

Good Luck.

 

[K Dales]

First: read up in the Access help file on the topic "database replication";
it might help solve your needs.

But, for the short answer, you can use 2 append queries. You should not
have to manually type in the field expressions. However, you do have a bit
of a problem since you can't guarnatee a unique primary key. Without knowing
how you distinguish the new records I can't be specific, but link the two
tables by whichever field(s) would give you a unique match for a specific
record. Make it an outer join (i.e.: "Include ALL records from [tblTracking]
and only those records from [tblTracking1] where the joined fields are
equal"). Now in the criteria grid specify the condition that the joined
fields from [tblTracking1] are Null. If these fields are null it means the
record exists in [tblTracking] but not in [tblTracking1]. Add all the fields
from [tblTracking] to the grid and make it an append query, specifying to
append these records to [tblTracking1]. Since all the column names should be
the same it should be able to figure that out (if not you can manually select
where the fields go).

If you run the above query it will append those records (that were in
tblTracking only) to tblTracking1. Do the reverse setup to add any new
records from tblTracking1 into [tblTracking]. After that, whenever you need
to synch the two tables run both append queries.

Hopefully you are only adding new records, not editing existing ones. If
you are editing existing records, you would need to find the ones that don't
match and update them - but you need to know who had the "earlier" version of
the data and who has the "changed" version and also who should "win" if BOTH
users edited the same record. This would require a time/date stamp on the
data and some logic to determine who's changes get precedence so that is a
complicated issue and hopefully you will not need to do all of this. If so
you may need to rethink your approach (see what I suggested above about
replication).
--
- K Dales

I hope someone can help or give me some guidance. There are two users who use
the same database. We each have our own copy on our PCs. I merge the table by
using the following in a query to update the fields. Field by field.

Nz([tblTRACKING].[CoName],[tblTRACKING1].[CoName])
Nz([tblTRACKING].[CoName],[tblTRACKING].[CoName])

However, the fields have increased and I was wondering if there is an easier
way to do this? I cannot split db with back end. The share drive where this
would be housed makes the db run very very slow. Our server is on the East
Coast and the users are in the West Coast. The other problem with sharing db
is that the users (2) would be hitting on same data.

Would like to know if there is a way to be able to update all the fields that
need updating at once. Fields consist of checkboxes, dates and text.

Any help will be greatly appreciated. Thank you.