Centralizing our database / real-time updates (1 Viewer)

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
I am not familiar with what features are available
with access to solve this problem. Basically here's the scenario, once my database is completed it will be stored in a central desktop. I'll also have a copy in my laptop and my partner will have a copy in his laptop. A client calls us, we open up the access database, we fill out a form. The form creates a record but that record is only on our separate laptop. We need away to combine the records to one database.

I suppose we could save each record on our own laptop and then transfer it to the central desktop and save it in the database on that desktop afterwards; however, problem with that is we have to constantly copy that database into our individual laptop to have the latest copy of the database.

Is there a better solution?

In an ideal world, we make the access run off the desktop (central computer) and we access that computer's information, therefore the database is always updated and accurate but in our case, I we have a few limitations as far as doing something like that.

Any ideas?
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Thanks, I wasn't aware of Briefcase. With seems with Briefcase it updates the actual file/folders that aren't synchronized, and replaces the entire database when there are changes.

The only problem with this is, let's say my partner and I are updating client data for two different clients at two different times. He goes about updating the main database after he's done, so the entire database is replaced. If I update my changes after he does, the main database will be deleted and replaces with mine. Doing this will effectively delete his updates an hour earlier.

I'm wondering if there is a way that, once the laptop is connected with the main desktop, that we can update just the specific records that were changed from our laptop to the desktop database seamlessly.
 

Mile-O

Back once again...
Local time
Today, 09:47
Joined
Dec 10, 2002
Messages
11,316
Just use the Database Splitter to get a front end and a backend. Leave the backend on the main desktop and copy the frontend to each laptop.

Sorted.
 

TKnight

Registered User.
Local time
Today, 09:47
Joined
Jan 28, 2003
Messages
181
Duckster... if your laptops are connected to the internet when they are "on the road" you can split the database and store the back end on a web server and both connect to it using the front end. This way you'll be working on the same database so won't need to sync/overwrite anything when you get back.

If you're not connected to the internet or don't have a web server things get a bit more tricky. If all you want is to ADD new records then it's not too bad. You can store the "main" database in the central location and create another DB to take on the road with you where you (and your partner) add records. When you get back you can both append the new records from the portable DB to the central one. That way nothing will get overwritten.

If you need to EDIT records then it's going to be a very difficult task unless there are set rules for the acceptance of the edits i.e. if your edits are always going to be accepted over your partners (or vice versa), or if the latest edits are always going to be accepted regardless of who made them.

If any of the solutions above are workable for you let me know and i'll go into more detail if you need it...

HTH

Tom
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Thanks for the help SJMcAbney and Tom, really helps. :) Regarding the database splitter, I'm assuming this is a function built into Access? I think this will work successfully in keeping the backend of the database untouched.

Tom, was wondering if you (or anyone else) could elaborate a little, on 2 things:

1) YOu mentioned: "If all you want is to ADD new records then it's not too bad. You can store the "main" database in the central location and create another DB to take on the road with you where you (and your partner) add records. When you get back you can both append the new records from the portable DB to the central one. That way nothing will get overwritten."

--> when you say "append the new records from the portable DB to the central one", do you mean simply manually entering or replacing the file/record in the central DB? Let's say I ADD a few client records on my copied portable Access DB for the day (offline), then connect with the central Access DB later on. Is there any automatic or automated process that helps adds these new records to the central database? Thanks.


2) I think you're right when you say editing offline, then uploading the edits to the central DB later, will require rules (ie. latest edits always accepted, or one user's edits always takes priorities, etc.). This was actually my main concern. Do you know how I would go about implementing these rules? General procedure?

Thanks again for the help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
Look up articles on Replication. When you replicate an Access database the origional copy becomes the "design master" and copies are referred to as replicas. There is an option to sync the design master to the replicas. When this is happening, new records from the replica are added to the design master, no problem. Updates are also applied. The tricky part is if a particular record in the design master was updated after the the last time it was sync'd with the replica. A human needs to decide which record should be kept, the one in the design master or the one in the replica.

That's the 10,000 foot view. Read the documentation. You will find articles in the MSDN as well as the kb and help.

