Question about table partitioning

[nadergirl08]

Hello,



I am building a db in Access 2010 to replace a payroll application. There are several tables that contain employee information, job information, etc. which are all referenced by the payroll table. Employees enter their information daily via a form and can change any of their information throughout the week. Once a week the hr person will run a report to finalize the payroll information. What I need to do is find a way for the hr person to lock all employees out of the previous records once payroll is committed so nothing can be changed after the week is up. I have been looking for a way to partition the payroll table so that older data can be easily moved to a locked partition but I am not having any luck finding information. Are partitions possible in Access? Any ideas of how I can make this work?

Thanks,
Nikki

[pbaldy]

Partitions are not possible that I'm aware of. One method that comes to mind is using a date to lock/unlock records. Your payroll process would update a date field somewhere. Then in your form's current event you set the AllowEdits/AllowDeletions/AllowAdditions properties of the form to True or False as appropriate to the date.

[nadergirl08]

Partitions are not possible that I'm aware of. One method that comes to mind is using a date to lock/unlock records. Your payroll process would update a date field somewhere. Then in your form's current event you set the AllowEdits/AllowDeletions/AllowAdditions properties of the form to True or False as appropriate to the date.

Using the date to lock records is a good idea. Thank you. The problem is that I need to make this as easy and user friendly as possible. I will not be the one entering the information and the person who will be wants to be able to print a report and click a button and -viola- the records are no longer able to be changed but can be viewed and queried. Any suggestions how to accomplish this?

[pbaldy]

Presumably the user is entering a date range for the payroll, so you would take the end date and update your table field to that date. One way in VBA:

CurrentDb.Execute "UPDATE TableName SET DateFieldName = #" & Me.EndDateTextbox & "#"

[Paul H]

Here's what I used to do. I don't know if this is good design or not, but I would have two tables, one for current data and one for archived data. Once a week you could archive the weekly data in to table 2, the delete it from table 1.

Here's a second idea. Put a Yes/No field called Locked in your table and use that to deny access to the employees. Would that work Paul?

[pbaldy]

As a rule I would not archive data into different tables. Leaving it in one lets you query it any way you may need (current, old or both type of thing). It avoids having to move it back when the user makes a mistake, etc. Like every "rule" it can be broken, and I've done it, but again as a rule I wouldn't.

The "Locked" field would certainly work, but in my mind it's as much or more work than the date field method. In the date method you update a single record in a table and use that to conditionally lock/unlock a form (comparing the date in the table to the date of the record). In your method, you need to update all the records (probably using a date field) and then conditionally lock/unlock a form based on that field. Unless I'm missing something, it's essentially the same method but using a new field in the table instead of an already existing (I assume) date field. I guess it's "do I update a single record in a new table or all records of an existing table?" I think either would work, so I suppose it's a personal preference type of thing. I'd still use the date method, but I wouldn't argue with anybody who chose the locked field method.

[nadergirl08]

Thank you very much for your responses. I have 2 options to consider and thats a good thing. I'm a bit more familiar with SQL... What if I wanted to make a SQL db so that I could have updating partitions. How hard is it to make the forms, macros, etc that I've already made be the user interface? How hard would that be?

[pbaldy]

Offhand, I don't see how it could be incorporated, but I haven't used that feature of SQL Server. Perhaps somebody that has will chime in.

[nadergirl08]

Presumably the user is entering a date range for the payroll, so you would take the end date and update your table field to that date. One way in VBA:

CurrentDb.Execute "UPDATE TableName SET DateFieldName = #" & Me.EndDateTextbox & "#"

I am not very familiar with VB either. :/ (Forgive me, I'm learning SQL from the ground up at the moment for the admin cert exam and I have very limited experience with anything else, but I am certified in Access 2003, I just never really used it until now). So my db is called HealthDeptPyrl.accdb, the table in question is the Payroll table. It contains 7 fields and one is absolutely a date field. To try to utilize this I'd open Visual Basic for Applications and type:

HealthDeptPyrl.accdb.EXECUTE "Update PAYROLL SET DATE =

And then what? I'm sorry, I'm so confused. By the way, I'm also very grateful that this forum exists.

[pbaldy]

For starters, I meant you'd have another table with the date to be locked in it. That would be that field's only purpose, to store the "locked" date. Then in your form's current event you'd compare that date field to the date field in your Payroll table for the current record, and lock/unlock the record as appropriate. The first part was actually fine like it was; "CurrentDb" is a way of referring to the current database. The code to update that date field would look exactly like I posted earlier, replacing the table/field/control names:

CurrentDb.Execute "UPDATE TableName SET DateFieldName = #" & Me.EndDateTextbox & "#"

[nadergirl08]

Thank you. I'm going to let them deal with it not being locked and if there is a complaint I'll explore this further. Thanks for all your help.