Changing a Access Database field size using code

[spacey]

I need to change a existing Access97 database table with data in it. I need to make the field size larger. I know Access97 can do this but I need VB code for a rollout to multiple sites. I tried using the ALTER TABLE SQL command but it doesn't work. Anyone have any suggestions?

 

[DSJ1967]

spacey,<br>
<br>
I could be wrong but I don't think that you can change a field once it is part of a table<br>
<br>
To change an individual Field, you could add a new TableDef that reflects the desired change, then move the data to the new table and delete the old table.<br>
<br>
DSJ

 

[spacey]

I know I could do that, but I don't have enough time for all the code necessary to do that plus the PC equipment at the external sites would take to long for the code to run. <br>
<br>
Thanks anyway DSJ.

 

[ag]

spacey, <br>
<br>
To the best of my knowledge, Access does not allow you to do this. The size property is read-only for fields that have already been appended, whilst you are working in VBA/SQL.<br>
<br>
i.e. you get one stab at it!<br>
<br>
I believe, that even if you change the field-size through the table designer that movement of data between the source field and temporary field occurs....happy to be corrected on that one.<br>
<br>
Andrew

 

[tusconpapa]

<br>
Public Sub CmdCreate()<br>
'YOU MAY WANT PRIVATE NOT PUBLIC (YOUR CHOICE)<br>
Public dbMyBase As Database<br>
Public dbMyworkspace As Workspace<br>
Public dbMytabledef As TableDef<br>
<br>
Public dbnewnum As Field<br>
<br>
' Public dbControlNum As Field<br>
' Public dbResOffice As Field<br>
' Public dbTitle As Field<br>
<br>
' Public dbOrgName As Field<br>
' Public dbPurpose As Field<br>
' Public dbTypeOfCost As Field<br>
' Public dbAmount As Field<br>
' Public dbSssSignatory As Field<br>
' Public dbOtherSignatory As Field<br>
' Public dbSssContact As Field<br>
' Public dbOriginalDate As Field<br>
' Public dbAmendmentDate As Field<br>
' Public dbLatestDate As Field<br>
' Public dbExpireDate As Field<br>
' Public dbMatchingResult As Field<br>
' Public dbExpiredFlag As Field<br>
' Public dbComment As Field<br>
' Public ix 'As Index<br>
' Public FL 'As Field<br>
' Public strflname As String<br>
' Public strixname As String<br>
<br>
'Create Data Access as objects<br>
'On Error Resume Next<br>
' strixname = &quot;entrynum&quot;<br>
' strflname = &quot;PKMouTable&quot;<br>
'public dbEngine As Object<br>
' public dblanggeneral<br>
'public dbworkspace<br>
'public Workspaces<br>
Set dbworkspace = DBEngine.Workspaces(0)<br>
'Kill &quot;C:\Moubase.mdb&quot;<br>
<br>
'On Error Resume Next<br>
Set dbDataBase = dbworkspace.CreateDatabase( _<br>
&quot;C:\MYbase.mdb&quot;, dbLangGeneral)<br>
Set dbtabledef = dbDataBase.CreateTableDef(&quot;MYtable&quot<br>
'Set dbworkspace = DBEngine.Workspaces(0)<br>
<br>
Set dbentrynum = dbtabledef.CreateField(&quot;NewNum&quot;, dbInteger)<br>
' Set dbControlNum = dbtabledef.CreateField(&quot;ControlNum&quot;, dbInteger)<br>
' Set dbResOffice = dbtabledef.CreateField(&quot;ResOffice&quot;, dbText)<br>
' Set dbTitle = dbtabledef.CreateField(&quot;Title &quot;, dbText)<br>
' Set dbOrgName = dbtabledef.CreateField(&quot;OrgName&quot;, dbText)<br>
' Set dbPurpose = dbtabledef.CreateField(&quot;Purpose&quot;, dbText)<br>
' Set dbTypeOfCost = dbtabledef.CreateField(&quot;TypeOfCost&quot;, dbText)<br>
' Set dbAmount = dbtabledef.CreateField(&quot;Amount&quot;, dbCurrency)<br>
' Set dbSssSignatory = dbtabledef.CreateField(&quot;SssSignatory&quot;, dbText)<br>
' Set dbOtherSignatory = dbtabledef.CreateField(&quot;OtherSignatory&quot;, dbText)<br>
' Set dbSssContact = dbtabledef.CreateField(&quot;SssContact&quot;, dbText)<br>
' Set dbOriginalDate = dbtabledef.CreateField(&quot;OriginalDate&quot;, dbDate)<br>
' Set dbLatestDate = dbtabledef.CreateField(&quot;LatestDate&quot;, dbDate)<br>
' Set dbAmendmentDate = dbtabledef.CreateField(&quot;AdmendmentDate&quot;, dbDate)<br>
' Set dbExpireDate = dbtabledef.CreateField(&quot;ExpireDate&quot;, dbDate)<br>
' Set dbMatchingResult = dbtabledef.CreateField(&quot;MatchingResult&quot;, dbText)<br>
' Set dbExpiredFlag = dbtabledef.CreateField(&quot;ExpiredFlag&quot;, dbText)<br>
' Set dbComment = dbtabledef.CreateField(&quot;Comment&quot;, dbText)<br>
<br>
<br>
<br>
'Set IDate = tdfMoutable.CreateField(&quot;IDate &quot;, dbText)<br>
' Set dbMou = tdfMoutable.createField(&quot;Mou&quot;, dbInteger)<br>
' Set dbIdx = tdfMoutable.createField(&quot;Idx&quot;, dbInteger)<br>
' Set dbScreen = tdfMoutable.createField(&quot;screen&quot;, dbInteger)<br>
<br>
<br>
<br>
'set Field Properties<br>
dbnewnum.Size = 7 'is size in number of characters<br>
' dbControlNum.Size = 10<br>
' dbResOffice.Size = 4<br>
' dbTitle.Size = 70<br>
' dbOrgName.Size = 70<br>
' dbPurpose.Size = 70<br>
' dbTypeOfCost.Size = 1<br>
' dbAmount.Size = 6<br>
' dbSssSignatory.Size = 65<br>
' dbOtherSignatory.Size = 65<br>
' dbSssContact.Size = 65<br>
' dbOriginalDate.Size = 11<br>
' dbLatestDate.Size = 11<br>
' dbAmendmentDate.Size = 11<br>
' dbExpireDate.Size = 11<br>
' dbMatchingResult.Size = 1<br>
' dbExpiredFlag.Size = 1<br>
' dbComment.Size = 65<br>
<br>
<br>
' Append each field object to its table object<br>
<br>
dbtabledef.Fields.Append dbnewnum<br>
' dbtabledef.Fields.Append dbControlNum<br>
' dbtabledef.Fields.Append dbResOffice<br>
' dbtabledef.Fields.Append dbTitle<br>
' dbtabledef.Fields.Append dbOrgName<br>
' dbtabledef.Fields.Append dbPurpose<br>
' dbtabledef.Fields.Append dbTypeOfCost<br>
' dbtabledef.Fields.Append dbAmount<br>
' dbtabledef.Fields.Append dbSssSignatory<br>
' dbtabledef.Fields.Append dbOtherSignatory<br>
' dbtabledef.Fields.Append dbSssContact<br>
' dbtabledef.Fields.Append dbOriginalDate<br>
' dbtabledef.Fields.Append dbLatestDate<br>
' dbtabledef.Fields.Append dbAmendmentDate<br>
' dbtabledef.Fields.Append dbExpireDate<br>
' dbtabledef.Fields.Append dbMatchingResult<br>
' dbtabledef.Fields.Append dbExpiredFlag<br>
' dbtabledef.Fields.Append dbComment<br>
<br>
'Append each table to its database<br>
dbDataBase.TableDefs.Append dbtabledef ' is needed<br>
<br>
''''''''''''''''''''''''''''''''''''''''''''''''''<br>
' table needs master index for some functions<br>
' With dbtabledef<br>
' Set ix = .CreateIndex(strixname)<br>
' Set FL = ix.CreateField(strflname)<br>
' ix.Required = True<br>
' ix.Primary = True<br>
'<br>
' add field to index's fields collection<br>
' ix.Fields.Append FL<br>
' .Indexes.Append ix<br>
' dbDataBase.TableDefs(&quot;Moutable&quot.Indexes.Append ix<br>
' End With<br>
' MsgBox &quot;new database Created&quot;<br>
'dbDataBase.Close<br>
MsgBox &quot;cmdcreate hold complete&quot;<br>
Theend: 'notice at this point only new space has been made<br>
End Sub ' this code works to here<br>
<br>
'in here build a loop from 0 or 1 to EOF<br>
' adjust main record index<br>
' use addnew<br>
' move oldnum to newnum<br>
'use update<br>
'goback to loop<br>
' end sub<br>
'get more coffee and debug<br>
<br>