Runtime vs Split Database

[Pat Hartman]

If you create an install using the developer's tools, the users can do it themselves.

This doesn't say anything about the runtime. It is talking about a distribution mechanism. If you build an install using the package and deployment wizard, it allows you to include the runtime. I would NOT use the runtime if the users already have the correct version of Access installed. If they don't have Access or have an older version, then the runtime is a free way of getting them to the most current version of Access.

How would the info get updated after my entry? Could I run a Macro on close in order for the info to go out to the various users? Would that need to be specified?

The BE is SHARED. Everyone sees your data changes immediately. It is the FE that must be distributed and installed.

If the updates don't have to be up to the minute, then you could distribute a static database that is not split so it is only a single file. The users share nothing but they don't see your data changes until you send them (or post to the FTP site) a new copy. The problem with this it you can never be sure that the users are keeping up with the replacement files and always using the most recent version.

 

[KristenD]

It should be updated daily at the end of the day. This is because our work force changes daily I could potentially in one day have over 200 entries to input depending on transfers, lay offs, new hires, rehires, etc. A job site may be shutting down, A new phase may be starting on another job site. These will be coming in through out the day and it is part of my job along with creating the db to update and enter as well.

No user currently has any version of access installed on their PC and/or Laptop. So it would make sense to use the runtime rather than splitting the database. I could however try both when I test next week with Home office users.

The management team only wants me to have access to the actual tables and inputting. They do not want the PM's to be able to go in and change anything or make any entries. From what I've read it would seem that runtime would be the best option, correct?

 

[Beetle]

So it would make sense to use the runtime rather than splitting the database.

As Pat (as well as possibly others in this thread) mentioned before, these are completely separate issues. Whether or not you use the runtime has absolutely nothing to do with splitting. If your users don't have Access installed on their macines, then you have two choices. Either install Access on every one of those machines (at considerable expense), or use the runtime. The runtime also has absolutely nothing to do with whether or not your users will be able to modify data. That is determined by how you design the front end.

 

[KristenD]

I apologize for the confusion...I have been Googling this all day trying to figure this out.

I think I understand. Whether or not I split the database my users will only have the runtime as we will NOT be installing Access on any of the computers.

I just have to figure out how to create a front end that does not allow any entry and only put the reports and queries on the Switchboard and use the code that was given to me to get rid of the tool bar and navigation pane and figure out how to get rid of the record thing on the bottom of the form.

 

[Beetle]

JMO - but I would have two different front ends. On that you use, which allows you access to everything, and another one for the users. The one for the users would have the Allow Additions/ Allow Edits/ Allow Deletions properties of all the forms set to No, along with having a start form set and all of the shortcut menus/toolbars/navigation pane turned off in the database options. I'm not sure what you mean by "get rid of the record thing", but if you mean the navigation buttons at the bottom of the form, you can easily set that to No in the form properties as well (although if you do that you'll need to provide some method for the users to navigate through the records). I would also disable the Shift Key bypass as an extra precaution. That's pretty common code so just Google it if you want to do that.

If you're using the older (.mdb) database format, then you may be able to achieve some of this (limiting user access to certain things) in one front end via User Level Security, but I couldn't make any specific recommendations about that because I don't use ULS (mostly because I think it sucks - that's a technical term).

 

[KristenD]

The record navigator that is on the bottom of the Switchboard forms.

I am using Access 2007. I should have more information soon on how many computers need access to the front end. From what I read on the office.com website there needs to be an EULA as well attached to this when it is deployed using the wizard?

I was hoping IT would be able to help me but no such luck. I will take a break and start fresh in the morning!

 

[Pat Hartman]

Kristen,
Are you clear yet that the runtime has nothing whatsoever to do with whether or not you split the app?

Why are you giving the users an Access application at all? You could export a spreadsheet and load it to Goodle Docs every day.

 

[Beetle]

You could export a spreadsheet and load it to Goodle Docs every day.

Excellent point. If the users will never be allowed to modify data, then why go through the hassle of managing a distributed database application, especially over a wide network.

 

[RainLover]

Kirsten

You must feel that you have been hit on the head with a Ton of bricks.

Kirsten, if you had an Excel Spreadsheet file you could not open it without Excel being installed on your computer.

The same for Access, you must have Access installed in order to open a File. The Difference is that Microsoft decided to make available a Free Cut down version of Access called Runtime. This Runtime version allows the user to enter Data and Produce Reports.
It won't allow the user to be able to Design new Reports or Create Forms etc.

Runtime allows you to distribute your completed Database for free.

The other Subject is Splitting a Database. Access is a multi user product. But there are limitations in that you must store the Data in one place only. This is called the Back End. The Back End only has Tables. No Queries, Forms, Reports etc.

The Front end is your GUI. It has all the pretty stuff Like Forms Reports etc. The Front end links to the Tables in the back End.

Every user MUST have their own copy of the Front end on their own machine. Usually on the C:\Drive. You can have dozens of Front ends but only one Back End.

If you make changes to your Database, usually this is done on the Front End. You will need to find a way to force an update on all users machines so that they are using the latest version.

Any changes done to the back End are done in the one place on your server and therefore does not require updating for each user. BTW back End changes need to be done with all users Logged Out. This is a problem we can look at later.

