Help me in SQL SERVER to MS ACCESS
Posted on 2003-11-11
I have a shopping web site done in asp with MS Acess as database
I have a Inventory system done by another person, in VB with SQL server as back end.
Now I want like this:
At the end of Each day,whatever the products that are entered in the VB system (backend sql server),that has to be reflected in the MS Access database.The DB structure of SQL SERVER and MSACCESS database are different totally.
In the MS ACCESS Database called
there are two table called 'products' and 'comp_note'
In the VB -> SQL SERVER ,there are no such tables
The main thing is that ,i have to create four columns newly in my MS ACCESS tables(on both the tables)
I have to take info on those 4 columns from that SQL Server DB.
Along with that whatever product is added in future in that inventory system, i have to append it in
my web database of MS Access
SO what im thinking of to do is :
(For a understanding of my view)
1.Using DTS to import the two tables,'products' and 'comp_note'( in the MS Access-latest version of the table.This includes
the extra four columns.)
This way the two tables would be created and records willl be stored in the SQL server.
2.when adding new records in the VB system,the columns required for these two tables would be actually
coming from different tables(which are already existing).so i would select the required columns and then
insert the data into the newly created two tables.
3.Then i will create a Upload button which should do the conversion of the two tables in SQL server
to MS ACcess and that has to be uploaded to a directory inside the site.This two tables has to be uploaded on a daily
basis.This is how the requirement is.
Can we use addlinked server concept here? I dont have knowledge on this.please help me out
Can any one tell me how to convert those tables alone into MS Access database and how to upload that without using FTP
sort of things ?
or is there any other way ?
please help me out.Thanks