Multiple User DAO Access to Database

[MagiCat]

I have a table in Access with approximately 14000 records, each of which need to be uploaded into a mainframe system using Reflection. I currently have everything in Reflection to pull the data sequentially from Access, then mark a given record that it's done.

What I'm wondering is how would I go about making it so that 2 or 10 or 50 computers could use the same database (it's on a shared drive) so that each of them can take part in uploading the data.

I'm guessing that I would have so lock records as they're being loaded and then marking them as finished after they are loaded. I'm just not sure how to make sure there are no duplicate records uploaded.

Any ideas?

 

[VicRauch]

I would think that if you know how many computers you will have to do the work, you can divide the table into that many pieces and have each comuter do it's own piece. That way you don't have to worry about locking, and two machines somehow locking the same record. I know that is not supposed to happen, but I don't trust Access enough to try that approach. But, if you assign machine one records 1 through 1000, machine two records 1001 through 2000, (assuming you have 14 machines). Then each machine could open it's own recordset, move to it's starting record number, and start processing. Each machine would have to open another shared table that would have the instructions for each machine. Instructions being which records (start and end) to process.
HTH,

 

[MagiCat]

The tough part is that this will be a stop and start process, the idea being that a given employee can just open up the Reflection Session that I've created and it'll just start uploading stuff until the employee breaks out of it. We're talking about 10 seconds per record for processing so this means that we need to get as many computers that we can on this as we can.

Unfortunately we can't just run it at night or on a weekend because the system has downtime.

 

[VicRauch]

I would suggest new fields in this table.
DateTimeProcessStarted and DateTimeProcessSucceeded.
Create a query over this table that only includes those records where the DateTimeProcessStarted is < Jan 1, 2000. Then your program can read this query as an updatable Recordset, as it reads the record, check to be sure this DateTime field is still < Jan 1, 2000. If so, update the DateTimeProcessStarted with the Now() function, then write the record back to disk. If it is not less than Jan 1, 2000, read another record and process it. At the end of the 10 second process time, update the DateTimeProcessSucceeded with the function Now() and rewrite the record to disk. You will also need a process that will look for records with a DateTimeProcessStarted that is older than 1 or 2 minutes and an unupdated DateTimeProcessSuccessed.
Hope this helps.

 

[MagiCat]

What happens if somewhat else updates the record after the second read? Could 2 computers could get the same record at the same time doing this unless I'm missing something.

 

[VicRauch]

I tried to explain that about two computers reading the same record. That is the idea behind checking the DateTimeProcessStarted field to see if another computer has already started processing this record.