Database almost complete, what about maintenance? (1 Viewer)

[christakis]

Hi guys,

With your help I have reached the final stages of my database design. The database is now aprox 6MB. Around 15 users will be using it (rarely at the same time) The database is in one mdb file and stored on a shared drive. Do you think its worth splitting it into a front/back end or not? I also want to auto compact it but I hear its not safe to do this with one mdb file.

Cheers,
Christakis

 

[boblarson]

Hi guys,

With your help I have reached the final stages of my database design. The database is now aprox 6MB. Around 15 users will be using it (rarely at the same time) The database is in one mdb file and stored on a shared drive. Do you think its worth splitting it into a front/back end or not? I also want to auto compact it but I hear its not safe to do this with one mdb file.

Cheers,
Christakis

1. 6Mb is not a big file.

2. You should split it regardless of size or anything, if you have multiple users (I would argue you should split it even if it is only ONE user as it is safer for the data). If you don't split it, you will be playing Russian Roulette with your database. See here for more about splitting databases.

3. Each user should have a copy of the frontend. Compacting the backend will only be possible when all users are out, but each user having their own frontend further reduces the chance of corruption AND if one user's FE corrupts it doesn't bring down the whole house. Your others can still work.

4. Plus having the separate FE you can have a master which you can do work in and then deploy to users (you could even use my free auto update enabling tool to enable auto updating for your frontends - see my signature).

 

[christakis]

Aha! Thank you for your reply. I will read more on how to split the database properly first and then try and use your auto-update tool.

I really appreciate your help.

Christakis

 

[christakis]

Right after reading the material you gave me, here are a few more questions.

1.) Is there a free installer tool i can use to create a nice simple installer for the mbe file?

2.) Can I password protect the database but ONLY ask for a password if you are trying to edit it (Opening it with shift)? I currently don't have a password and I doubt anyone will edit it but JUST IN CASE

3.) Your free tool is sweet but I need to play with it a bit more.

Cheers,
Christakis

 

[boblarson]

Right after reading the material you gave me, here are a few more questions.

1.) Is there a free installer tool i can use to create a nice simple installer for the mbe file?

You really don't need to install it, but if you want it in a specific folder, you can use UltimateZip to create a self-extracting zip file which can put it where you want.

2.) Can I password protect the database but ONLY ask for a password if you are trying to edit it (Opening it with shift)? I currently don't have a password and I doubt anyone will edit it but JUST IN CASE

Not that I know of. But you probably want to create an MDE out of it and use the Disable Bypass key code (do a search here or on Google) and then they can't get in anyway.

3.) Your free tool is sweet but I need to play with it a bit more.

Let me know if you have questions (or post them to my support forum on my site). I have quite a few places using it, including several municipalities, the U.S. Marine Corps, BAE Systems, and others.

 

[christakis]

Alright, thanks for your fast reply. The reason I am asking for the installer tool is that I will put the front end on my companys intranet. It is a lot easier for users to download an installer than following a manual installation procedure from the wiki page.

Cheers,
Christakis

 

[boblarson]

Alright, thanks for your fast reply. The reason I am asking for the installer tool is that I will put the front end on my companys intranet. It is a lot easier for users to download an installer than following a manual installation procedure from the wiki page.

Cheers,
Christakis

The other thing you could do is to just have a batch file that they run (if your network settings will allow that). Or have them download that batch file and run it. Then it could create the folder and then download the frontend to the correct location.

 

[christakis]

I will look into that but I doubt the network will allow it. Thanks, Chris

 

[boblarson]

I will look into that but I doubt the network will allow it. Thanks, Chris

I know - so downloading a batch file and then running it is probably the better option. But, if you can't do that even then if you have WinZip, I believe it had the capability to do self-extracting zip files and if not then I know that UltimateZip does (I've used that several times and I like its interface and all for creating them). But if you do use UltimateZip then make sure that the file you are starting with is in your C:\ root directory so when you specify which folder to go to (like C:\Programs\Databases\) it will actually extract properly (I found that out the hard way).

 

[AJordan]

I've never thought about this for the back end. I usually just provide detailed instructions, stating that after clicking Extract All files, input "C:\".

Then again I am using Windows Vista and 7. What are the pro's and con's of using something like UltimateZip vs the manual method I mentioned above? I also assume If someone was using Windows XP they could use WinZip for self extraction?

Anyway, again what are the pro's and con's of using Windows to create a compressed folder and having the user "Extract All Files" to a specified location? I guess there might be a different answer for each OS.

 

[boblarson]

The benefit of creating a self-extracting file with, say UltimateZip, is that you can specify the folder and then you don't have to rely on the user doing it. Of course there really is no reason that the file has to be anywhere standard unless you have some sort of functionality in the database that requires it to be in a single spot. My auto update code doesn't need each user's frontend to be in a specific spot. It just finds where it is currently at and then copies the new file to the location where the old file was. So I can have mine in C:\Temp\ and you could have yours in D:\Databases\ or something like that and it would be fine.

 

