×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Changing a Access Database field size using code

Changing a Access Database field size using code

Changing a Access Database field size using code

(OP)
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?

RE: Changing a Access Database field size using code

spacey,

I could be wrong but I don't think that you can change a field once it is part of a table

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.

DSJ

RE: Changing a Access Database field size using code

(OP)
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.

Thanks anyway DSJ.

RE: Changing a Access Database field size using code

spacey,

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.

i.e. you get one stab at it!

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.

Andrew

RE: Changing a Access Database field size using code


Public Sub CmdCreate()
'YOU MAY WANT PRIVATE NOT PUBLIC (YOUR CHOICE)
Public dbMyBase As Database
Public dbMyworkspace As Workspace
Public dbMytabledef As TableDef

Public dbnewnum As Field

' Public dbControlNum As Field
' Public dbResOffice As Field
' Public dbTitle As Field

' Public dbOrgName As Field
' Public dbPurpose As Field
' Public dbTypeOfCost As Field
' Public dbAmount As Field
' Public dbSssSignatory As Field
' Public dbOtherSignatory As Field
' Public dbSssContact As Field
' Public dbOriginalDate As Field
' Public dbAmendmentDate As Field
' Public dbLatestDate As Field
' Public dbExpireDate As Field
' Public dbMatchingResult As Field
' Public dbExpiredFlag As Field
' Public dbComment As Field
' Public ix 'As Index
' Public FL 'As Field
' Public strflname As String
' Public strixname As String

'Create Data Access as objects
'On Error Resume Next
' strixname = "entrynum"
' strflname = "PKMouTable"
'public dbEngine As Object
' public dblanggeneral
'public dbworkspace
'public Workspaces
Set dbworkspace = DBEngine.Workspaces(0)
'Kill "C:\Moubase.mdb"

'On Error Resume Next
Set dbDataBase = dbworkspace.CreateDatabase( _
"C:\MYbase.mdb", dbLangGeneral)
Set dbtabledef = dbDataBase.CreateTableDef("MYtable")
'Set dbworkspace = DBEngine.Workspaces(0)

Set dbentrynum = dbtabledef.CreateField("NewNum", dbInteger)
' Set dbControlNum = dbtabledef.CreateField("ControlNum", dbInteger)
' Set dbResOffice = dbtabledef.CreateField("ResOffice", dbText)
' Set dbTitle = dbtabledef.CreateField("Title ", dbText)
' Set dbOrgName = dbtabledef.CreateField("OrgName", dbText)
' Set dbPurpose = dbtabledef.CreateField("Purpose", dbText)
' Set dbTypeOfCost = dbtabledef.CreateField("TypeOfCost", dbText)
' Set dbAmount = dbtabledef.CreateField("Amount", dbCurrency)
' Set dbSssSignatory = dbtabledef.CreateField("SssSignatory", dbText)
' Set dbOtherSignatory = dbtabledef.CreateField("OtherSignatory", dbText)
' Set dbSssContact = dbtabledef.CreateField("SssContact", dbText)
' Set dbOriginalDate = dbtabledef.CreateField("OriginalDate", dbDate)
' Set dbLatestDate = dbtabledef.CreateField("LatestDate", dbDate)
' Set dbAmendmentDate = dbtabledef.CreateField("AdmendmentDate", dbDate)
' Set dbExpireDate = dbtabledef.CreateField("ExpireDate", dbDate)
' Set dbMatchingResult = dbtabledef.CreateField("MatchingResult", dbText)
' Set dbExpiredFlag = dbtabledef.CreateField("ExpiredFlag", dbText)
' Set dbComment = dbtabledef.CreateField("Comment", dbText)



'Set IDate = tdfMoutable.CreateField("IDate ", dbText)
' Set dbMou = tdfMoutable.createField("Mou", dbInteger)
' Set dbIdx = tdfMoutable.createField("Idx", dbInteger)
' Set dbScreen = tdfMoutable.createField("screen", dbInteger)



'set Field Properties
dbnewnum.Size = 7 'is size in number of characters
' dbControlNum.Size = 10
' dbResOffice.Size = 4
' dbTitle.Size = 70
' dbOrgName.Size = 70
' dbPurpose.Size = 70
' dbTypeOfCost.Size = 1
' dbAmount.Size = 6
' dbSssSignatory.Size = 65
' dbOtherSignatory.Size = 65
' dbSssContact.Size = 65
' dbOriginalDate.Size = 11
' dbLatestDate.Size = 11
' dbAmendmentDate.Size = 11
' dbExpireDate.Size = 11
' dbMatchingResult.Size = 1
' dbExpiredFlag.Size = 1
' dbComment.Size = 65


' Append each field object to its table object

dbtabledef.Fields.Append dbnewnum
' dbtabledef.Fields.Append dbControlNum
' dbtabledef.Fields.Append dbResOffice
' dbtabledef.Fields.Append dbTitle
' dbtabledef.Fields.Append dbOrgName
' dbtabledef.Fields.Append dbPurpose
' dbtabledef.Fields.Append dbTypeOfCost
' dbtabledef.Fields.Append dbAmount
' dbtabledef.Fields.Append dbSssSignatory
' dbtabledef.Fields.Append dbOtherSignatory
' dbtabledef.Fields.Append dbSssContact
' dbtabledef.Fields.Append dbOriginalDate
' dbtabledef.Fields.Append dbLatestDate
' dbtabledef.Fields.Append dbAmendmentDate
' dbtabledef.Fields.Append dbExpireDate
' dbtabledef.Fields.Append dbMatchingResult
' dbtabledef.Fields.Append dbExpiredFlag
' dbtabledef.Fields.Append dbComment

'Append each table to its database
dbDataBase.TableDefs.Append dbtabledef ' is needed

''''''''''''''''''''''''''''''''''''''''''''''''''
' table needs master index for some functions
' With dbtabledef
' Set ix = .CreateIndex(strixname)
' Set FL = ix.CreateField(strflname)
' ix.Required = True
' ix.Primary = True
'
' add field to index's fields collection
' ix.Fields.Append FL
' .Indexes.Append ix
' dbDataBase.TableDefs("Moutable").Indexes.Append ix
' End With
' MsgBox "new database Created"
'dbDataBase.Close
MsgBox "cmdcreate hold complete"
Theend: 'notice at this point only new space has been made
End Sub ' this code works to here

'in here build a loop from 0 or 1 to EOF
' adjust main record index
' use addnew
' move oldnum to newnum
'use update
'goback to loop
' end sub
'get more coffee and debug

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close