Gathering Info From Multi Sites

D

DS

Hi Everyent one, I'm looking for some suggestions. I have the same
Access Application at various sites, different cities, different streets
whatever. I need to take all of these locations at the end of the
business day and bring all the info to one spot that is also located
somewhere else. What is the best way to do this?
Thanks
DS
 
A

Albert D.Kallal

The best way is to have one copy of the data, and have some type of remote
connection.

So, they all could run the application (mde) on their computers, but you
have one server sitting somewhere, and use sql server on it for the back
end. Of course, "best" way is going to depend on many things. For example,
if those other users don't have a good internet connection, this is idea is
of no use...is it? Also, if their connection fails, then they can't use
their software. (but, with no internet, for many companies these days..that
is like not having a phone).

Also, when you say gather all this information, do you ever want the
information to go the OTHER way? For example, one table might be pricing
information, and a update to a price would thus go back to all of the users?

So, again, not knowing the particulars, a solution is hard to suggest.

Another approach would be to have each system connect to a central system
that has sql server on it (thus, the systems would be stand alone, but then
connect each night to transfer the data to the central sql server based
system.

And, if you are JUST transferring data one way all the time, then each
system could generate a file (a csv) and email it to you, and you simply
import the file into you mdb.

Another possible technology is replication. Replication is better then a
transfer of simple file since the relationships of data is also transferred.
So, if I got a order entry system, and add ONE new customer, and ONE new
order, that could potentially in a complex database mean that 5 or even 10+
tables of data gets updated. Well, now that is complicated and difficult
type of data transfer to build yourself. (hence, the suggestion to have one
system, and allow everyone to connection to it).

Of course, ms-access DOES have a technology built in for transfer and
synchronize these databases. This technology is replication. (take a read of
it in the help files).

And:

For some info on replication, check out:

Jet 4.0 Replication
http://www.mvps.org/access/general/gen0033.htm

TSI Synchronizer
http://www.mvps.org/access/modules/mdl0033.htm

Synchronization without Replication
http://www.mvps.org/access/tables/tbl0015.htm

Database Replication in Microsoft JET 4.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/dbrepjet.asp


Replication FAQ
Written by Mary Chipman, Michael Kaplan, Paul Litwin, and Steve Thompson. As
with any other FAQ, this paper answers many Access Replication questions you
might encounter.
http://download.microsoft.com/download/access97/faq/1/WIN98/EN-US/RPLFAQ97.EXE


There is also a ton of articles on Replication at:

http://www.trigeminal.com/usenet/usenet.asp?1033
 
D

DS

Albert said:
The best way is to have one copy of the data, and have some type of remote
connection.

So, they all could run the application (mde) on their computers, but you
have one server sitting somewhere, and use sql server on it for the back
end. Of course, "best" way is going to depend on many things. For example,
if those other users don't have a good internet connection, this is idea is
of no use...is it? Also, if their connection fails, then they can't use
their software. (but, with no internet, for many companies these days..that
is like not having a phone).

Also, when you say gather all this information, do you ever want the
information to go the OTHER way? For example, one table might be pricing
information, and a update to a price would thus go back to all of the users?

So, again, not knowing the particulars, a solution is hard to suggest.

Another approach would be to have each system connect to a central system
that has sql server on it (thus, the systems would be stand alone, but then
connect each night to transfer the data to the central sql server based
system.

And, if you are JUST transferring data one way all the time, then each
system could generate a file (a csv) and email it to you, and you simply
import the file into you mdb.

Another possible technology is replication. Replication is better then a
transfer of simple file since the relationships of data is also transferred.
So, if I got a order entry system, and add ONE new customer, and ONE new
order, that could potentially in a complex database mean that 5 or even 10+
tables of data gets updated. Well, now that is complicated and difficult
type of data transfer to build yourself. (hence, the suggestion to have one
system, and allow everyone to connection to it).

Of course, ms-access DOES have a technology built in for transfer and
synchronize these databases. This technology is replication. (take a read of
it in the help files).

And:

For some info on replication, check out:

Jet 4.0 Replication
http://www.mvps.org/access/general/gen0033.htm

TSI Synchronizer
http://www.mvps.org/access/modules/mdl0033.htm

Synchronization without Replication
http://www.mvps.org/access/tables/tbl0015.htm

Database Replication in Microsoft JET 4.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/dbrepjet.asp


Replication FAQ
Written by Mary Chipman, Michael Kaplan, Paul Litwin, and Steve Thompson. As
with any other FAQ, this paper answers many Access Replication questions you
might encounter.
http://download.microsoft.com/download/access97/faq/1/WIN98/EN-US/RPLFAQ97.EXE


There is also a ton of articles on Replication at:

http://www.trigeminal.com/usenet/usenet.asp?1033
Click to expand...
Thanks Albert,
This SQl Server thing sounds good. How do you use it? Where do you buy
it? As you can see I'm at loss here. I'm all ears though. Thank you!
DS
 
A

Albert D.Kallal

Thanks Albert,
This SQl Server thing sounds good. How do you use it? Where do you buy
it? As you can see I'm at loss here. I'm all ears though. Thank you!
DS
Click to expand...

There is a free copy of sql server on every office CD. When you use a
development tool like VB, or c++, or ms-access, you as a developer can make
a decisions as to what database system you want to use with the development
tool you are using (so, remember, ms-access is not a database, but only a
tool like VB, or c++ in which you create software - you as a developer still
make the choice as to which database system you use).

So, while most of use the JET database engine with ms-access, you can use
Oracle, sql-server, MySql etc. as the database part for ms-access.

In addition to the free sql server database on the office cd, you can also
use the new express edition of sql server. You can read about it here:

download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

about:
http://www.microsoft.com/sql/express/
 
D

DS

Albert said:
There is a free copy of sql server on every office CD. When you use a
development tool like VB, or c++, or ms-access, you as a developer can make
a decisions as to what database system you want to use with the development
tool you are using (so, remember, ms-access is not a database, but only a
tool like VB, or c++ in which you create software - you as a developer still
make the choice as to which database system you use).

So, while most of use the JET database engine with ms-access, you can use
Oracle, sql-server, MySql etc. as the database part for ms-access.

In addition to the free sql server database on the office cd, you can also
use the new express edition of sql server. You can read about it here:

download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

about:
http://www.microsoft.com/sql/express/
Click to expand...
Thank you Albert.
DS
 
Top