dynamically modifying a column in a table

[ChewDoggie]

G'day all!

I'm a novice, so bear with me. We have a VB application that's already been sent out to the end users (about 600 of them) and just discovered that one of the columns in the underlying ACCESS table is the wrong length and it's causing many errors to occur (users aren't happy -- go figure!).

We have the ability to download updates to their tables via connections to our server. We need to modify a column in a table, making the length 4 chars instead of 3. Can someone help me with the syntax for doing this? The column will NOT have any default values but sometimes the column will be an indexed key, and other times a non-indexed key. Sometimes the column will not be a key at all.

I don't know the syntax for these various idiosyncrasies. Can someone help me?

THanks !!!


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Remou]

How about Alter Table:
[tt]strSQL = "Alter Table tblTable Alter Column Field1 Char(4)"
DoCmd.RunSQL strSQL[/tt]

 

[ChewDoggie]

Does that change any of the properties of the column? OR are the properties preserved?

Thanks! I'll start there.


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

Sorry...

Does that change any of the OTHER properties of the column? OR are the OTHER properties preserved?


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Remou]

I did not test exhaustively, but the indexes I tried with were preserved. I tried with a primary key and an index.

 

[ChewDoggie]

I tried that syntax as a query in Access and it worked. However, when I try to execute that command in my code, I get a syntax error. The query string looks like this:

Alter table Grid alter column Comp char(4)

and the error message states, "Syntax Error in ALTER TABLE statement". I'm executing the command using cn.execute sql, does that make a difference?

Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

....Also, I just noticed that all of the keys in the table are now gone.


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Remou]

I ran the code below in Access 2000. I used a table having three text fields set to a length of 3:
[tt]Fields Index
Comp Key
Field2 Yes (Duplicates OK)
Field3 No[/tt]
I altered the length of each field in turn from 3 to 4, the indexes were not affected.
If you want to include more detail, I will try again matching your set-up more closely, if possible.

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim conDatabase As ADODB.Connection

Set conDatabase = New ADODB.Connection
conDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Tek-Tips.mdb;"
strSQL = "Alter table Grid alter column [Comp] char(4)"
    
Set rs = New Recordset
rs.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

Set rs = Nothing
conDatabase.Close
Set conDatabase = Nothing

Comp is a reserved word and needs to be bracketed:
SQL Reserved Words

http://office.microsoft.com/en-us/assistance/HP010322491033.aspx

 

[ChewDoggie]

YOU ARE A GOD !!!!!!!!!!! It worked !!! Thank YOU !!!



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org