473,287 Members | 1,643 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Help Using DTS to import multiple tables into Access Database

38
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:

Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4. ALTER  PROCEDURE [dbo].[FillDatabase]
  5. AS
  6.     SET NOCOUNT ON
  7.         DELETE FROM dbo.Cards
  8.         DELETE FROM dbo.Meter
  9.         DELETE FROM dbo.Products
  10.         DELETE FROM dbo.Settlement
  11.         DELETE FROM dbo.Sites
  12.         DELETE FROM dbo.Stock
  13.         DELETE FROM dbo.Tanks
  14.         DELETE FROM dbo.Transactions
  15.         DELETE FROM dbo.Users
  16.  
  17.         DECLARE @Result BIT
  18.  
  19.         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"'
  20.         IF ( @Result = 1 )
  21.             BEGIN
  22.                 RAISERROR( 'Error translating data', 18, 1 )
  23.  
  24.                 RETURN
  25.             END
  26.     SET NOCOUNT OFF
  27.  
Cheers

Terry
Mar 28 '07 #1
1 2774
iburyak
1,017 Expert 512MB
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.
Mar 28 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Doug Baroter | last post by:
Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in...
3
by: Michael Persaud | last post by:
HI, I have a xml file and would like to import its contents into a table in SQl2000 can some one say how? it has to insert multiple records Thanks MP
13
by: Aladdin | last post by:
I have an MS Access form on which I have a listbox listing tables in that database. I want to be able to click on any of those tables and view its contents on the same form using subforms or any...
6
by: Don Leverton | last post by:
Hi All, I've got a situation where I am developing an Access 97 app for a client, and am in the "beta testing" stage. I have split the app up, using the DB splitter, into front-end /back-end...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
1
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
7
by: Pete | last post by:
I need to import multiple tab delimited files for which I do not have the formats. All files have column headers in the the line. The files must be dynamic. The only common value is that the...
4
by: theintrepidfox | last post by:
Dear Group Excuse my ignorance but I have never come across this problem and need some advice. I have two copies of the same MS Access 2000 database. One from about 6 months ago ond one recent...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.