Change Field Size in BE table via Code

[Gibson]

I'm using the following code in an attempt to increase the field size of
Field1 in backend table flkpTable from 30 to 32. The field data type is Text.
The code runs through without an error but does not change the size of the
field. When I check the field size it remains at 30. I obviously missed
something. Help!

Set wrkDefault = DBEngine.Workspaces(0)
Set dbsUpdate = wrkDefault.OpenDatabase(DatabasePath, True)
Set tdfUpdate = dbsUpdate.TableDefs("flkpTable")
With tdfUpdate
Set tdfField = .Fields("Field1")
tdfField.Properties.Append tdfField.CreateProperty("Field Size", dbText, 32)

End With
Set wrkDefault = Nothing
db.Close
Set db = Nothing

 

[Douglas J Steele]

You can't use DAO to change the size of an existing field.

You either need to add a new field of the correct size, run an Update query
to populate that new field, delete the old field, then rename the new field,
or, if you're using Access 2000 or newer, you can use the Alter Table Alter
Column DDL syntax. I believe it would something like:

ALTER TABLE flkpTable ALTER COLUMN Field1 Char(32)

 

[Gibson]

Thanks for the reply Douglas,
I tried your suggestion and received this error:
Cannot execute data definition statements on linked data sources.

I assume it is because the table is in a linked backend database. Any
suggestions on how to reference the linked table in the ALTER TABLE
statement? I've tried a numaber of ways with no luck.

Thanks

 

[Douglas J. Steele]

How are you trying to run the SQL?

Using DAO, you'd need to instantiate a Database object that points to the
back-end database and use the Execute method of that object:

Dim dbBackend As DAO.Database

Set dbBackend = OpenDatabase(<full path to back end database>)
dbBackend.Execute strSQL

Using ADO, your Connection string needs to point to the back-end database

 

[Gibson]

Brilliant! You nailed it. Thanks so much for your help. Now go have yourself
a Moosehead. I sure am. Thanks again.