I suggested much earlier in the thread that the Front End for Data Entry could be Different to the Front end for Reports. You could distribute a different Version to Head office as opposed to users on site. This suggested has again been mentioned by Beetle in post # 25

Another thing you must do is to lock down the Database so as to prevent naughty people doing things that they should not be doing.

You also need to lock down the Back End and only allow access through the front end.

Perhaps you should consider talking to the boss and ask for an extension. Show him what has been said here. It might add some weight.

Kirsten I think you a very lucky as you have some of our best here helping you.

 

[KristenD]

I do very feel very lucky to have all of your expert advice here. I appreciate so much! You all are amazing. This last part of developing the database certainly has my head spinning to say the least.

I do understand that runtime and splitting have nothing to do with each other.

I am going to talk to my boss today and let him know that I will be needing extra time to play around and figure this out as I will be trying to figure out how to create the two front ends.

The reason they want to give them the Access application is there are so many reports that would be run by the users. I have about 30 reports right now and more could be added as the application gets used.

Thank you all again for your amazing knowledge. This website has been a life saver for me!

 

[KristenD]

One quick question...How would I force an update to all the users? I know I would have to make sure no one is in the db correct? Is this done through code and then pushed out to the users when they open up the db?

Thanks again for your help!

 

[RainLover]

I would write a VB Script that Opens the Database, but first it checks to see if it is the latest. If not Copy the new version over, if it is the latest then just open.

The trouble is I have not written VB Script in ages. Might need some help.

 

[KristenD]

I have downloaded a few Access and VB books to my tablet that I have been perusing to help me with code.

I am very excited to get this up and running. I think I just got overwhelmed yesterday.

 

[Beetle]

How would I force an update to all the users?

You might have a look at Bob Larson's free Auto Update tool here.

 

[KristenD]

Awesome thank you!!

I just got an added bonus as this is only going out to 3 users now. Two in the home office and one in the regional office in Baltimore. Whew!

 

[RainLover]

You might have a look at Bob Larson's free Auto Update tool here.

Sean

If I remember correctly, doesn't Bob's tool require the user to do some action.

In other words it is not fully automatic.

 

[Beetle]

As far as the end users go, it only requires that they click OK to a prompt when they open the application and a new version is available. It stores a version number in the master copy of the FE as well as the BE. Each time a user opens their copy of the FE it checks the version number. If the version number isn't current the user gets a message. When they click OK the db is closed, the old copy deleted, new copy downloaded and reopened. It does require that the db administrator update the version number in both the master FE and the BE when they make a design change.

 

[Beetle]

There is also Tony Toews AutoFe update tool here, which is more advanced, but not free.

 

[KristenD]

Thank you very much!! I will probably end up using the free version and just do some training with the users.

 

[RainLover]

I would write a VB Script that Opens the Database, but first it checks to see if it is the latest. If not Copy the new version over, if it is the latest then just open.

The trouble is I have not written VB Script in ages. Might need some help.

Below is some VB Script that will update without the user ever knowing.

Copy and Past into Notepad and rename it with *.VBS

Rem ________________________________________________________________
Rem Written by Des Wells AKA Rainlover and ChrisO AKA ChrisO
 
Rem Create a .txt File and Name it Token.txt. Place this in the Source Directory.
Rem The Messages can be deleted.
Rem DestinationFile and SourceFile need to be changed to suit your own requirements.
Rem To force an update, modify the 'SourceToken' file in the 'SourceDirectory' directory.
Rem Place a Shortcut to this file on the User's Desk Top to open the Database.
Rem ________________________________________________________________
 
SourceDirectory = "D:\UpdateScript\"
DestinationDirectory = "D:\Update\"
SourceFile = "MyDB.mdb"
SourceToken = "Token.txt"
DestinationFile = "MyDB.mdb"
DestinationToken = "Token.txt"
 
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(DestinationDirectory & DestinationToken) And FSO.FileExists(DestinationDirectory & DestinationFile) Then
    If FSO.GetFile(SourceDirectory & SourceToken).DateLastModified > FSO.GetFile(DestinationDirectory & DestinationToken).DateLastModified Then
        MsgBox "File Needs Updating."
 
        If FSO.FileExists(DestinationDirectory & DestinationToken) Then FSO.DeleteFile DestinationDirectory & DestinationToken
        If FSO.FileExists(DestinationDirectory & DestinationFile) Then FSO.DeleteFile DestinationDirectory & DestinationFile
 
        FSO.CopyFile SourceDirectory & SourceToken, DestinationDirectory & DestinationToken
        FSO.CopyFile SourceDirectory & SourceFile, DestinationDirectory & DestinationFile
    Else
        MsgBox "Files are in Sync."
    End If
Else
    MsgBox "Files need Installing."
    FSO.CopyFile SourceDirectory & SourceToken, DestinationDirectory & DestinationToken
    FSO.CopyFile SourceDirectory & SourceFile, DestinationDirectory & DestinationFile
End If
If FSO.FileExists(DestinationDirectory & DestinationFile) Then
    CreateObject("WScript.Shell").Run DestinationDirectory & DestinationFile
Else
    MsgBox DestinationDirectory & DestinationFile & "    does not exits."
End If