BTW, one somewhat undesirable thing happens when you go with replication. Access changes ALL your autonumbers to random instead of sequential. That means that from that time on, all new autonumbers will be assigned randomly to minimize the problem of conflicts between the design master and replica sets. A random autonumber can range from a very small negative number to a very large positive number. These autonumbers can no longer be used as your customer numbers or order numbers if these numbers are ever printed or given to the customer to be used for reference. A customer probably will be confused by an order number = -2. If this is going to cause you a problem, you'll need to switch to custom generated numbers. Post back for details if you need them.
 

TKnight

Registered User.
Local time
Today, 09:47
Joined
Jan 28, 2003
Messages
181
Duckster

An Append query will copy records from one table and insert them into another. If you have your Client table in the central DB (ClientTableA) and copy of that table in your portable DB's (ClientTableB) you can add to ClientTableB table then "upload" the information to ClientTableA when you get back to the office.

A simple solution...

In your portable DB create a link to ClientTableA (File > Get External Data > Link Tables).
This link will only work when your portable DB can "see" your central DB (i.e. it's on the same drive or on the laptop when it's docked to the machine) however it won't cause any problems when you're on the road as long as you don't use it.

Then build an append query in the portable DB (see help on action queries) to copy new records from ClientTableB to the linked ClientTableA. You can then run this query every time you get back into the office and it will add the new records to the central DB. You might want to create a delete query to clear the records from ClientTableB once you've appended them otherwise you could add the same record twice.

As explained earlier, this will only work with adding new records. You might want to experiment with this and see if it meets your needs before tackling updates (which is your second question)

See if you can get that solution working, then I can help you more if you want to go further with it...

HTH

Tom
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Thanks a lot. This is really helpful info. I'll try some of these things and give you guys an update to what I do. Regarding the actual editing of records (as opposed to adding), Pat made a good point mentioning a human decision is needed whether or not to replace some of the data - sometimes i get over my head w/ all the technology around. I'm still working on the actual database and will test some of these things out very shortly. Will update soon... :)
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
I know this thread was from awhile back, but I finally had the need to really set this centralizing up and I followed TKnight's instructions above using an append query.

It has worked well however I have 2 problems:

1) After running the append query in my off-site database, the table on the central database recreates all the original records, THEN adds the additions at the end. So, if the central database had 50 records, after running the update it will show over 100 records (recreates and adds the same 50 records, then adds the updated ones from the off-site databse). I THINK I need to create a delete query to correct this, but am not sure how to go about doing this. Can someone help?

2) The database has more than one table that needs to be updated. For example, I have a Client Table and a Contact Date Dependant Table. The Contact Date Dependant table is related to the Client Table via the automatic primary key (ClientID fields). Now, when I create another Append Query for the Contact Date Dependant Table and run it, it does not seem to work. It does not update at all. I get an error message due to key violations.

The primary key for the Client Table is an autonumber (ClientID), and the primary keys for the Contact Date Dependant Table is (ClientID and ContactDate).


Thanks for the help in advance.
 
Last edited:

boblarson

Smeghead
Local time
Today, 02:47
Joined
Jan 12, 2001
Messages
32,059
I've had great luck using Replication for a big database that we have. I have the main database on the server and about 15 replicas on various desktops and laptops.

The one benefit I have is that I have the Access 2000 Developer Edition and it contained Microsoft Replication Manager 4.0, which has been wonderful. I can set it to replicate every 15 minutes and it keeps us all in synch automatically without having to either do it manually or create my own code.
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Thanks, I may look into this in the future. I just need a simple solution to centralizing my database when i add client records from my laptop out of the office.

