asked on

Updating MSsql from Access 2003

Hi guys

os=windows xp-pro
MSAccess 2003
MSSQL 2005 express

I have an Access database called 'customers.mdb'
I have an MSSQL database called 'customers'
Both databases contain only 5 tables each which are identical
I have custom accounting software which constantly updates customers.mdb

I need to update my mssql 'customers' database once a week from customers.mdb
What is the best way to do that?
Dory

julienwood

For the simplicity

1. Create A new ACCESS database name eg. DataUpload
2. Create 5 linked table which link to customers.mdb (eg. TABLEA1, TABLEA2 ....)
3. Create 5 linked table which link to MSSQL express (eg. TABLES1, TABLES2
4. Create 5 delete queries to delete old data in SQL express (eg. delete from TABLES1, delete from TABLES2 ....)
5. Create 5 insert queries to insert new data from Access to SQL express (eg. insert into TABLES1 select * from TABLEA1)
6. Create a macro to execute 5 delete queries first and 5 insert queries next

Now you have a new ACCESS database with a macro to manage your weekly update process.
Let me know if you need more detail.

dory550 lambert

ASKER

Julienwood:
Thanks for your help
I am fine an msaccess side
I createted the mdb file and linked to the 5 tables in customer.mdb
But I am kinda new to MSSQL
I created a system (SQL native client) odbc using windows integrated authentication
Connection was tested OK
but could not link from msaccess to MSSQL using it
When choosing ODBC as the linking method the linking wizard just closes
Any ideas?
Dory

ASKER CERTIFIED SOLUTION

julienwood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dory550 lambert

ASKER

julienwood

Your answered was very detailed
Everything worked like a charm
thank you
Dory