[christakis]

I have Winzip. I will try and create a self extracting archive with that. To be honest I am more worried about finishing everything by Friday now that I will be creating the FE,BE etc... I also still need to populate some of the tables and Access is a retard when it comes to importing my Excel files. Thankfully csv does the job (Most of the times).

Chris

 

[christakis]

Hi Bob,

I've been working on what you gave me. I managed to split the database properly, put the back end on the remote server and pluged in your code to autoupdate the front end. It works nicely, however there is one thing that worrys me.

When I open the master FE .mdb the code runs and tries to autoupdate the .mdb file. If the master .mdb is not the latest version (or even not there at all) the database will overwrite (or delete) the .mdb file you just opened. Say someone downloads a copy of the master .mdb, edits it, saves it and closes it down. Next time he forgets to open it in edit mode. He will loose all the work he did.

I know I can avoid this by telling the script to only run for .mde files. I will do that with a simple if statement. However, what do i use to identify that the file is a .mdb instead of .mde?

i tried a quick test in my form load event,

Private Sub Form_Load()
Me.LoginID = GetUser()
Call SetAdminPermissions(Me)
'Call checkVersion
Dim format As String
format = CurrentProject.FileFormat
format = CurrentProject.ProjectType
End Sub

CurrentProject.FileFormat = 10 and CurrentProject.ProjectType = 2. Is any of the two what I am after?

Cheers,
Christakis

 

[boblarson]

I could have sworn that I have code in there to not run the update code if the master file is opened from the location that is listed in the master file location (in the table).

I don't know about it looking for what project type it is.

 

[christakis]

I could have sworn that I have code in there to not run the update code if the master file is opened from the location that is listed in the master file location (in the table).

I don't know about it looking for what project type it is.

Because the master is stored at the remote location, it is unlikely that someone would edit the FE remotely. People would download a copy and edit it locally as its faster. I have sort of "fixed" the problem by documenting the updating process excesively. If the user edits the master and then change the version on both FE and BE at the same time and then replace the old master with the new one it should be ok.

What I was doing was update the FE .mdb, change the FE version (but not the BE version). Then compact and repair before creating the .mbe. That closed the master .mdb, if I then forgot to open it in edit mode, the update code executed which deleted the new .mdb and loosing all your changes. To add insult to the injury my home drive is an other remote drive which meant that the file was deleted for good. Its a stupid mistake but costly nonetheless and should be adressed to. In my opinion, people should only deploy .mde front ends hence the script should only run for .mde front ends.

I dont know if the version I download is the most recent one. I noticed that you put a master location textbox in your code wizard but it isnt saved in tbl-version_master_location

Cheers,
Chris

 

[irish634]

1.) Is there a free installer tool i can use to create a nice simple installer for the mbe file?

Yes there is. Check this out. The downfall is many companies require IT support to run installers. They usually don't allow users to install anything. I like manually deploying my FE apps to each user, then using Bob's update utility.

I could have sworn that I have code in there to not run the update code if the master file is opened from the location that is listed in the master file location (in the table).

That's in the version that I have. The problem I have with it (I also emailed you about) is that my FE "Master" is an MDE file. My Development file is of course an MDB file located in a different location. The two files are also named differently (FE_Master and FE_DevMaster). This is a small issue. The update code would run no matter what on my development master mdb file. The code would also run if I put the DevMaster in the master folder, because I have different file names.

I corrected this by adding a few lines to check the file extension. If I am working with an MDB file, the update code skips. If it's an MDE file, it runs.

I simply do this:

    'FE Update
    If Right(CurrentProject.Name, 3) = "mdb" Then
    Else
        FrontEnd_Updater
    End If

Hope this helps
Craig

 

[DCrake]

Irish

I have looked a tthe link you posted about the installer. Have you used it yourself? and if so, how have you found it?

At present I deal alot with VB6 and use the package and deployment wizard. This is fine but it has some pit falls.

Does this system handle the registering of OCX's during installation?

David

 

[christakis]

I simply do this:

    'FE Update
    If Right(CurrentProject.Name, 3) = "mdb" Then
    Else
        FrontEnd_Updater
    End If

Hope this helps
Craig

Yes this is exactly the same as the code I used eventually

here is the on load event of my main menu

Private Sub Form_Load()
'Check if a newer version exists
If Right(CurrentProject.name, 3) = "mdb" Then
Else
    Call checkVersion
End If
'Get user ID and hide/show buttons based on users
'access level (Through SetAdminPermissions function - located in CustomFunctions Module)
Me.LoginID = GetUser()
Call SetAdminPermissions(Me)
End Sub

 

[irish634]

David,

As far as the installer, I have only used it messing around and it was extremely easy to use. I also believe it will do what you ask for OCX. See here.

Craig

 

[christakis]

Regarding the installer, its nice and neat. I also tried this
http://download.cnet.com/Install-Creator/3000-2383_4-10218346.html or
http://www.gold-software.com/download1312.html

Unfortunately it is company policy that we do not create installers for our software.