Upload Access DB then convert to SQL

[mach10]

I need to be able to take my Access Database and upload it to my website (this
will happen every work day). After it is uploaded I need to take the Access
table and insert it into my SQL database. The SQL table will need to be dropped
and then recreated again with the new information, or all of the rows will need
to be deleted before inserting the new data. I'm uploading all the records not
just new records, so that is why everything in that table needs to be deleted
first.

Does anyone know how to do this? I'm not sure what I need to do after I upload
the Access database to my server. Any help in taking the Access table and
inserting it into my SQL database would be greatly appreciated!!!!!

Thanks in advance!!!!

[Dan Bracuk]

Is there a reason you can't simply use your Access database?

If not, can you access the sql db from outside the web server? If so, put a
linked table into your Access db and run your queries from it.

Originally posted by: mach10
I need to be able to take my Access Database and upload it to my website (this
will happen every work day). After it is uploaded I need to take the Access
table and insert it into my SQL database. The SQL table will need to be dropped
and then recreated again with the new information, or all of the rows will need
to be deleted before inserting the new data. I'm uploading all the records not
just new records, so that is why everything in that table needs to be deleted
first.

Does anyone know how to do this? I'm not sure what I need to do after I upload
the Access database to my server. Any help in taking the Access table and
inserting it into my SQL database would be greatly appreciated!!!!!

Thanks in advance!!!!

[paross1]

You might want to look into using DTS (Data Transformation Services) in your
SQL Server for this. You can write packages to drop and create tables, then
import the data, etc. You can then schedule this package to be run as a job at
whatever time you see fit.

Phil

[ksmith]

Hi,
It sounds like some of the alternatives mentioned may make more sense than
what you have described. But to do what you want:
Upload the access file to a location you want.
Create a DSN in the cfadmin for it. (Make sure maintain connections is not
checked.)
Upload your new file as needed.
Do a drop table or truncate in a cfquery.
Recreate the table and add your data