Replication between SQL and Access97?

I have an Access 97 database that I would like to "mirror" in my SQL server 2000 database.

Basically I would like SQL server to keep a current copy of an Access 97 database table which exists on remote machine on our network. Any time there is a change made to the Access table, I would like the SQL server to be updated automatically.

Is this something that is possible to do with replication/synchronization, or do I have to push the data up with another app?

ThanksTo my knowledge you can not set up relication from Access to SQL Server. Are you sure your tables are in Access? Or are you using an Access Data Project or tables linked into a mdb file?

If your tables are truly located locally in your access database the only way I can think of doing what you want to do is to link in your SQL Server tables and for every insert, update and delete you run the same queries against the SQL tables at the same time.

Why not just move your tables in your access application to SQL Server?|||The tables are definately in an Access 97.

I thought about moving the tables into the SQL database but I really need a copy locally in the event of a network failure. (The app which generates the data and fills the tables runs on the machine remote to the SQL database.)

Thanks for your suggestions.|||move the tables to sql. and then you can have DTS packages that push them down to your local access app.

Is'nt your SQL Server running backups on itself anyways?|||Depending on why you want to do this, you could
1)put a copy of sql server on the remote server and then use those tables in your access app. Replication to the other server is then easy if you still want this.
2)use dts packages to copy the data into your sql server tables using a scheduled job.
3)use heterogenous transactional replication but it looks a little complicated at first sight and I know nothing of the implications and practicality of implementing this type of replication|||I clearly need to bone this. I did find some info in the SQL Server Books Online under "Access Subscribers" which looks promising.

I believe this is item 3 refered to in the previous post.

Thanks for your help.sql