Help Using DTS to import multiple tables into Access Database

[tezza98]

I need some help.

Im using a dtsrun command to import 9 tables into an Access database, most of the tables have about 1000 rows, but one has 20000+ rows and grows everyday. Im Using Access 2003 and SQL Server 2000. on a WinXP Machine

When the database is opened a stored procedure drops all the tables and then imports the csv files using dtsrun. The database is only used for reporting and the data is entered into the csv files using 3rd party systems,

What i want to know is if there is a way to only import new rows, or rows that have been changed into the database, so i dont have to drop the tables everytime the database is opened.

I have seen one way to do this is to use temporary tables and import the data to the temp tables and the compare teh temp tables with the actual tables, and the insert and update the new rows, but this solution still has to import all the rows before hand and will take longer then it takes now

Here is my stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[FillDatabase]
AS
    SET NOCOUNT ON
        DELETE FROM dbo.Cards
        DELETE FROM dbo.Meter
        DELETE FROM dbo.Products
        DELETE FROM dbo.Settlement
        DELETE FROM dbo.Sites
        DELETE FROM dbo.Stock
        DELETE FROM dbo.Tanks
        DELETE FROM dbo.Transactions
        DELETE FROM dbo.Users
 
        DECLARE @Result BIT
 
        EXECUTE @Result = master..xp_cmdshell 'DTSRun /N "PropogateFuelDatabase" /F "C:\Program Files\TransHost\Reports\Reports Database\PropogateFuelDatabase.dts" /A Instance:8="frontpc\fieldwicks" /W "-1"'
        IF ( @Result = 1 )
            BEGIN
                RAISERROR( 'Error translating data', 18, 1 )
 
                RETURN
            END
    SET NOCOUNT OFF
 

Cheers

Terry

[iburyak]

You said:

I have seen one way to do this is to use temporary tables and import the data to the temp tables and the compare teh temp tables with the actual tables, and the insert and update the new rows, but this solution still has to import all the rows before hand and will take longer then it takes now

You can link Access to server tables using ODBC this way you dont have to import anything just compare data and insert\update\delete differences.