• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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

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.

3 Solutions
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!
Note:  this has to be a function.  However, we all know how easy it is to change a Sub to a Function.
"....and have BOTH on the net...."

what exactly do you mean by this?

You cannot run an Access application across the internet.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Luke ChungPresidentCommented:
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.
BrujAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now