Marking last Updated Record

[clickaccess]

Hi Gurus,

I'm new here so be gentle with me!

I am writing a database that will have a Master db and about 50 Slave dbs that will feed to the Master. I have figured out how to append from the slaves to the Master and it basically works.

Now, what I need is to do is to make it efficient in terms of Speed and Accuracy. I have decided to do most of the trimming on the slave side so it will be speedy. One of the task that I need help on currently is to determine the last record that was appended in the previous session on the slave side. I have decided to have a temp table that will store this record and when ever appending will consult this table and only append the newer records to the Master so as to eliminate duplicating on the master side.


The three field I want to check when this record is in the temp table are RecordID, EmpID, and the JobID.

So here is a summary of what I need:

To automatically write the last record of the session to a temp table (performed Slave side)

To consult the temp table with the main table to detirmine the new records for appending and then append to the Master (performed Slave side)

Thanks

 

[JeremyNYC]

Two thoughts:

1) I'd jettison the master/slave terms. Pretty offensive to a good number of people, and not accurate in this case.

2) Why are you doing this? Are the 50 other databases on remote computeres that only connect occasionally? Otherwise this hsould all be in one database

and a third one, just for fun...

3) There's really not much information in your post. How are you moving data from one database to the other? What session are you talking about?

Ah, heck...

4) Temp tables, in generally, are terrible for speed, as they cause a lot of bloat in your database, and you wither get slowed down due to the large size or you'll get slowed down due to having to compact the thing so often.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BNPMike]

What you are describing is replication. Often that just means propagating copies from a central master. However some systems do allow updates from multiple satellites. Indeed Access's JET I think does - or used to do - something like that. I'd have a look for a database that provides replication out of the box.

If you do it yourself you may find the last 1% of reliability is not that easy to shake-down, compared to the vendors who've got floors full of programmers.

 

[clickaccess]

Jeremy,

I agree with the first point, that is jettisoned! it was only to convey the message.

Second, yes, currently the complaint is that db is too slow. About 31 analysts use one db. This concept is my suggestion.

Third, currently analysts have their own laptops on which they work and they all have dockports at their desks. They currently access this db on the network whenever they are at their desk. My solution will only connect when they are ready to commit.

I intend to give them(analysts) a copy(customized) of the db just for their own data input and a very low level reporting of their own work. When they are ready to commit their work this UPDATING will take place. Main db will store all data from all 30+ analyst. The supervisor and the manager will have access to the main db and all the reports generated there.

Fourth, temp table can be scuttled as long there is an indication of the last record updated on the main screen whenever the db is accesses.

Any other question?
Thanks

 

[clickaccess]

Does anyone have any clue for this one?

 

[JeremyNYC]

CA,

Did you look into replication? That's what is typically done for situations like this. I've never dealt with remote users, so I haven't had to learn about replication, but I would suggest reading up on it, backing up your database, and testing that out as a possible solution. But read up on it a good bit. I know there are some gotchas to be found, though I don't knwo what they are.

Is your database split into a front end and a back end? If not, that's an absolute must for a situation like this.

But I think replication should be able to do everything you need.

Jeremy

PS: Cheers to the jettisoning!

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[cmmrfrds]

I assume the user is going to connect to the network and press a button for the update, so you will need to do sufficient error checking. No temp table.
(1) put a date field in the client pc's such as exportdate.
(2) create a temp variable and put in the current date i.e. Now() - now will give you date/time, which you want.
(3) write a query that will look for records where the import date is not filled and save the query.
(4) create an ADO connection to the Main database on the network or any other way (there are options) that will allow you to access the main table without requiring the client to attach to the main table.
(5) write an append query that uses the previous query to extract from the client pc and updates the main table. also, have a field added to the main table called import date that you update with the variable date that was saved.
(6) write an update query that updates the export date in the client table with the variable date value you saved.
(7) write a query that counts the records in the client table with export date as the criteria.
(8) write a query that counts the records in the main table with the import date equal to the variable date.
(9) check to make sure the counts equal. if not let the client know they need to talk to you. You might save the imported date so you can use it for recovery.
(10) you should have sufficient information retained for recovery.

 

[clickaccess]

cmmrfrds (Programmer),

I like the way you picked up on this. I don't know what you think but I'm excited about this. There are some differences in my version.

YOU (1) put a date field in the client pc's such as exportdate.
ME is thinking to import the date and time from the Main db that is on the server, So as to have date and time sync.


You (2) create a temp variable and put in the current date i.e. Now() - now will give you date/time, which you want.
ME is thinking same as #1

You (3) write a query that will look for records where the import date is not filled and save the query.
ME is thinking no need.

You (4) create an ADO connection to the Main database on the network or any other way (there are options) that will allow you to access the main table without requiring the client to attach to the main table.
ME is thinking no need to access the main table.
Each analysts work is triplicated, once on their own machine second on the server as a back up of the analysts db and thirdly the analysts work (just the work since last save) will be appended to the main table on the main db (analysts will have no other access to the main db.

You (5) write an append query that uses the previous query to extract from the client pc and updates the main table. also, have a field added to the main table called import date that you update with the variable date that was saved.
ME is thinking same as #4


You (6) write an update query that updates the export date in the client table with the variable date value you saved.
ME is thinking the dates and time will remain the server time for uniformity.

You (7) write a query that counts the records in the client table with export date as the criteria.
ME is thinking no need.

You (8) write a query that counts the records in the main table with the import date equal to the variable date.
ME is thinking no need.

You (9) check to make sure the counts equal. if not let the client know they need to talk to you. You might save the imported date so you can use it for recovery.
ME is thinking only check the count on the client vs the count on the main to compare that the # of records are equal. If that is not the case a message pops up to see me and also I get a message related to the issue.

You (10) you should have sufficient information retained for recovery.
ME is thinking there are three locations plus my own removable meadia to fix any errors.

This does'nt have to be too complicated. I just need help with a few issues. Replicated does too much and gets bloated.

 

[cmmrfrds]

The main thing is to have the same date/time stamp in both the client tables and the main table to aid when something goes wrong.

 

[cmmrfrds]

An after thought, I would add the name of the client pc as a field in the main table when you are doing the import.

 

[clickaccess]

cmmrfrds (Programmer),

So do you have any solution for the original question?

Working on a FREE email.
Feel free to sign up.

http://www.SacMail.com