Macro's & Linked Tables

[cstrong]

Hi all,

We have a bit of a problem here. We have a macine on the network which has Access on it. Within the Access database, there is a linked ODBC connection to an SQLBase database. Now, we need the data from SQLBase into our SQL Server. Normally, I'd set up a DTS and insert rows to my SQL Server DB etc etc etc but for some reason, we can't get the data to a remote source (in this case SQL Server). However, if we do the same thing locally, it works....so thats the reason for access....Now...Is there a Macro that I can run within Access that will fire when there is a change of data in the Linked table. The idea is to insert the data into an Access table, which I can import into SQL Server....Note, that you can't see the Linked table from SQL Server, only access tables.

Thanks!
Clive

 

[RobHudson]

As Access isn't a 'real' database you can't do triggers. It would be good if you could!

The only thing I can think of doing here would be to have Access sitting there with a procedure running that is constantly looking for new data in the linked table. Maybe by keeping track of the number of rows in the linked table?? When new data appears, run a query that builds a table of the new data. That could then be picked up by SQL Server. In order to keep track of what the new data is. I guess you'd need to take a copy of the linked (maybe a make table query) and use that to say show me all the data in linked table that is not in local copy of table. Then recreate the local table having produced the table ready for SQL Server...

If that makes sense!

Probably not the best idea in the world but might work!