RE: Importing Access to MSSQL
I use the DTS package designer wizard in SQL to create a DTS script for this.If you must drop the tables each night then the procedure is: drop tables create tables (no indexes or constraints) copy data in -AT this step can do many things including clean up the data, merge the data etc. Run your script to build indexes, keys, constraints and views etc. Run any clean up scripts you like. All of this - including your FTP task - can be automated using a DTS package. -Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 10:41 PM To: CF-Talk Subject: Re: Importing Access to MSSQL i use this, pretty slick, takes about 30 seconds http://www.data-conversions.net/access2mysqlpro.html Several ways to do this. Use the upsizing wizard -- but I'm told that there are problems using that approach for data -- okay to get the table structures in place I think. Alternatively, create the new tables manually in MSSQL. Once the tables are in place, use CF to query the Access Table and then use CF to Insert recrods into MSSQL table. Another way is to use a Windows app in client-server mode with ODBC drivers to do the Query and Post process -- I use Visual FoxPro and ODBC drivers for this purpose a lot irrespective of what the from and to databases are (Excel, Text, Access, MySQL, MSSQL, Oracle, etc). Both of these processes allow for a lot of data checking and cleanup to be done in the migration process because of FoxPro's extensive string and date manipulation functions.Processing Datetime fields can be a bugger to deal with especially going from other than Access to MSSQL. To give you some performance numbers, I deleted and then created a new MSSQL table located at a hosting service from a local 10,000 record FoxPro DBF file -- the process took about 20 minutes over a 56k modem. Just another two cents -- hope it helps... [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Importing Access to MSSQL
Have you compressed the access database?I don't know if there is a way to programmatically do this, but it can dramatically reduce the size of an access file.Hardly the solution you're looking for, but it might be good enough to buy you some time. Cheers, barneyb -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 1:16 PM To: CF-Talk Subject: Importing Access to MSSQL Hello All, I am not really experienced in MSSQL but I have been notified by my hosting provider that I can't keep using access on their server due to the size of my access databases.My problem is that I have an access database sent via FTP to my server overnight that is created from an application that I do not have access to. My Question is how difficult is it to take the Access database that the over night process sends me, import it into MSSQL(deleting all the records first) with CF, then delete the Access database that was FTP'd to me with CFFILE? It sounds easy enough I just have no clue how to go about it Can anyone point me in the right direction? Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Importing Access to MSSQL
Tried that.They are still complaining.The database is actually compacted everynight, that bough me time the last time :) - Original Message - From: Barney Boisvert To: CF-Talk Sent: Tuesday, April 06, 2004 4:30 PM Subject: RE: Importing Access to MSSQL Have you compressed the access database?I don't know if there is a way to programmatically do this, but it can dramatically reduce the size of an access file.Hardly the solution you're looking for, but it might be good enough to buy you some time. Cheers, barneyb -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 1:16 PM To: CF-Talk Subject: Importing Access to MSSQL Hello All, I am not really experienced in MSSQL but I have been notified by my hosting provider that I can't keep using access on their server due to the size of my access databases.My problem is that I have an access database sent via FTP to my server overnight that is created from an application that I do not have access to. My Question is how difficult is it to take the Access database that the over night process sends me, import it into MSSQL(deleting all the records first) with CF, then delete the Access database that was FTP'd to me with CFFILE? It sounds easy enough I just have no clue how to go about it Can anyone point me in the right direction? Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Importing Access to MSSQL
Several ways to do this. Use the upsizing wizard -- but I'm told that there are problems using that approach for data -- okay to get the table structures in place I think. Alternatively, create the new tables manually in MSSQL. Once the tables are in place, use CF to query the Access Table and then use CF to Insert recrods into MSSQL table. Another way is to use a Windows app in client-server mode with ODBC drivers to do the Query and Post process -- I use Visual FoxPro and ODBC drivers for this purpose a lot irrespective of what the from and to databases are (Excel, Text, Access, MySQL, MSSQL, Oracle, etc). Both of these processes allow for a lot of data checking and cleanup to be done in the migration process because of FoxPro's extensive string and date manipulation functions.Processing Datetime fields can be a bugger to deal with especially going from other than Access to MSSQL. To give you some performance numbers, I deleted and then created a new MSSQL table located at a hosting service from a local 10,000 record FoxPro DBF file -- the process took about 20 minutes over a 56k modem. Just another two cents -- hope it helps... [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Importing Access to MSSQL
i use this, pretty slick, takes about 30 seconds http://www.data-conversions.net/access2mysqlpro.html Several ways to do this. Use the upsizing wizard -- but I'm told that there are problems using that approach for data -- okay to get the table structures in place I think. Alternatively, create the new tables manually in MSSQL. Once the tables are in place, use CF to query the Access Table and then use CF to Insert recrods into MSSQL table. Another way is to use a Windows app in client-server mode with ODBC drivers to do the Query and Post process -- I use Visual FoxPro and ODBC drivers for this purpose a lot irrespective of what the from and to databases are (Excel, Text, Access, MySQL, MSSQL, Oracle, etc). Both of these processes allow for a lot of data checking and cleanup to be done in the migration process because of FoxPro's extensive string and date manipulation functions.Processing Datetime fields can be a bugger to deal with especially going from other than Access to MSSQL. To give you some performance numbers, I deleted and then created a new MSSQL table located at a hosting service from a local 10,000 record FoxPro DBF file -- the process took about 20 minutes over a 56k modem. Just another two cents -- hope it helps... [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
