SQL Server <---> Access

[ranjithvenkatesh]

Hi,

I have a Access Database A

A.mdb gets additions every month.

We need to import A into an SQL Server database S.

We lose the keys in the process.

We manually add the keys in S.

When a new A comes we need to import it to S.

I try to do this with Data Transformation Services.

When I schedule a job to do the same I get the following error. Table exists already in S. How do I drop the tables in S before importing the new A and not losing the keys?

Ranjith

 

[dbomrrsm]

Do a select from the access table with a where cause that the key doesnt already exist in S

select * from A
where key not in (Select key from S)

[blue]DBomrrsm[/blue]

 

[donutman]

Why do you lose the keys? If it's a datatype conversion issue, then why not create a Key Conversion table in Access that can be used to transform the Access key to a SQL Server key. Then the DTS job can do the entire job.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[chrissie1]

why not use a linked server and copy the data with an insert and/or update statement?

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[SQLSister]

If you need the data in both Access ands SQL server, why not just use a SQL Server table and link it in Access? Then you only need to enter the data once.

But there is no reason why you should lose the keys in a properly set-up DTS package. And yes, now that you have initially populated the data, I would only import changed or new data if you havea way of telling what the changed data is. Usually you do these types of imports by using a linked server or by bringing the data into a holding table and then using the execute SQL task in DTS to perform the work. Do the updates first, then the inserts and then the deletes.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief