Runtime vs Split Database

[KristenD]

I am trying to decide whether to go with Access Runtime or split my database once it is ready for to go live.

I will be the only one doing the entry into the database. The users will accessing reports and queries that I have created. I do not want nor do they want any access to the tables or the forms used for entry. The only form that they should see would be the Switchboard forms which I have created. I don't want them to be able to see the navigation pane on the left side nor have any access to the Ribbon toolbar above. But the front end should be updated by the back end.

Now, I have done several trial and error runs and have accomplished nothing but locking myself out of several back databases and creating a split database where the back end has no form that would allow me to enter the information needed. There should be NO entry on the front end, they want that locked down and as secure and read only as possible.

Also as of right now the database is housed on my PC, not on a network location. I believe the front end will end up on our FTP site, to allow all of my users access. And I really don't think my company wants me to travel all over the eastern seaboard to install everything locally and have to send updates.

What are your suggestions? I need this to be able to go live in the next few weeks so I can train the users.

 

[spikepl]

Your post is unclear.

But the front end should be updated by the back end

Frontend on FTP ? Meaning what ? That everyone can DL and the run it locally? Why not the whole db?

A backend in the context of Access holds data only and nothing else. A frontend holds the "everything else".

Since no one else is going to be updating anything, then each user can get his own unsplit db as runtime.
Send them a new copy when you have updated it or put it on the FTP server and issue an update notice. The end.

 

[KristenD]

Frontend on FTP ? Meaning what ? That everyone can DL and the run it locally? Why not the whole db?

QUOTE]

Yes, the front end would be accessible to all of the users. We are a large construction company that has jobsites all over the eastern seaboard of the US. I will have many users accessing the db. I apologize if what I'm asking is unclear. This is the first time I have ever developeda db from scratch and deployed it. I have until the end of June and I have been working on it since the end of March. I want to make sure that I am putting out the correct file but was told not to let the users have access to the tables or be able to change any info.

 

[Galaxiom]

I would still split the database. Separation of data and program is a fundamental principle of good application design.

Continued deveopment of unsplit databases is not as easily managed as when they are split.

 

[Pat Hartman]

I am trying to decide whether to go with Access Runtime or split my database once it is ready for to go live.

This statement makes about as much sense as "I am trying to decide whether to go with the steak or move my checking acount to a new bank." One simply has nothing to do with the other.

The Access runtime engine is a crippled version of MSAccess.exe that can be used to "run" existing applications but cannot be used to create new ones or modify objects in existing ones. If your app is being run on the user's PC with the runtime engine, the only thing users will be able to do is add/change/delete data using the forms you created. If your forms do not allow them to modify data, then they won't be able to. However, this is no protection for the data because if the user has the full retail version of Access installed, he can simply use that to open the BE and do whatever he wants unless you have made some effort to secure objects.

If you want to protect the data, the only real solution is to use SQL Server or some other RDBMS that provides security.

Any Access application that is used by more than one person should be split into an FE and BE for stability. The BE (back-end) contains ONLY tables. The FE(front-end) contains everything else. The BE is placed on a file server that everyone has access to. The FE is installed locally on each client PC. The FE is NOT shared. Only the BE is shared.

If you want to use the FTP server to distribute the FE, that will be fine. The users can download new versions as needed. I don't think you can use the FTP server to share the BE though. The users will need read/write/delete access to the FTP directory because Access will need to create its lock file there when someone opens the BE. If Access can't create the lock file when it opens, it opens in exclusive mode which locks everyone else out.

You can download the developer tools (I'm not sure what they are called these days and you didn't specify what version you were using), from the MS download site. This is an addin for the appropriate version of Access and allows you to create installation files that the users can run that will install the runtime as well as your app plus make the appropriate registry entries to get past security for A2007 and A2010. SageKey is another option if you have to run applications of different Access versions on the same computer.

I'm not sure what you were doing that locked you out of the database. Splitting doesn't do that. Are you trying to implement security using ULS?

 

[RainLover]

Kirsten

What is your problem. You have given much information but have not described what your real issue is.

My main concern after reading your post is knowing how the users can connect to the Same Back End. Given that your sites are spread over distance and I am not sure that you have all sites networked.

Just one thought I could throw into the discussion is that you can have Two Front End designs both linked to the same back end. One to run reports and the other just for you to enter/update Data.

To update the sites would be a rare occassion with regard to the front end but the Back End where that data is stored needs updating constantly.

Perhaps something like Dropbox could solve the problem if there is no network.

Look forward to hearing from you.

 

[KristenD]

The outline of what I was given for the database is to allow users to be able to run reports and/or queries with the data that I input into the database. They do not want the users to be able to add/update/edit/delete the data in the db. I need to be able to create a front end that opens to the MainMenu Switchboard form which I have figured out how to do. But my problem is the ribbon up top is still there along with the navigation pane. I don't want either of these to show.

Basically I just need the front end to open the switchboard form with nothing else. Each time I have done this with my back ups I get the ribbon up top (even though it only shows the home tab) and the navigation pane where the users are still able to get into the forms to be able to manipulate the data.

I do want the very rundown basic crippled MSAccess for those users since all they will be needing are the reports that I have created.

I hope this information helps!
Thank you again for all your wonderful advice!

 

[KristenD]

I forgot to mention all of our sites are networked over our FTP server. As of right now the database is not on there just on my PC. What the mgmt team would is for the users to be able to go to the FTP site and run it from there so I don't have to go all over to install it on the Project Managers and Superintendents laptops/PCs. It would take me forever to get that done and ALOT of traveling.

 