Still having problems =(
 

TKnight

Registered User.
Local time
Today, 09:47
Joined
Jan 28, 2003
Messages
181
Duckster, If I re-read this correctly you add records to your "mobile" version of the DB when you're on the road and then need to append them to the main DB when you get back. You're not editing other records on the road, only adding new ones.

If that is correct this will solve your problem:

The append query that posts the records from the mobile table to the main table needs to filter out records that are already in the main table. You can do this by joining them in the queries design view. Make sure the join includes all from "mobile" table and only records from "Main" table where ClientID's match (Double click on the join to get this option). When this is done include the ClientID from the main table in the query and set the criteria to Is Null. This will then pick up records from the mobile table that do not have related ID's in the main table. (Be careful - Do not try to append two client ID's!)

You may want to re-think the autonumber client ID because you can't append to an autonumber field. Where you could get unstuck is if people are using the Main DB whilst you are away.

Say records
1, 2, 3 and 4 are already in the DB when you leave the office. You add 5, 6, 7 and 8 whilst on the road but your colleague also adds a record. As the DB's are separate at that point his client ID will be 5. When you come back into the office and run your append query the DB will think "I already have client 5 so skip that one" and you'll lose a client. Also the link between your Client table and contact date table is at risk of becoming jumbled because you don't have complete control of the ID's.

What you are doing is ok but you need to be aware of all the things that can muck up your data and make sure you deal with them appropriately.

Try and get the append query to only insert the new records and then get back to me if you want more help with the rest...

HTH

Tom
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Thanks Tom and the rest of u guys, have been extremely helpful. I've been progressing well with this.

2 obstacles I'm having right now and haven't been able to figure out:

1. I still am not able to create one append query which updates "multiple" tables in my "main database". In the append query, I'm only able to update one table, and when I try adding a second related table to update, the query does not work. For example, I have a client table right now (clientID is PK), and a contact date dependant table (clientID and contact date are the PKs). If i create an append query for just the client table and run it, it updates the new records without a problem. However, if I add the second table (the contact date dependant table), the query won't run, and won't update. Right now the database contains a form with information that requires additions to both tables, so, I need an append query which is able to update both tables. One way around it is creating a second query (2 queries in total for this), but is there a way to have it all in one query?

2. For my contact-date dependant table, there is only one client, and the client may have more than one contact date. I was testing my append query to update my central database, and for new clients that i've added with MULTIPLE contact dates, I noticed that after running the query, only the FIRST contact date and info. appears in the updated table. Does anyone know how to fix this?


Again, thanks a lot.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
1. An append query can only add rows to a single table. You will need two append queries if you have two tables.
2. It is not clear whether you are adding to the 1-side table or the many-side table. Explain your structure and post the SQL.
 

dent

Registered User.
Local time
Today, 09:47
Joined
Sep 22, 2005
Messages
17
I have exactly the same problem. Posted the question before i saw this thread. Pat if the sequential id numbers generated by access are not a problem for reference, is your previous post the way to go with this?
 

duckster

Registered User.
Local time
Today, 02:47
Joined
Jul 17, 2004
Messages
78
Pat Hartman said:
1. An append query can only add rows to a single table. You will need two append queries if you have two tables.
2. It is not clear whether you are adding to the 1-side table or the many-side table. Explain your structure and post the SQL.

Thanks, I've created two append queries. My append query for the Client table works. When I run it, only the new records are added to the central database Client Table.

For the second table, the Contact-Date Depandant table, only the first new record of each client gets added. If a client contacts us more than once, there will be two records in this table for that one client. I can only get the first record to update for some reason. This table is a one to many relationship, one client may have more than one contact-date. The primary key of this table is based on two fields: 1) the ClientID field, which is an auto-generated field, and 2) contact date field.
 

TKnight

Registered User.
Local time
Today, 09:47
Joined
Jan 28, 2003
Messages
181
Not sure why that is happening...

It's either filtering something out of the table you're appending from or it's breaking the rules of the table you're appending into and ditching the records. Are you running the queries manually or with code?
If with code don't use DoCmd.setwarnings = false just yet so you can see if that is the case.
If you are running it manually open the query in design view and then view it as a datasheet. If you are expecting two records and you have one then you're filtering on something, but if there are two records there and only one is being inserted by the append query then you're breaking a rule somewhere and Access is ditching the record.

Once you know which one of these is causing the problem you should be able to get it sorted...

HTH

Tom
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2002
Messages
42,970
duckster, you didn't post your SQL.

dent, I answer dozens of posts each day so I'm sorry, I have no idea what you are talking about. Post your question in the original thread.
 

Users who are viewing this thread

Top Bottom