Online Connection between Access and SQL Server

[Michael]

Hello,
I am trying to develop a database solution for an organisation in
Indonesia.

This organization has a number of offices and users throughout the
country. They all need to maintain their own data, but I also need to
create a central database, to store all of the data, and share data
between the individual users. The problem is that the internet
connections are not reliable, so a online solution isn't possible. I
am planning to implement stand alone databases in Access, which "sync"
with the central server. This sync could happen automatically (maybe
daily), or could be initiated by the user. This Sync would/could take
place when the internet connection is working.

I am very familiar with Access, but I suspect that I will need to use
SQL Server for the central database. I have not used SQL Server
before, and have a few questions:

Is it possible to host a SQL Server, so that it is "Online"? What do I
need to do for this? Does this need to be hosted by an ISP, or could
it been hosted on a computer in our office? What are the security
considerations?
Is it then possible to connect to this SQL Server from the Access
databases in the various locations to "Sync" the data? If I could run
SQL statements from the stand alone databases which could access the
online SQL Server I could write the code for the Sync procedure. The
bit that I am unsure of is how to connect to the SQL Server?

Could anyone point me in the right direction? Examples? References?
What sort of technology to use?
I know VB and how to write SQL statements, it's just the connectivity
that I am unsure of.

Thanks in advance.

Cheers

Michael

 

[lyle fairfield]

Hello,
I am trying to develop a database solution for an organisation in
Indonesia.

This organization has a number of offices and users throughout the
country. They all need to maintain their own data, but I also need to
create a central database, to store all of the data, and share data
between the individual users. The problem is that the internet
connections are not reliable, so a online solution isn't possible. I
am planning to implement stand alone databases in Access, which "sync"
with the central server. This sync could happen automatically (maybe
daily), or could be initiated by the user. This Sync would/could take
place when the internet connection is working.

I am very familiar with Access, but I suspect that I will need to use
SQL Server for the central database. I have not used SQL Server
before, and have a few questions:

Is it possible to host a SQL Server, so that it is "Online"? What do I
need to do for this? Does this need to be hosted by an ISP, or could
it been hosted on a computer in our office? What are the security
considerations?
Is it then possible to connect to this SQL Server from the Access
databases in the various locations to "Sync" the data? If I could run
SQL statements from the stand alone databases which could access the
online SQL Server I could write the code for the Sync procedure. The
bit that I am unsure of is how to connect to the SQL Server?

Could anyone point me in the right direction? Examples? References?
What sort of technology to use?
I know VB and how to write SQL statements, it's just the connectivity
that I am unsure of.

Thanks in advance.

Cheers

Michael

Yes.

There are many providers of internet available MS-SQL databases for as
little as 10 USD / month. They provide server maintenance and do daily
backups. They are up 99.44% of the time which is far more than the server
of any organization with which I have worked. I have been very happy with
DiscountAsp.Net, after being less than happy with Interland. (3 times the
service, one third the cost). One can connect to them with a simple cable
or dsl internet connection and do the table, sproc creation etc with any
number of utilities, including Access. Many are free. I use Microsoft SQL
Server Management Studio Express (free). Its interface is much superior
to Access's.

One can then connect and "sync" through Access and (ODBC or ADO). Of
course there are other technologies for doing so; some are feee. If one
is not going to use Access as a User Interface for editing, reporting etc
then it may be entirely wasteful to load in this inefficient collection
of archaic procedures and convoluted code.

MVPs and others here have argued that such internet-enabled databases are
insecure. I have been using them for many years. I have never had a
problem with security. In the autumn I created a new database at
DiscountAsp and challenged the insecurephobiacs to break in and do a
simple task, viz, create a simple table named with their name, eg,
"Albert" or "David". No one has done so yet.

Typically these providers use very sophiticated security software as
below:

""TippingPoint Intrusion Prevention Systems
The TippingPoint Intrusion Prevention System (IPS) delivers the most
powerful network protection in the world. The TippingPoint IPS is an
in-line device that is inserted seamlessly and transparently into the
network. As packets pass through the IPS, they are fully inspected to
determine whether they are legitimate or malicious. This instantaneous
form of protection is the most effective means of preventing attacks
from ever reaching their targets.

TippingPoint's Intrusion Prevention Systems provide Application
Protection, Performance Protection and Infrastructure Protection at
gigabit speeds through total packet inspection. Application Protection
capabilities provide fast, accurate, reliable protection from internal
and external cyber attacks. Through its Infrastructure Protection
capabilities, the TippingPoint IPS protects VoIP infrastructure,
routers, switches, DNS and other critical infrastructure from targeted
attacks and traffic anomalies. TippingPoint's Performance Protection
capabilities enable customers to throttle non-mission critical
applications that hijack valuable bandwidth and IT resources, thereby
aligning network resources and business-critical application
performance.

The system is built upon TippingPoint's Threat Suppression Engine (TSE)
- a highly specialized hardware-based intrusion prevention platform
consisting of state-of-the-art network processor technology and
TippingPoint's own set of custom ASICs. The TippingPoint ASIC-based
Threat Suppression Engine is the underlying technology that has
revolutionized network protection. Through a combination of pipelined
and massively parallel processing hardware, the TSE is able to perform
thousands of checks on each packet flow simultaneously. The TSE
architecture utilizes custom ASICs, a 20 Gbps backplane and
high-performance network processors to perform total packet flow
inspection at Layers 2-7. Parallel processing ensures that packet flows
continue to move through the IPS with a latency of less than 215
microseconds, independent of the number of filters that are applied.

The TippingPoint TSE architecture also enables traffic classification
and rate shaping. Sophisticated algorithms baseline "normal" traffic
allowing for automatic thresholds and throttling so that mission
critical applications are given a higher priority on the network.

The TippingPoint IPS family offers a range of products that differ in
capacity and the number of simultaneous segments they protect.

TippingPoint X505
TippingPoint 50
TippingPoint 200
TippingPoint 200E
TippingPoint 400
TippingPoint 1200E
TippingPoint 2400E
TippingPoint 5000E
TippingPoint SMS (Enterprise-Level Management System)
TippingPoint ZPHA (Zero Power High Availability)
An integral part of the TippingPoint solution is the Digital Vaccine®
Service that delivers new filters on a weekly or even daily basis to
maintain evergreen protection for the latest vulnerabilities, exploits,
viruses and rogue applications."

 

[Bonnie]

Hi Michael,

How much data are you talking about. Here's a quick and simple solution
that I have used. Set up an ftp drop box with your ISP, create a batch file
on the users desktops that runs ftp script to grab a particular file off the
ftp site (I would use a comma delimited format or something that doesn't take
a lot of up/download time, this will always overwrite the last file to
update). You can either put a timed trigger to run it or a button for your
users to update. After the file has been downloaded, it next runs the
pre-designed import process and a query to append. Seamless to the users. I
use it for my data entry at home girls and it's quick and I have no problems.

Bonnie

 

[shenjing1021]

Hi Michael,

How much data are you talking about. Here's a quick and simple solution
that I have used. Set up an ftp drop box with your ISP, create a batch
file
on the users desktops that runs ftp script to grab a particular file off
the
ftp site (I would use a comma delimited format or something that doesn't
take
a lot of up/download time, this will always overwrite the last file to
update). You can either put a timed trigger to run it or a button for
your
users to update. After the file has been downloaded, it next runs the
pre-designed import process and a query to append. Seamless to the users.
I
use it for my data entry at home girls and it's quick and I have no
problems.

Bonnie