Multiple User DAO Access to Database

MagiCat

Board Regular
Joined
May 24, 2006
Messages
121
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
May 24, 2006
Messages
121
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
May 24, 2006
Messages
121
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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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.
 

Forum statistics

Threads
1,136,418
Messages
5,675,718
Members
419,584
Latest member
mrh8rr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top