Splitting up data and code.... In MS Access 2007

Posted on 2011-10-30
Last Modified: 2012-05-12
Hey, I have got (hopefully not a stupid question...)
I would like to split my table to have code in one file and my data in another. and have BOTH on the net.
All of the references I see to front/back end state the the application is run from the local desktop.

I would LIKE to keep the code on the server and have it run from there. The reason is just for maintaining the code.
I want to have the data separate becuase that way, I can update my code, with out having people log out. I use a link to the application, and between version, I just change the link to point to the new version.
The way it is right now, I have to kick everybody off, copy the application, then copy all of the data from the old to update the new.
the way I would like to run it, when I put in the new versions, as people log out of the old, and then the next time they login, it would just load the new code, no updating of the data tables.
Then when I see the lock remove from the previous version, I can then move THAT code offline.

Is this possible?
Are there any "gotchas" I need to worry about? I realize speed wont be quite as good and a local app and shared data, but that to me seems like a nightmare to maintain, especially when you have people across the country, and many are noobs

Again, I have tried searching, but I only fing references to local/remote setups.

Question by:Bruj
    LVL 44

    Assisted Solution

    You can have anyone run any function on your local machine by having the anyone create a Reference to your mdb in his local machine.  This works!
    LVL 44

    Expert Comment

    Note:  this has to be a function.  However, we all know how easy it is to change a Sub to a Function.
    LVL 77

    Expert Comment

    "....and have BOTH on the net...."

    what exactly do you mean by this?

    You cannot run an Access application across the internet.
    LVL 84

    Accepted Solution

    Quite honestly, if you're supporting people "across the country" then you should either (a) be using Terminal Server with your Access application or (b) you should not be using Access as your frontend and should instead be using a web-based language like ASP.NET, PHP, etc etc. Access cannot operate in the way you describe, since the Access application requires Access to run.

    You could setup a system where your users would "pull down" a new copy of your Access application each time they ran it - sort of like an automated weblink that would (a) connect to a server and (b) copy the latest version of the Access system to the local desktop and (c) launch than new copy. This could work, so long as you're comfortable with the many variables that could cause things to go awry - for example, failed downloads, partial downloads, corrupt downloads, failed connections to the server ... the list goes on and on.

    Access can use FTP to do this, so that would sort our step (a):

    Since you could run this from inside an Access app, you could then fire off the newly copied version from inside your "launcher" app, then tranfer control to the newly launched app, and close down your launcher app.

    As to keeping BOTH on the net - you cannot remotely connect to an Access database, so you can't do that. You could move your data to a different platform that supports remote connections (like SQL Server, Oracle, MySQL, etc), but you cannot connect to an Access database unless that database is on the same network as your local machine (and that is highly unlikely with web-based Access databases).
    LVL 10

    Assisted Solution

    Keeping both copies on the network may be convenient for you, but is not optimal for your user.

    For optimal performance and to let each user have private tables (these tables stay on the front-end), each user should have their own copy of the FE.

    Read my paper on Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability for more information.

    As for simplifying the launching and distribution of front-end databases to each user's desktop, check out our Total Access Startup program which is designed to centralize control and simplify Access application deployments.

    Author Closing Comment

    Due to design pressures and also to recent RIFs, we will only have 2 people using the current tables. Will leave as they are.
    Thanks for all of the assistance though!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This collection of functions covers all the normal rounding methods of just about any numeric value.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now