Syntax error on ALTER TABLE query statement

[ranshe]

I am working in VB6 with and Access.mdb database.

I get a syntax error on the Alter Table query statement below. I have tried to change it but I cannot seem to beat the problem.

Using the .Open statement I have done selects/insert/drops/deletes and updates so that does not seem to be the problem.

Can you help me?

dbImportDB.Open "dbImportDB"
rsNewCampaign.Open "ALTER TABLE " & Trim(strCampName) & " RENAME TO " & strHistTblName, dbImportDB, adOpenDynamic, dLockOptimistic

 

[fredericofonseca]

you can not do that on a recordset object.

change rsnewcampaing to be your connection or a command object instead.

And where did you get that syntax "alter table RENAME to" Never seen it before.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gmmastros]

Instead of renaming a table, you could select into a new table, and then drop or delete all the data from the original table. Something along the lines of...

Select * Into NewTable From ExistingTable
Drop Table TableToDrop

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

I believe this clarifies what George is saying a bit (is that right, George?):

Select * Into NewTable From ExistingTable
Drop Table ExistingTable