Would like to get rid of prompt for copying a table to an existing table

[AshikHusein]

I have a macro which copies a table to a table which is already existing (ie macro to copy the table to an already existing table.) The idea is to update the table with new information. (Note that we are not appending the data but trying to get in completely new data into the existing table. Now when the macro is run, a box pops up telling that the table already exists and that whether it is to be replaced.

I have turned off the prompts by going to Tools->Options->Edit and it does not prompt for things like deleting tables but it does not seem to be working for copying tables to existing tables.

Would appreciate help as to how I could prevent this from happening or if there is a better way to copy new data froma table to another existing table wihtout getting such prompts. Thanks.

 

[FoFa]

Why not run a delete query then an append query instead?
Or
Delete the table first then run your Make Table query, use the macro DELETE OBJECT command.

 

[AshikHusein]

I am doing exactly as you have mentioned. But the problem is that the main file is being imported as a text file and the date fields are imported in "TEXT" format. The file that it is being copied on to has the same date field in the "DATE" format. Now I need the dates to be in a date format for analysis. If I delete the file that it is to be copied on to and then copy the newer version with the same name as that of the deleted file then the dates of the copied file are also in the "TEXT" format which messes up the reports.

Now the solution could be to import the text file with the date fields in the "DATE" format. Do you know of a way that this can be done? Thanks.

 

[FoFa]

Have you used the ADVANCED button n the import to specify the date format of you import file and see if it will convert it for you?
If that does not work, I would import it to a temp table, and convert the data as you move it to your main table via a query. You can setup a macro to do all the steps for you if it is a static file name.

 

[AshikHusein]

I am in the process of doing exactly the same thing as you have said. ie using the advanced TAB. Now there are certain fields which are time fields and are not converting to date/time. In the TEXT file they are like the following :

10:15:16 AM

The date field is showing no errors and seems to be converting well to date/time. ANy suggestions? I appreciate your help in the above matter. Thanks.

 

[FoFa]

Are you time formats not part of your date expresion?
What is the file format, delimited or fixed width?
Text quotes around the time field?

 

[AshikHusein]

The date is in a different field and the time is in a different field.

The width is delimited.

In the .txt file the time is in qoutes (ie "12:10:13 PM")

Thanks.