Microsoft Access for Multiple Users

I have a Microsoft Access 2007 file that I learned how to share in a "shared folder".

I am now interested in learning the dynamics of having two users use Access at the same time. Is this easy to do? Does it come standard with Access 2007? I would imagine it may be slightly complicated as two users may be on changing the same table at the same time.

Do I have to pay for something additionally?

Any resources would be great.
cansevinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dqmqConnect With a Mentor Commented:
Yes, it's standard with all versions of Access for as long as I can remember. You can distribute run-time versions of an Access database at no charge.  Otherwise, each workstation is must have an Access licence.  

Sharing Access data is relatively easy, but not painless.  Life is sweeter when  unless your application was designed for multi-user Access from the get-go.  

Sharing the forms, queries, and reports is another matter.  Most practitioners split their databases into a shared backend for the tables and individual COPIES of the front end for querys, forms, and reports.  Access includes a wizard that makes splitting pretty easy.  
0
 
OxonDevConnect With a Mentor Commented:
As dgmg said the preferred way of deploying a multi-user access application is to split the database file into a back end (with all the tables) and a front-end (with everything else).  You use linked tables in the front end to access the data in the back end file and you distribute the frot ends to each user.

If you share a file with forms and reports eventually it will become unstable and corrupt.  Before that a shared file is slower to us.  So although deploying multiple front end files takes more work, there is an increase in performance and stability.
0
 
EirmanConnect With a Mentor Chief Operations ManagerCommented:
The key to access sharing is to split your database into two ....... The tables database (in a shared location) and everything else in an identical frontend database on each pc. Make sure that each pc sees the shared tables on the SAME drive letter. As you develop the frontend on one pc you can use a simple batch file to copy it to each pc. They can be up and running with new versions about a minute,
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Make sure that each pc sees the shared tables on the SAME drive letter"

The most reliable approach is to like a Front End master to the full UNC network path of the backend ... and then deploy a copy of the front end to each workstation (as noted above).  Using a mapped drive letter is not recommended.

The help with a semi automated deployment of each FE copy ... see this approach:

Auto Updater:  http://www.granite.ab.ca/access/autofe.htm

Also ... there are dozens of discussions on EE already on this exact subject.  I would suggest doing a search and read up on some of the info.

mx
0
 
cansevinAuthor Commented:
Wow... this seems intimidating. I think I am going to give it a shot, no clue if I have the knowledge upstairs to accomplish it. Thanks for starting me off!
0
 
Jim P.Commented:
You may also want to refer to http://uterangel.com.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"Wow... this seems intimidating."

Well ... maybe a little at first. But with only two users, you should have minimal issues.  And with only 2 users, you probably don't need the automated deployment scenario.

To start off with ... you *mainly* need to split your app into a Back End (tables only) and a master Front End (every thing else - forms, queries, etc.). Link the master FE to the BE in the common 'shared folder'. Then put a copy of the FE on the two workstations.  It should now be ready for use.  When and if you run into multi-user issues, we can address those then.

To help you with the splitting and linking ... here is that info:

Start with this:

'How to manually split a Access database in Microsoft Access
http://support.microsoft.com/kb/304932

'Preparing Your Access 2003 Database for Deployment, Part 1
http://msdn.microsoft.com/en-us/library/aa662933.aspx

http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/Index.html

This EE link has a lot of good info also:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23986481.html?cid=748#a23179169

A final note:

After you do the split ...put the new BE on your server ... use the Linked Table Manager  ...
Tools>>Database Utilities>>Linked Table Manager.  Be SURE ... to always check to box called Always Prompt For New Location.  This will force the Windows  File Browse to open.  

Also, I recommend that you navigate via the full network path using My Network Places >> Entire Network >> Microsoft Windows Network ... then point to the Workgroup and ultimately the folder location on the server that contains the backend.  This way, you never have to worry about mapped drive letter changes, etc.

Also ... if you happen to use the Database Splitter Wizard to split ... AND ... you happen to have the Switchboard table ... that will also be put into the BE.  Put this back into the FE and delete it from the BE.  This is really a 'local' table and does not need to be linked.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Do I have to pay for something additionally? "
No.  As noted above, each workstation must have a legal copy of Office (or at least Access) ... which is usually the situation anyway.

My take is you are not quite ready to do a 'runtime' version of this app.

mx
0
 
dqmqCommented:
>"Wow... this seems intimidating."

Compared to any other dbms development platform on the planet, it's a stroll in the park.  

