Solved

Access database on 2 computers

Posted on 2012-12-29
13
299 Views
Last Modified: 2013-01-08
My service company is expanding and I going to need to hire someone to assist me in the office. My concern is I run the company from an access database that controls all customer contact info, invoicing, and memos.

With now two computers working, how can I get all the info back to one database. Ie: if my assistant makes changes to an invoice or adds an invoice, how can I get that to one central database?

Any ideas would be greatly appreciated!
0
Comment
Question by:cansevin
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 10

Expert Comment

by:JEaston
ID: 38729468
You have a couple of choices.  If you have MS SQL Server I would upsize your database to this.

Otherwise, you can move the tables in to a new backend database which is linked to the front end which has your queries, forms etc.  Each PC that needs it can then have a copy of the front end and all the data is therefore shared as it is stored in the backend database.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 38729473
0
 
LVL 10

Expert Comment

by:JEaston
ID: 38729491
@boag2000:  Yes, if there is not a database to upsize to, then this is what I was meaning.  Thanks for the links, it has been years since I have split an access database!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38729531
Then it is not quite clear what your question is here...
(Because splitting the db is what both JEaston and myself are suggesting)

A Split database is the standard way of allowing multiple users update the same data.
(which is what you seem to be asking about here)

So, sure you can run an unsplit database if the concurrency is always going to be low, and/or the likelihood of two users updating the exact same field/record will also be rare.
(Only two users)
But what happens when this increases?
What happens when your app matures and you have automation that may result in edit  conflicts if the db is not split?

Even if you upsize the Backend to SQL Server, you still need a front end in each users workstation.

In other words, if you are sure splitting the db is not the solution, then what other technology are you looking for?

<it has been years since I have split an access database! >
In the same way that someone can claim that it's been years since they backed up their data (and they have never had a HD crash).
Or that it has been years since they have updated there Antivirus software, and never gotten a virus/Trojan/malware

So again, (unless we are misunderstanding your question), I am not sure what you are looking for here...


JeffCoachman
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38729556
Another approach is to change it to a web application.
It requires extra knowledge to develope, but has the following advantages:
     One application on server,
     Browser as fron end.
     Ease of adding other users.
     Can be accessed remotely.
0
 
LVL 10

Expert Comment

by:JEaston
ID: 38729557
@boag2000: I think you read my second comment and thought it was from the author.  I was just replying to you, although on re-reading it I missed the word 'server' in the line "if there is not a database server to upsize to"

Sorry if I caused confusion!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38729577
JEaston.

Yes, I think that is what happened.

Then I too must apologize for any confusion my post(s) caused...
:-(

Let's see if the OP comes back and clarifies...

;-)

Jeff
0
 

Author Comment

by:cansevin
ID: 38729650
Wow... this is confusing. I wish I was more intelligent with this stuff. Seems like "splitting" the database is one option or MS SQL Server is another option?

It will be rare that both computers are working on the exact same invoice/client.

What would be the pro's and cons of both? How expensive is getting up a MS SQL Server for my small business? How hard is it to "split" the database?

Thanks for all you help! Looks like I have a lot to learn!
0
 
LVL 10

Assisted Solution

by:JEaston
JEaston earned 167 total points
ID: 38729668
If your haven't used MS SQL server before then you may find this more restrictive.  It is more powerful, but changes how you manage tables etc as it can no longer be done from within access.  In terms of cost I believe there is a free verision with some size limitations.  It is worth learning if you ever plan to develop applications with more than a handful of users.

The difference between upsizing (to an SQL Server) or splitting (into front end and backend access files) is not huge.  It is a different process, but the principle is the same - move the table to the new backend and then link the front end to the backend.

Technically speaking I would say SQL is by far the better.  It has better backup solutions etc, but you do need to learn a whole new application.

If your looking for a simple solution and don't expect there to be more than a few users I would go with splitting the database.  You can alway move a split database to SQL Server in the future.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 166 total points
ID: 38729714
Folks .. let's keep this simple.  The OP has 2 users and 2 computers.
The most logical, practical solution - is .. ad already suggest to 'split' the db into a Front End (everything but tables) and a Back End (tables only). I'm sure the links provided by Boag2012 are sufficient for showing how this is done.

Put the BE on one of the computers (I suggest the OP's computer) - and a copy of the FE on both computers, linked to the BE. Done.

SQL Server and web are total overkill for this in terms of complexity and learning curve.

mx
0
 
LVL 10

Expert Comment

by:JEaston
ID: 38729733
@DatabaseMX:  I agree in general terms if users will stay small.  If it is expected to grow then thinking about progression to a server solution never hurts.  Developing a web application is a huge step - I have several web based systems which started life in Access.
0
 
LVL 75
ID: 38729765
"stay small."
Well ... I won't debate SQL Server - and of course, you really can't go wrong with SQL Server.  But, based on the description of the OP's situation - I'm thinking Access - especially since the OP already has a function Access db.

Meanwhile, I have (up to) 160 simultaneous users running over a 1Gb fiber optic WAN - 64 mile round trip between local workstations and our shared drive - with no issues whatsoever :-)

mx
0
 
LVL 9
ID: 38733777
I agree with MX - discussion of moving to SQL Server or rewriting of a web app is very premature for the scenario that the OP described.  Merely splitting the Access database and running the FE on two different PCs, as MX described, should be completely sufficient.

cansevin, once you have your database split, you're welcome to use our free J Street Access Relinker on our J Street Downloads page:  http://www.JStreetTech.com/downloads
It's a lot nicer than the Linked Table Manager.

It's some code that you simply copy into your front-end application.  It handles multiple Access back-end databases, ignores non-Access 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.

Cheers,
Armen Stein
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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

10 Experts available now in Live!

Get 1:1 Help Now