logging people off when running a macro

[Xoanon123]

I work for a direct marketing company and we receive about 1000 - 2000 orders daily, the orders come in the form of 2 tables that I have to append to the main DB and then run a macro which includes a few more queries.

Out of habit I get all my users off the DB to append it, but as the DB grows this macro can run for more than 30 minutes (in the meantime the users drink coffee and plot my assassination). I was curious to know if I can let them stay in the system while the macro runs. This would cut down on overhead around the office.

 

[SgtJarrow]

Could you now have this process run after hours??? Using the task manager on some PC, you could set the database to open at say midnight (or whenever), while no one is there working (or at least should limit the impact), do the necessary updates, then close itself down again.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[Xoanon123]

Unfortunately not, appending the orders coming in needs to be done during the day in order for the shipping department to have access to them and be able to print labels.

I also run a backup at night and need Access closed in order to let the tape drive copy it.

 

[SgtJarrow]

Well, using an open source manager for your backup will allow you to backup the file even if you are using at night....

Aside from that, if you must run it during the day, if you are appending from two tables into a third, and the users are not adding records to this third table, you should be okay...

When accees begins its append process, it creates a recordset of the records to be appended. This is usually a snapshot type recordset. So once it has created this set, it is actually done with the source table. Then it opens a recordset to the target table...now this is a dynaset type recordset, because it will be adding records and needs to be editable. As it is working on putting these new records in the table, no other actions should be performed on the target table....you may corrupt data.

So if your process is:

Append Table 1 to Table 3
Append Table 2 to Table 3

Then as long as the users do not have access to table 3 during the process, you should be fine. They can even still be working in Tables 1 and 2 and it shouldn't matter...I think you should be fine.

But as always, I would make a BACKUP and test it before going live with this process..... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[Xoanon123]

Robert,

Thanks, my users do in fact need to work on table 3 (being the target table), so I'm not too sure how practical it would be for them to have access yet not be able to create new records....I don't trust them to remember not to add new records while I update...perhaps it's just better to leg them off. thanks!