Create Table

[robWhitz]

Currently, I have a macro that does a couple of things.
1) It deletes an table (e.g. Table1).
2) Then it imports all the data into a new table call Table1.

However, when the macro is cancelled, I will have an error when I try to re-run this macro. Error occurs because the macro was not able to find Table1 to delete (step 1).

I was thinking of having a condition check here. Meaning if Table1 do not exist, create Table1. Then proceed with Step 1 and 2. If Table1 exists, proceed to Step 1 and 2.

How and where can I do it? Under modules or procedures? I need the macro to recognise this condition.

Thanks in advance.

 

[mikehoot]

FYI - The jet error code for this type of error is 3011. You could try trapping this error, and using IF to create table if it is missing.

If you prefer using macros, try this.

Create a blank table called something like tblBlank

In your macro, insert two lines at the beginning.

1. Setwarnings = NO
2. Copyobject - New Name - tblTable1
Source Object Type - Table
Source Object Name - tblBlank

and then continue your macro.

This will create your table, and if it already exists will replace it avoiding the error messages like "are u sure?"

Good Luck

 

[LonnieJohnson]

Here are couple of more suggestions...

1. Run a Make A Table query and it will just over-write the existing table. No need to delete it or worry if it isn't there.

2. Run a Delete Query to delete all the records and not the table itself, then run an Append Query to add new records.

Just a couple of thoughts.
ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[robWhitz]

Thanks, mikehoot. It works just fine. I have implemented that and using it without a problem. )

 

[robWhitz]

Hi LonnieJohnson. You first suggestion is great. But I have quite a number of tables to delete, so that means I have to maintain a couple of make-table queries. I will keep this in mind for my future works.

As for the second suggestion, I have a counter in the table. So, if I were to delete the records in the table, re-import the tables, my counter will continue from the last used one. So... I decided not to use this.

In any case, thanks for the suggestions. It just goes to show that there are many different ways to achieve the same objective.