Help with ADO Execute command(Ex.db.Execute("Q_Matts_Qu

[wren17]

Hello everyone,

I am having a problem with executing Make-Table queries from VBA. The problem comes when I run the query and it tries to make a table that already exists. Normally if I were running the query manually. I would get the option to overwrite the existing table.

My question is: Does anyone know a way to execute a Make-Table query so that it will always overwrite the existing table instead of exitiing with an error.

Thanks,
Wren

 

[dk]

Re: Help with ADO Execute command(Ex.db.Execute("Q_Matt

Hi,

You'll need to delete the table first e.g.

On Error Resume Next
CN.Execute "DROP TABLE YourTableName"

'Your create table code


The on error statement is required in case the table doesn't already exist (the DROP TABLE statement will fail otherwise).

 

[bat17]

You will create bloat in your database if you keep creating and deleting tables. It is better to just delete the existing records and append the new if you can.

Peter

 

[SydneyGeek]

Re: Help with ADO Execute command(Ex.db.Execute("Q_Matt

Peter is right about the bloat -- but you can deal with that by regularly compacting the database (essential for the health of the DB anyway)

Denis

 

[dk]

You will create bloat in your database if you keep creating and deleting tables. It is better to just delete the existing records and append the new if you can.

Peter

You'd also experience bloat using this method. As Denis has pointed out the only way to avoid this is to compact regularly.

 

[bat17]

Deleting records also has the advantage of not disturbing any relationships or indexes created on the tables

Peter