Question Database split. 2 questions

[Robbyp2001]

Good morning folks

I have a database project that is held on an internal network. There are multiple users. Since the design is now finished, I would like to split the database.

When the database opens for the first time each day, a module runs, appending the contents of one table to an archive table and replacing the contents of the first table with fresh data. There are several other queries that run on first opening.

If (Forms!Switchboard![Date of Reg] >= Date) Then
' This code will allow the first user of the database to run the update of new records.
Exit Function
End If
‘After this, the system will bypass the updates thus allowing faster access.

Question 1: if the database is split, what will happen when each user subsequently opens the database?
Hopefully nothing will happen, but I'd like to get an opinion on it first.

Question 2: If the database is split, what is the best way to reconstruct as a stand-alone database (with the current data) sometime in the future if required. I was thinking of creating a blank database, then just importing everything from the back and front ends.

Is this possible or if not what is a better method.

Thanks folks

Rob

 

[DavidAtWork]

1) Where does Forms!Switchboard![Date of Reg] get it's value from, is this a field that is populated on database open from what would the BE tables,
presumably when the updates have run, this date will now be historic ie < today
in which case, YES this should only happend when the first user opens the database

2) You could do as you suggest OR just take a copy of the FE, open it and delete all the linked tables and then use the Import facility to import the live tables from the BE
After the import you just need to check the name of tables are as they were before opening any queries.
David

 

[Mr. B]

Question 1: if the database is split, what will happen when each user subsequently opens the database?
Hopefully nothing will happen, but I'd like to get an opinion on it first.

When you split a database the only thing that you are doing is moving the tables to another file and then creating a link to them. There is no other difference so you should have no problems created by spliting the database.

Question 2: If the database is split, what is the best way to reconstruct as a stand-alone database (with the current data) sometime in the future if required. I was thinking of creating a blank database, then just importing everything from the back and front ends.

If required, yes you could disconnect (delete link) all of the linked tables and then acutllyimport them into a copy of the front-end file, creating a one file versiona again.

Just a note: After splitting the database, be sure that each user has their own copy of the front-end file that is linked to the back-end file in the shared folder on the network.

 

[Robbyp2001]

Dear David and Mr B, many thanks for your prompt replies. I now feel much more confident about this action.

David, Forms!Switchboard![Date of Reg] gets it's value from an underlying table which is populated by an append query. It is set to default date(), so when the records are appended [date of reg] automatically has today's date. Once this append has been completed and [date of reg]=date() then when the next users opens the database, the module cannot run. The action can only run when [date of reg]>=date()

This means that the following day [date of reg] will be <date() thus the action can run, thus records are moved to an archive table and the new records are appended.

It is very important that this action runs only once per day as many of the records in the underlying table will be edited that day, and a record of these edits is kept in the archive table after they are appended.

Thanks again guys

Rob

 

[DavidAtWork]

Rob, are you sure you've got the logic right with your Forms!Switchboard![Date of Reg]
"Once this append has been completed and [date of reg]=date() then when the next users opens the database, the module cannot run. The action can only run when [date of reg]>=date()"
I would have thought only run when [Date of Reg] > Date
David

 

[Robbyp2001]

Hi David, you are correct of course. I t should read >date(). I suppose though that since it happens the next day then it makes no difference. I do take your point however.

I'll look at this.

Thanks

Rob