How to turn off autonumber via vba. 1

[generallyconfused]

Hello all,

It is possible to change an autonumber fields datatype to a number (long integer) via the access gui under table design.

My question is how would I do the same thing via vba code, preferably with ADO, to a dbase that is not open. I was thinking of something like this.....

First off, this code assumes there is a access dbase at C:\Temp.mdb that contains a table named "Test" that has the first field set as an autonumber.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp.mdb;"
conn.Open
rs.Open "Test", conn
rs.Fields(0).Type = adBigInt
rs.Update
rs.Close
conn.Close

It doesn't work though, but I hope you guys get the gist of what I'm attempting. The type property seems to be read only. I figure there must be a way to do it since it can be done through the gui.

Thanks,
GC

 

[vbajock]

You can use ADOX catalog library to change field types,etc. Pretty deep subject for a forum, I recommend you get a book, like WROX's Beginning ADO 2.6. There is a bunch of sample code on it at the MSDN web site as well. Type ADOX in the search box and choose Code examples.

 

[generallyconfused]

Everything I've read so far implies that once a table is created, the properties of its fields can only be read, not written.

Does this mean I will have to recreate the table, it's relationships, and all of its data JUST to turn off the "Autoincrement" property?

That's seems like a heckuva lotta work considering it is so simple to accomplish through the gui.

Please help.

PS vbajock. I tried this but it still would not work (I think because the property is read only)....

Dim conn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp.mdb;"
conn.Open
Set cat.ActiveConnection = conn
Set col = cat.Tables("Test&quot.Columns(0)
MsgBox col.Type
MsgBox col.Name
MsgBox col.Properties("Autoincrement&quot
col.Properties(&quot;Autoincrement&quot = False <-Errors out here
Set col = Nothing
Set cat = Nothing
conn.Close

The msgboxes return the correct responses. So I'm grabbing the table, I just can't change it.

 

[p27br]

try this : col.Type = adInteger

 

[vbajock]

Your syntax looks correct. What is the error you are getting?

 

[p27br]

Sorry I answered too quickly.
I think you can't change this property via ADOX. There is a simpler way :

run this SQL statement: ALTER TABLE test ALTER COLUMN columnname LONG

 

[vbajock]

p27br, that works great. Have a star!

 

[generallyconfused]

You guys rock! Thanks!

 

[p27br]

thanks for the star guys !