SQL: ALTER TABLE problem 1

[RonMcIntire]

All:

I'm trying to delete the data from a table and reset the autonumber index back to 1 but I'm having trouble.

Everything I read says I can use ALTER TABLE to do it but it doesn't seem to work for me.

Here's my code:

Private Sub nResetCtlNo_Click()

Dim SQL_Text As String

'Delete the data from table tblSVOrders
DoCmd.RunSQL "Delete*from tblSVOrders"

'Drop the index from the table
DoCmd.RunSQL "ALTAR TABLE tblSVOrders DROP COLUMN CtlNo"

'Add a new index column to the table
DoCmd.RunSQL "ALTAR TABLE tblSVOrders ADD COLUMN _ CtlNo autonumber"

End Sub

I keep getting the error "Invalid SQL statement. Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

What am I doing wrong?

Appreciate all the help I can get.

Ron

 

[AlexCuse]

Is 'ALTAR' a typo in your post, or your query?

Ignorance of certain subjects is a great part of wisdom

 

[Golom]

As AlexCuse says ... it's ALT[red]E[/red]R.

And your comments say you are dropping and adding INDEXES but you are actually dropping and adding columns. Check out the CONSTRAINT clause for ALTER TABLE ... ADD COLUMN if you want the column to be an indexed column.

 

[RonMcIntire]

Alex & Golom:

Thank you both for your response.

Ooops! My post and code were both typos and I did mean column, not index.

I got the DROP COLUMN statement to work now but the ADD COLUMN is still a problem. I've read the CONSTRAINT clause help a number of times but still don't fully understand it. Here's what I have:

DoCmd.RunSQL "ALTER TABLE tblSVOrders ADD COLUMN CtlNo long interger CONSTRAINT UNIQUE"

What I want to do is use CtlNo as an autonumber type field. Ooops! I just realized it's not necessary to index it. I don't know if the CONSTRAINT clause can be used this way or even if there is a need for it. Somehow, the field seems like it should be defined as being unique. When I change the field type to "Long", the statement works but the field is placed near the bottom of my table column definitions and the column is not autogenerating.

When I try the code, I get an error message that now says I have a syntax error in the ALTER TABLE statement. Man, this is really confusing to me.

Ron

 

[PHV]

What about simply this ?
DoCmd.RunSQL "DELETE * FROM tblSVOrders"
DoCmd.RunSQL "ALTER TABLE tblSVOrders ALTER COLUMN CtlNo COUNTER(1,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonMcIntire]

PHV:

That sounds like a great idea and simple too. However it still generates a "syntax error in the ALTER TABLE statement."

I've read the "Comparison of Data Types" help but what they are trying to say is not clear--yet.

Ron

 

[PHV]

Which version of access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonMcIntire]

Access 97.

Ron

 

[PHV]

OK, your version of JetSQL doesn't handle well DDL.
Either upgrade to a more decent version or delete all the records and then compact the database to reset the autonumber.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonMcIntire]

PHV:

I was afraid of that. I can use Access 2003 but I had hoped to stay with '97 at least for a while longer for the useability by my successors.

1. Any chance there's a later version of JET that will handle the problem?

2. Is there a chance I can handle compacting of the same application programmatically?

3. Is it even possible in A97?

4. How about replacing my table with a blank copy of a table structure with 0 records and a control number beginning with 1?

5. I even thought about a recordset DefTable but I'm out of my league on this one.

Ron

 

[PHV]

1. JetSQL 4.0 admits the "ALTER TABLE ... ALTER COLUMN ..." DDL instruction.
2.

http://www.mvps.org/access/general/gen0041.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonMcIntire]

PHV:

I may have a solution. I copied the table structure to a new table called "tblSVOrders - Structure" then created a make-table query to overwrite the existing "tblSVOrders." It works pretty slick and does what I want it to do. Time will tell if there's a problem down the hill somewhere.

I'll have to be carefull and not delete the structure file.

Any comments?

Ron

 

[PHV]

With the make table query (SELECT ... INTO ... FROM ...) you loose the index.
You may consider this:
DoCmd.runSQL "DROP TABLE tblSVOrders"
DoCmd.CopyObject , "tblSVOrders", acTable, "tblSVOrders - Structure"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonMcIntire]

PHV:

That sounds good too.

My application involves downloading emailed telephone orders for Singing Valentines from a calling service to an interim table, cleaning up the data a bit then appending the new data to the main table "tblSVOrders." We only do this once a year so I need to start with a fresh "tblSVOrders" table every year.

Dropping the old table and copying a new object whether the way I describe or by your method will do the trick nicely. Personally, I like your way better. It seams a little safer somehow.

Thanks for your time and helping me sweat this one out. Thanks to Alex and Golom too for your input.

Ron