Different Offices - One Database

[Judi]

Good morning/afternoon (depending on where you are...)

I have a question about the best way to move ahead with a tricky situation.

I have a database which is used to track quotes coming into the company I
work for. We have estimators in 3 locations. Most of the estimators are in my
local office, but there are 2 small offices in other states.

My boss wants me to help those other offices to be able to input into our
database. The database is on a shared drive which everyone can access, but
it's very slow for the other 2 offices.

My question is what is the best way to have them use the database? I have
done a bit of research and see that splitting or replication seem to be a
couple of possibilities. My boss seems to think that having them fill in
information on an excel spreadsheet and then importing the spreadsheet would
be the best way to go. I have tried this and can not get the information to
append to the main database.

I need to run reports that include all offices. I want all 3 office's
information to go into one table.

If you can help me, please do, I'm still new at this and need you to talk
slowly. I know almost nothing about VBA, SQL, mdb, or anything else that may
be abbreviated, so if that stuff is necessary, please be patient with me and
explain everything like you were talking to a child.

Thank you so much for any and all advice you can give me.

Judi <><

 

[Golfinray]

You need to split the database. You split it into a Front End (FE) and Back
End (BE.)
The BE is on the network drive and cotains all the tables. The FE is loaded
on each user's computer in their "My Documents" folder and you put a shortcut
to it on their desktop. The file on the server has to give users
read/write/edit/and delete privileges AND so does the folder the file resides
in. The best explainations for splitting: Joan Wild has a good explaination
at jmwild.com and google Albert Kallal, his site also has a good
explaination. Backup your database first and jump in. It is really not hard,
just follow the instructions step by step.

 

[Judi]

Will splitting the database make it faster in the other offices? They need to
have it as close to real-time as possible. If they have to sit and wait for
something to update, they're not going to do it. As far as I know, they will
not be running any of the reports, but they will have some queries running
for the forms they use.

Thanks for the help,

Judi <><

 

[Jae]

Splitting the db is a good idea, but the question that should be addressed
first for your problem is whether your 2 non-local offices are using same
network or servers. If so, this will be a walk in a breez. If not, we have to
look into other options such as migrating your BE to the MS Sql.

 

[Judi]

How do I find that out? Is it something I need to talk to IT about? I'm not
completely computer illiterate, but I have never worked with a network
before, only single computers...

Judi <><

 

[Jae]

I guess the easiest way would be to ask your IT ppl.

 

[Judi]

It's called a "Network Drive" is that what we need to know, or do I need more
information than that? I'm sorry to be so dim on this matter. I tried to
figure this out by myself, but didn't get very far on my own, and that's why
I'm here...

Thank you again for your help.

Judi <><

 

[John W. Vinson]

My boss wants me to help those other offices to be able to input into our
database. The database is on a shared drive which everyone can access, but
it's very slow for the other 2 offices.

Access is really not designed to work over the Internet. I would strongly
suggest that you use a "Terminal Server" solution; Windows Terminal Server
comes with Windows, or you can get more power and flexibility by using
something like Citrix Server.

See http://www.granite.ab.ca/access/terminalserver.htm for some links and
discussion.

I have to emphatically disagree with golfinray's suggestion. It's appropriate
for a local network but will give bad performance and database corruption over
a Wide Area Network.

 

[Clif McIrvin]

Good morning/afternoon (depending on where you are...)

I have a question about the best way to move ahead with a tricky
situation.

I have a database which is used to track quotes coming into the
company I
work for. We have estimators in 3 locations. Most of the estimators
are in my
local office, but there are 2 small offices in other states.

My boss wants me to help those other offices to be able to input into
our
database. The database is on a shared drive which everyone can access,
but
it's very slow for the other 2 offices.

My question is what is the best way to have them use the database? I
have
done a bit of research and see that splitting or replication seem to
be a
couple of possibilities. My boss seems to think that having them fill
in
information on an excel spreadsheet and then importing the spreadsheet
would
be the best way to go. I have tried this and can not get the
information to
append to the main database.

I need to run reports that include all offices. I want all 3 office's
information to go into one table.

If you can help me, please do, I'm still new at this and need you to
talk
slowly. I know almost nothing about VBA, SQL, mdb, or anything else
that may
be abbreviated, so if that stuff is necessary, please be patient with
me and
explain everything like you were talking to a child.

Thank you so much for any and all advice you can give me.

Judi <><

Your boss's suggestion of using Excel at the remote sites is not
necessarily a bad one.

Some questions to consider:

1. Would doing data entry on a spreadsheet require a lot of repetitive
entry that the data entry forms eliminate?

2. What about validity checking? (I presume there is at least some
validity checking in the local data entry forms.)

I can think of strictly Excel based solutions to both questions; I can
also think of Access based solutions, such as:

3. Create a "Data Entry" copy of the main database which includes the
data entry form and the related tables that are involved in validity
checking, etc.

4. Create a process (Macro or VBA -- It may be possible from a Query,
though at my present level of knowledge I can't answer that) which
exports the (daily?) data to a (comma) delimited text file or an Excel
workbook which resides in the DataEntry database.

5. Create a companion process in the main database to import the data.


There are many, many different approaches to solving your problem.
Investigate a bit, pick one you'd like to explore further and come back
with your questions!

Good luck.

 

[a a r o n . k e m p f]

Access is not designed to work over _ANY_ networks in the real world.
it wasn't even designed-- from the ground up- for multiple users


To anyone with a sense of logic-- they would move to SQL Server.
Using Access Data Projects, you can have better performance-- over
more networks-- for more users-- and more records-- than anything
avaiable if you are stuck with an obsolete database

 

[posted_by_anonymous]

Ignore this troll. He knows little or nothing about Access and little or
nothing about SQL Server, despite his constant recommendations of it.

 

[George Hepworth]

IMHO, the best way to deal with trolls is a two-part strategy.

1. Ignore their standard rantings.
2. Step in when it is necessary to correct a misstatement that could
potentially mislead a novice poster.

While it is true that novice posters don't know a great deal about Access,
it is also true that most of them are smart enough to figure out quite
quickly who the trolls are.

 

[posted_by_anonymous]

Sir, you have made your point of view quite clear. It is equally clear that
there are those who do not agree with your approach when it comes to this
particular troll and believe he must be taken on, and answered, and labeled
-- however many times it takes to brand him as a lying, disruptive, pariah to
prevent or reduce the damage he does.

Anony Mous

 

[a a r o n . k e m p f]

I just came back from the promised land to save you guys- teach you
all about SQL Server

Sorry-- but that's my story and I'm sticking to it.
I should recognized as a SAINT.

And just because 900 other people STALK me and chase me down--
ATTACKNIG EVERYTHING I SAY and SPREADING LIES AND ABUSING THE POLICE
BY WASTING THEIR TIME-- COMPLAINING ABOUT A ****NIG SWEAR WORD?

whatever.

-Aaron

 

[So Sorry For Poor Aaron]

IMHO, the best way to deal with trolls is a two-part strategy.

And, he's really gone over the top now... thinks he's been resurrected;
thinks he's saving us all; and thinks he's a Saint. Saint Aaron the
Discombobulated does have a nice ring to it, I'll have to admit that. Poor
sweetcheeks, he's just delusional. Maybe it should be Saint Aaron the
Delusional, martyred for no good reason, except that he threatened murder and
arson (items which to him, aren't as important as 'swear words').

So Sorry For Poor Aaron the Discombobulated and Delusional Saint