How to push data from local ADP to remote SQL server?

[christopher.b.lewis]

I have been using Access for many years. I now need to upsize to SQL
because my website is getting much more traffic than it used to. I
want to continue using my existing Access forms to populate the new SQL
database.

I have tried to link my existing local Access forms to my remote SQL
sever database. However, my forms have many lookups that pull
thousands of rows of data; so performance is inadequate.

I was thinking that a quick and good solution would be to update my
data locally using an Access Database Project (the lookups should
perform reasonably well locally) and then push the new data updates to
the live, remote SQL database.

Does this sound like a fine way to work?
Any suggestions on how I should push my new data to the remote SQL
server? (I have never done this before.) What concepts/processes
should I learn about to get this done? Any specific recommendations on
how to begin would be much appreciated.

One of my concerns is that during the data transfer from local to
remote, the integrity of the relationships will break. Is there a
standard way of dealing with that?

 

[Norman Yuan]

ADP does not contain data. It is pure frnt end application, connected to SQL
Server, with all data sit in SQL Server database (while *.mdb front end can
hold data on its local table). Using ADP in your situation does not help at
all, or not much different from using *.mdb linked tables.

 

[christopher.b.lewis]

Using ADP in your situation does not help at

all, or not much different from using *.mdb linked tables.

The data on my remote server needs to be in moved to SQL, but I want to
maintain it locally with my existing Access forms and then push the
updates to the remote SQL server. What do you suggest?

 

[Sylvain Lafontaine]

You can use Replication (difficult to set up when used over the internet),
develop your own synchronising mecanism or use one of the many data
synchronising tools that are on the market.

Personnally, I like to use the tool from Red Gate.

All these tools will use transactions for updating the data, so you don't
have to have concerns about the integrity of your data, including
relationships. The exception to this will be the case where the data on the
website have also been edited (ie., in the case that you will need
bi-directional synchronising) but this doesn't appears to be your case from
your description.

However, if you need it, the Merge Replication will easily (hum-hum...)
overcome this problem.

Other possibilities would be to use ADP to update the data directly on the
remote SQL-Server. When well designed, an ADP solution will be faster than
an MDB solution.

The idea of using, if necessary, a local SQL-Server for fast retrieving of
lookup data might seems a good idea but it will not be easy to do with ADP
because of the lack of a direct support for multiple connections. (But it
can be done if you want to).

Finally, using other solutions like Terminal Server, ASP.NET or WinForms
will give you the best performance for a remote access.

 

[dbahooker]

I'm not sure; I've had some pretty excellent responses using ADP.. it's
a great option for going over a WAN for example.

can't you just link servers or something? or synchronize between
servers?

Sylvain you know any good replication resources? I've always had
problems with that; maybe do they make books that specialize in
replication??

Still burning from the last time i did replication

 

[christopher.b.lewis]

Thanks for the advice!