Changing table design properties with a module

[PPSAG]

I have a database with a field in 4 different tables that needs to be altered when data is imported. The field is text - 5 and needs to be 8. We can't just redesign the table, it has to occur when new data is loaded, the data comes in with a five character field and we add three characters. We're trying to do this with a module so that it can be executed easily by data entry personnel but we are having no luck.

Mooo...

 

[BillPower]

This should do what you want as long as the Table(s) are in the Current DB:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "ALTER TABLE YourTableName1 ALTER COLUMN YourFieldName TEXT(8);"
DoCmd.RunSQL (strSQL)
strSQL = "ALTER TABLE YourTableName2 ALTER COLUMN YourFieldName TEXT(8);"
DoCmd.RunSQL (strSQL)
strSQL = "ALTER TABLE YourTableName3 ALTER COLUMN YourFieldName TEXT(8);"
DoCmd.RunSQL (strSQL)
strSQL = "ALTER TABLE YourTableName4 ALTER COLUMN YourFieldName TEXT(8);"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

Change YourTableName1-4 with you Table Names and the same with YourFieldName.

I would test this first on a Backup Copy of your DB to make sure it does what you want. Place the code in the On Click event of a Command Button. Coincidentally, I answered an almost identical question earlier in thread702-554347 .

Regards

Bill

 

[PPSAG]

Thanks for the response. It isn't working though. I'm not getting any errors, but the field size isn't changing either. Am I missing something else?

Mooo...

 

[BillPower]

Can you post your Code please.

This is very odd because if something was going wrong, you should get an error. Common Errors:

Incorrect Table Name
Incorrect Field Name
Table Locked by another User
Table is Linked

Bill

 

[PPSAG]

My code is exactly like yours, except of course with the correct field names. I did mis-spell 'column' the first time, but I corrected that and still get no change in my field.
Since I work for a state government agency, we're behind in our technology. I am using 97, and thought maybe that was the issue, but shouldn't I still get an error message?

Mooo...

 

[BillPower]

As Aqif pointed out in Thread702-554347 this Method won't work in '97.

Yes you should have got an error message.

I suggest using Aqif's Function or posting your question again in the above Thread.

Bill