Is there a way to create a general dts to import tables from access mdb file?
so that i will not have to change the dts for every table i am adding to the access file?
something like foreachtable? or a way to read the tables list from the access file in the sql connection?
I'd just use a stored procedure. Have it take an argument for the linked server name for the access file (or a UNC pathname and have your procedure create the linked server too using sp_addlinkedserver), then use sp_table_ex to put the catalog into a temp table, use a cursor to traverse the list of tables, and do a SELECT INTO from each of the foreign tables into a SQL Server table.