[RainLover]

I would prefer to do the travel, but that is a bit cosly.

Leave it with me and I will find and test some code for you.

Back asap.

 

[KristenD]

I would not mind the travel either but I think I would be gone for the whole month of June between all the job sites! And since some of our job sites are prisons, I really don't want to step foot on site there haha!

Of course I wouldn't mind so much the Air Force Base, FBI or Langley that would be pretty neat.

Thank you so much!

 

[RainLover]

Public Function HideAll(ByRef frm As Form)
    Dim cmdBar As Object
     CurrentDb.Execute "Delete * From tblHiddenCommandBars"
    
     With CurrentDb.OpenRecordset("tblHiddenCommandBars")
        For Each cmdBar In CommandBars
            If cmdBar.Visible Then
                DoCmd.ShowToolbar cmdBar.Name, acToolbarNo
                .AddNew
                !BarName = cmdBar.Name
                .Update
            End If
        Next cmdBar
     End With
 
    If (frm.Modal = True) Then                                      ' If the form is set to modal.
            frm.Modal = False                                   ' Disable modal.
            DoCmd.NavigateTo "acNavigationCategoryObjectType"   ' Select Navigation Pane.
            DoCmd.RunCommand acCmdWindowHide                    ' Hide selected.
            frm.Modal = True                                    ' Enable modal.
        Else
            DoCmd.NavigateTo "acNavigationCategoryObjectType"   ' Select Navigation Pane.
            DoCmd.RunCommand acCmdWindowHide                    ' Hide selected.
    End If
End Function
 
Public Function ShowAll(ByRef frm As Form)
 
    With CurrentDb.OpenRecordset("tblHiddenCommandBars")
        Do Until .EOF
            DoCmd.ShowToolbar !BarName, acToolbarYes
            .MoveNext
        Loop
    End With
End Function

This might help.

You have one Function to Hide and another to Show.

Place it in the Open Event of the First Form that you open or if you prefer in an Autoexec.

 

[RainLover]

I forgot something.

You need a Table with the Command Bars listed if you want to restore them.

tblHiddenCommandBars
BarName as Text

 

[RainLover]

If you change your mind about that trip and need someone to carry your bags let me know.

Just give me a week's notice to get a Visa etc.

Would love to see New York

 

[KristenD]

Awesome!! Thank you!! I can't say thank you enough. This website is amazing and has helped me tremendously getting this db created and up and running. I may not have created the prettiest database but it is definitely functional and does what it was designed to do. I really appreciate all the expert advice and help everyone has given me.

Surprisingly enough, I really enjoyed creating it. It was frustrating at times but definitely rewarding seeing an almost finished product.

 

[KristenD]

Haha ok! The more I think about it I think I may want to travel!!

 

[RainLover]

an almost finished product.

ROTFLMAO

A Database is never finished.

One day it may die and you will need to create a new one but until then they will always be asking for improvements.

You may have a job for life.

Programming is:
Phase 1
Design
Coding
Testing, Testing, Testing
Find what does not work
Phase 2
More Design
More Coding
More Testing, Testing, Testing
Find what does not work now
repeat Phase 2 until you do not find any more issues.
Phase 3
Give the app to the end users
End users find what does not work now
Go back to Phase 2
Phase 4
Wait for the end users to have issues or the requirement have changed
or
Developer/programmer learns how to do things better
Go back to Phase 2
Phase 5
Developer/programmer learns how to do things better and the original design will no longer meet the needs of the users.
Time scrap the old app and create a new one and convert the data.
Back to Phase 1.

I have found that a database app never really is finished until it is no longer used at all.

 

[KristenD]

LMAO!!! Clearly I'm new to the field!! Well then I will have to keep learning and honing my newly found skills and stay employed.

I do know that I will have two additional databases to create once this one is complete but they are fairly simple ones.

I just printed that out and put it up on my bulletin board

 

[gemma-the-husky]

Kristen

and when RainLover says "Design" as step 1, what he also means is "Analysis"

Analyse the sytem carefully. Ask questions. Do not trust what the users say. Make sure you understand what their system does. Ignore anything users say about how they expect the implementation of the system to work. That's your job.

Underneath everything, they will have either
a) assumptions they take for granted, but which you do not know about
b) little things out of the ordinary that occasionally happen

It isn't the 99.9% of normal stuff that is the real problem. It is the 0.1% percent of unusual stuff

if you don't find out about these, and design around them and plan for them, you have problems when the users at your first demo, - after 2 weeks working on it - say "what about such and such"

 

[Pat Hartman]

so I don't have to go all over to install it on the Project Managers and Superintendents laptops/PCs. It would take me forever to get that done and ALOT of traveling.

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

Kristen,
Sharing the FE is a bad idea. Each user should have his own personal copy of the FE and it should run from his local PC rather than across a network. That means that you need a way to install it on each PC if you don't think the users are capable of downloading a file and then running it. The FTP may be used to share the BE.

 

[KristenD]

I am testing right now using the guidelines set forth by the executive team. Hopefully the testing will be completed in the next 2 weeks as the db is to go live at our 2nd qtr management meeting.

I do plan on having my boss use it on my test database before implementing the live one.

Pat,
Your recommendation is to use the Access Runtime? 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?

I will probably end up having to install it on the user's computers as I know they will not do it themselves. If they can't go out to the FTP website and pull down the db they just won't use it.