If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change field size with code
I am trying to change all the text fields in my tables with a certain name to
a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
#2
|
|||
|
|||
Change field size with code
You cannot alter the size of the field using DAO.
In JET 4 (Access 2000 and later), you can execute a DDL statement to do it. This kind of thing: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);" DBEngine(0)(0).Execute strSql, dbFailOnError Using DAO, you have to CreateField() of the new size, execute an UPDATE query to populate it, and then remove the old field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rbm" noemail@please wrote in message ... I am trying to change all the text fields in my tables with a certain name to a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
#3
|
|||
|
|||
Change field size with code
Thank you for the quick response. I just figured that if I could do it with
AllowZeroLength, I could do it with FieldSize. I am using Access97 so I guess I am stuck with changing each one individually. Thanks again. "Allen Browne" wrote: You cannot alter the size of the field using DAO. In JET 4 (Access 2000 and later), you can execute a DDL statement to do it. This kind of thing: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);" DBEngine(0)(0).Execute strSql, dbFailOnError Using DAO, you have to CreateField() of the new size, execute an UPDATE query to populate it, and then remove the old field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rbm" noemail@please wrote in message ... I am trying to change all the text fields in my tables with a certain name to a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
#4
|
|||
|
|||
Change field size with code
The DDL will probably work for A97 also. If you have syntax problems, look
in Access help. Open the table of contents and drill down. There should be an entry for SQL or something like that. Within that you should find a section on DDL. "rbm" noemail@please wrote in message ... Thank you for the quick response. I just figured that if I could do it with AllowZeroLength, I could do it with FieldSize. I am using Access97 so I guess I am stuck with changing each one individually. Thanks again. "Allen Browne" wrote: You cannot alter the size of the field using DAO. In JET 4 (Access 2000 and later), you can execute a DDL statement to do it. This kind of thing: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);" DBEngine(0)(0).Execute strSql, dbFailOnError Using DAO, you have to CreateField() of the new size, execute an UPDATE query to populate it, and then remove the old field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rbm" noemail@please wrote in message ... I am trying to change all the text fields in my tables with a certain name to a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
#5
|
|||
|
|||
Change field size with code
Thanks. I will look there.
"Pat Hartman(MVP)" wrote: The DDL will probably work for A97 also. If you have syntax problems, look in Access help. Open the table of contents and drill down. There should be an entry for SQL or something like that. Within that you should find a section on DDL. "rbm" noemail@please wrote in message ... Thank you for the quick response. I just figured that if I could do it with AllowZeroLength, I could do it with FieldSize. I am using Access97 so I guess I am stuck with changing each one individually. Thanks again. "Allen Browne" wrote: You cannot alter the size of the field using DAO. In JET 4 (Access 2000 and later), you can execute a DDL statement to do it. This kind of thing: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);" DBEngine(0)(0).Execute strSql, dbFailOnError Using DAO, you have to CreateField() of the new size, execute an UPDATE query to populate it, and then remove the old field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rbm" noemail@please wrote in message ... I am trying to change all the text fields in my tables with a certain name to a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
#6
|
|||
|
|||
Change field size with code
Pat Hartman(MVP) wrote: The DDL will probably work for A97 also. Yes, it will work on a Jet 3.x file format but you have to use the OLE DB Provider for Jet 4.0. The OLE DB Provider for Jet 3.51 simply does not have the syntax. |
#7
|
|||
|
|||
Change field size with code
There's no reason you can't automate Allen's suggestion of using
CreateField() to create the new field, executing an UPDATE query to populate it, and then removing the old field. Something like the following untested air-code: Sub ChangeFieldSize(TableName As String, FieldName As String) On Error GoTo Err_ChangeFieldSize Dim dbCurr As DAO.Database Dim tdfCurr As DAO.TableDef Dim strSQL As String Set dbCurr = CurrentDb() Set tdfCurr = dbCurr(TableName) With tdfCurr ' Rename the existing field ' (put the word "Old" in front of the existing name) .Fields(FieldName).Name = "Old" & FieldName ' Create the new field .Fields.Append .CreateField(FieldName, dbText, 33) End With ' Update the new field with values from the old field strSQL = "UPDATE [" & TableName & "] " & _ "SET [" & FieldName & "] = [" & "Old" & FieldName & "]" dbCurr.Execute strSQL, dbFailOnError ' Delete the old field tdfCurr.Fields.Delete "Old" & FieldName End_ChangeFieldSize: Set dbCurr = Nothing Exit Sub Err_ChangeFieldSize: MsgBox Err.Number & ": " & Err.Description Resume End_ChangeFieldSize End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "rbm" noemail@please wrote in message ... Thank you for the quick response. I just figured that if I could do it with AllowZeroLength, I could do it with FieldSize. I am using Access97 so I guess I am stuck with changing each one individually. Thanks again. "Allen Browne" wrote: You cannot alter the size of the field using DAO. In JET 4 (Access 2000 and later), you can execute a DDL statement to do it. This kind of thing: Dim strSql As String strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);" DBEngine(0)(0).Execute strSql, dbFailOnError Using DAO, you have to CreateField() of the new size, execute an UPDATE query to populate it, and then remove the old field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rbm" noemail@please wrote in message ... I am trying to change all the text fields in my tables with a certain name to a size of 33 characters using a module. It worked for setting AllowZeroLength, but a similar code to change the field size returns an error 3219 "Invalid Operation". The code runs through the tables and fields ok, but hangs up on the line that sets the field size to 33. Any help would be appreciated. The code I am using is this: Public Function ChangeTxtFldSize() On Error Resume Next Dim i As Integer, J As Integer Dim db As Database, td As TableDef, fld As Field Set db = CurrentDb() For i = 0 To db.TableDefs.Count - 1 Set td = db(i) For J = 0 To td.Fields.Count - 1 Set fld = td(J) If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then fld.Size = 33 End If Next J Next i db.Close Set db = Nothing End Function |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to send emails to myself and email addresses with same domain name? | andy | General Discussion | 7 | October 5th, 2005 07:24 PM |
Change a Field Size | JA Peachrose | General Discussion | 2 | September 27th, 2005 09:33 PM |
Intermitant failure to send e-mail in Outlook 2002 | Bob J | General Discussion | 6 | June 29th, 2005 04:47 AM |
error 550 won't deliver | theMooooo | General Discussion | 3 | March 29th, 2005 12:47 PM |
Outlook XP email goes to sent folder -receipient doesn't receive,. | Jobde | General Discussion | 3 | February 9th, 2005 04:29 PM |