The only thing I would add to MX's expert suggestions is to keep a copy of your original .mdb or .accdb file as a fall back.  Then, split the DBMS and have at it.  You should be multi-user capable within the hour.    
0
 
Jim P.Commented:
Please keep a backup. But you have enough Acces to cover 98% of your possible ussue.

The other 2% of issues aren't worth considering. ;-)
0
 
Andrew_WebsterCommented:
The other 2% are what usually take 98% of the d*mn effort! >_<
0
 
OxonDevCommented:
Amen Mr Webster!
0
 
cansevinAuthor Commented:
Thanks guys... so my dumb a$# hitted up craigslist and tried to look for an professional to accomplish the task. After the first guy proposed between $1,000 and $2,000 which was way over my budget, the next two guys told me Access 2007 is already completely compatible for it... awesome news.

Correct me if I am wrong, cause I am an idiot. But I have a minor database I created that I saved on my "public" folder on my desktop in my home office. When I open that file on my laptop (which also has Access 2007) through the shared folder with it also open on my desktop... Access takes are of having two users on the same file? Ie: if we both happen to be on the same form with the same client making changes to the form (which is rare but an issue), Access will properly either update the first one or tell us when the other has changed it.

Are there any issues I should be afraid of? I did a test run and it seemed to work perfectly.

Maybe I posted a completely wrong issue for what I was dealing with.

Please advise. Thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"the next two guys told me Access 2007 is already completely compatible for it"

All version of Access since 1.0 are multi-user compatible.  Craig's list?  BEWARE.  You will be much better off contacting an Expert here via their profile.  Several offer consulting services.

"Are there any issues I should be afraid of?"
With only two users, your issues s/b minimal.

mx
0
 
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
After you split your database, which is absolutely what you should do, you may also want to make sure your linked tables *stay* linked.  For example, if the back-end database changes names or folders, or if it isn't available for some other reason, it's good to have a friendly way for the users to deal with it.

Linked Table Manager is awkward for users.  If you want a more automated approach to relinking tables, you're welcome to use our free J Street Access Relinker on our J Street Downloads page:  http://ow.ly/M56Q

It handles multiple Access back-end databases, ignores ODBC linked tables, and can automatically and silently relink to back-end databases in the same folder as the application (handy for work databases or single-user scenarios).  There's a ReadMe table with instructions.

0
 
dqmqCommented:
If you do not split your database, it's only a matter of time and luck before you corrupt it.  Then, $1,000 will seem like a bargain.

But don't let that discourage you.
1. Make a backup copy
2. Put the one-user version in a network folder and give all users R/W permission
3. Run the split database wizard on the network copy. This will result in two .accdb files, one with tables (the BE) and one with everything else (the FE).
4. Open the FE and use the LinkedTableManager to relink all the tables by drilling down from MyNetwork Places (instead of a drive letter) as suggested by MX.
5. Copy the front-end to each workstation that you want to share the application.

That's the 98% of the work in 2% of the time part and you should be up and running in a couple hours, even for a novice.

Then, do some testing, testing, testing to find the problems.  Typically they are few, but evasive.  That's where you may want some expert help. And you will be way better off, finding an expert by researching EE profiles than taking your chances on Craigslist.




0
 
Andrew_WebsterCommented:
dgmd's advice is good, as is the attitude behind it.

I've come to realize that when I'm in meetings as a developer, I sound like a doom and gloom merchant.  It's because if we don't pay attention to those 2% things, then sure enough, they will take up 98%!  Too often, I've been on a project that was pitched at a few days and took three months, or pitched at three months and took two years!  Why? Because of the 2%.

But the way you get a feel for what the 2% is, and the kind of problems that can arise will only come from do lots and lots of work with the 98% that's easy as anything. That 98% is what makes Access great.

And there's a whole bunch of other stuff you can get on creaiglist, but a pro developer won't be it!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, 1K-2K is a super bargain for any Access database!

mx
0
 
Andrew_WebsterCommented:
LOL!  It's true.  $1-2K seems like a lot when compared to our weekly pay, but when you look at the cost of running any kind of serious business, it's what you pay for printer ink for a year.  I'd say the average length of time I've spend working on a pro application is around three months, and I personally want a little more that a couple of grand for that!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I should clarify what I said ... in that 'for any Access database done by a PRO developer that really knows what they are doing and has the experience level to pull of a successful application!"

mx
0
 
Andrew_WebsterCommented:
LOL!

Sorry for hijacking your question, cansevin, for an old-lags b*tching session!

Go ahead, go for it, and keep coming back here for help if you need it.
0
All Courses

From novice to tech pro — start learning today.