October 11, 2005 at 10:57 pm
Hi,
I have table tabX, in MS SQL, with Columns Col_1, Col_2 and Col_3. The values in Col_1 are 'A', 'B', and 'C'. I would like to write SQL that will:
1) Create a MS Access DB called A.mdb
2) Create a table called 'data_dump' which contains Col_2 adn Col_3
3) Export the data from tabX to the newly crewated data_dump in a.mdb
4) Repeat this for 'B' and 'C'... and as new ones are added, it will automatically create new Access DB's.
Is this possible, how can i write SQL that will create the MS Access DB and secondly Export from the MS SQL table to Access table?
Any helps would be appreciated.
Chris
October 11, 2005 at 11:16 pm
Create DTS in your SQL to export data from your SQL to access, you can use a DTS wizard to do this.
October 11, 2005 at 11:54 pm
Thanks for your reply Deni.
This would work if the number of Access Database I needed to produce was fixed. However, I need to produce a separate Access Database for each value in Col_1.
October 20, 2005 at 10:48 am
You could have a working SQL-Server database created to take the results of your queries and have a generic Access database name that your dts runs against to dump data from the Working SQL-Server database and the generic Access database.
Run your queries that goes to one of the databases, use a DOS copy of the access database to the name you desire, copy a blank Access database over top of the generic Access database and process the next one. I use perl scripts for my application and I pass the name of the access database to the copy command.
I hope this makes sense.
Steve
October 20, 2005 at 4:57 pm
Hi Steve,
Many thanks for your reply. The requirements have changed now so that it is only csv's but I think your idea will work nicely for either requirement.
Regards,
Chris
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply