How to move records from linked tables to native table automatically

[D]

Hello everyone,
I'm looking to create a batch file or VB script of some sort
to copy the next date that has not already been imported and it's
corresponding data from a linked table (SQL using DSN) to a native
table to eliminate impact of reporting directly on the SQL server. Is
it possible to create a process to automate this Copy/Paste or Append
query task nightly around 4:30 am or so?

 

[Tim Ferguson]

I'm looking to create a batch file or VB script of some sort
to copy the next date that has not already been imported and it's
corresponding data from a linked table (SQL using DSN) to a native
table

Without knowing anything about your table structures, I guess it's
something like this:


insert into MyTable (RecordDate)

select Max(RecordDate)+1
from OtherTable IN [SQLServer; "c:\data\mydsn.dsn"]
where Imported=False

I made up the connection string in the IN clause. In fact, if you've
already linked to the other table then you don't need the IN clause at all.

Hope that helps


Tim F