Increasing Data Field Length

[Dwight]

Is there a way to increase a data feild length using code?

I have an application that has a front end application and back end
database. I would like to add a button on a form that allows a user to
increase the length of the corresponding data field.

Thanks in advance!

Dwight

 

[Allen Browne]

You can modify the Size of a Text field by executing a DDL query like this:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

If the table is linked from another MDB, you will need to OpenDatabase, and
Execute the query statement on that database, not the current one.

 

[Dwight]

The table is linked from another MDB. Can this all be done by using a button
on a form from the front end application? The back end database is locked to
user

Thanks!

Dwight

 

[Allen Browne]

Search help for the keywords already given, i.e.:
OpenDatabase
Execute

 

[Dwight]

Thanks Allen!

I am getting closer, but when I execute this code I get the following error:

***************
Private Sub UpdateFieldPosition_Click()

Dim dbs As Database
Dim strSQL As String

Set dbs = OpenDatabase("C:\ResumeBuilder2.0\ResumeBuilder_data.mde")
strSQL = "ALTER TABLE Header ALTER COLUMN Position TEXT(100);"
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing

End Sub
******************

ERROR:
The database engine could not lock the table 'Header' because it is already
in use by another user or process.

Your assistance would be greatly appreciated.

Dwight

 

[aaron.kempf]

Dwight
you should just set this to a reasonable number at development time

I really reccomend using Access Data Projects instead of this MDB
crap.. you can always change objects on the db server, unlike this MDB
crap that gives u random 'file in use' errors


hth

-Aaron

 

[Allen Browne]

Okay: so the locking error indicates that the table is already in use.

Perhaps something (form? report? query? combo?) is using data from this
table at the time when you are attempting to run this code?

 

[i_takeuti]

Thanks Allen!

I am getting closer, but when I execute this code I get the following
error:

***************
Private Sub UpdateFieldPosition_Click()

Dim dbs As Database
Dim strSQL As String

Set dbs = OpenDatabase("C:\ResumeBuilder2.0\ResumeBuilder_data.mde")
strSQL = "ALTER TABLE Header ALTER COLUMN Position TEXT(100);"
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing

End Sub
******************

ERROR:
The database engine could not lock the table 'Header' because it is
already
in use by another user or process.

Your assistance would be greatly appreciated.

Dwight