Change text field width with vba

[FuzMic]

Hi mates

I have an existing table with a text field that i want to change its width using vba. Can someone show some codes. Thanks in advance.

 

[The_Doc_Man]

Depends on version of Access, but MIGHT be as simple as

Tables("my-table-name").Fields("my-field-name").Size = new-integer-field-size

Or of course instead of quoted literal names you could have the appropriate values in string variables.

WARNING: Shortening the size of a populated text field will pop up a warning that you might truncate (and therefore lose) data. Also, I would not swear to this on a stack of encyclopedias, but I think the table cannot be opened by any Access object at the time, such as queries, forms, reports, etc. I think while it is open, the tabledef and fielddef objects are locked.

 

[sneuberg]

Also

CurrentDb.Execute "alter table NameOftable alter column NameOfField Text(25)"

Changes the field with to 25.

 

[The_Doc_Man]

FuzMic ...

I gave you the VBA solution.

Steve gave you the DDL (Data Definition Language) solution.

With Access there are frequently multiple ways to skin a cat.

{MMMEEEEEOOOOOWWWWRRRR!!!!}

 

[FuzMic]

THANK YOU great guys, short & sweet!

 

[jdraw]

You can substitute your tableName and ColumnName and use this

Sub tsql()
Dim sql  As String
sql = "ALTER TABLE[COLOR="DarkOrchid"] tblImages[/COLOR] Alter Column [COLOR="DarkOrchid"]IMagePath text(250)[/COLOR];"
CurrentProject.Connection.Execute sql, dbFailOnError
End Sub

Ooops: Those guys are fast...............................................poor cat.

 

[The_Doc_Man]

JDraw's method DOES have one advantage. If you do the .Execute of SQL or DDL and something goes wrong, there is an implied automatic rollback AND you will get a trap telling you that something went wrong.

Steve omitted the dbFailOnError parameter so this could simply fail quietly.

My method doesn't have a rollback, though if there is another error, you would get a trap.

 

[sneuberg]

I don't believe any of the methods we came up with work on a linked table. If the table is in the backend you would need something like:

Sub tsql()
Dim sql  As String
Dim db As DAO.Database
Set db = OpenDatabase(GetBackendPath)
sql = "ALTER TABLE [COLOR="Blue"]NameOfTable[/COLOR] Alter Column [COLOR="Green"]NameOfField[/COLOR] text([COLOR="red"]25[/COLOR]);"
db.Execute sql, dbFailOnError
db.Close

End Sub

Public Function GetBackendPath()

GetBackendPath = Split(Split(CurrentDb.TableDefs("[COLOR="blue"]NameOfTabl[/COLOR]e").Connect, "Database=")(1), ";")(0)

End Function

Where NameOfTable is the name of the linked table, NameOfField the field name and 25 would be the new size.

 

[The_Doc_Man]

Good point, Steve!

 

[FuzMic]

GREAT, GREAT lesson, i used the set to point to a non linked table.