Inserting additional fields in Access table

TableA already have data in multiple fields.

Iw ill like to insert 5 additional fields and specify the characteristics of the fields being inserted.
e.g
TableA's current field

ID       Pk (Autonumber) - Long Integer, increment
Field1  (number)            - Long Integer
Field2  (Date/Time)        - Short Date
Field3  (Text)                  - Field Size: 35


and now I want to insert four additional fields with the following chracteristics

ApID     (fk)            
Field4  (number)       - Long Integer
Field5  (Yes/No)         - Short Date
Field6  (Text)             - Field Size: 6
Field7  (Date/Field)    - Short Date
Field8  (Memo)
****************
Final Result expected in the new TableA after modification should be:

ID        Pk (Autonumber) - Long Integer, increment
Field1  (number)            - Long Integer
Field2  (Date/Time)        - Short Date
Field3  (Text)                  - Field Size: 35
ApID     (fk)            
Field4  (number)       - Long Integer
Field5  (Yes/No)         - Short Date
Field6  (Text)             - Field Size: 6
Field7  (Date/Field)    - Short Date
Field8  (Memo)




LVL 3
bobby6055Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BRetiredCommented:
You can switch to design mode and add the fields without disturbing existing data. Always work on a copy of your database to avoid any issues.

Chris B
0
bobby6055Author Commented:
I know that. I am looking for an sql or query to do that instead of doing it manually. I have several tables to update and doing it maually for all the tables will be cumbersome.
0
TinTombStoneCommented:
Sample procedure to show how to add fields to an existing table

Sub AddFields()
    Dim db As dao.Database
    Dim fld As dao.Field
    Dim tDef As dao.TableDef

    Set db = CurrentDb
    Set tDef = db.TableDefs("sample")
   
    'create a field, set attributes and append to table
    Set fld = tDef.CreateField("Field1", dbLong)
    fld.Attributes = dbAutoIncrField + dbFixedField
    tDef.Fields.Append fld
   
    'or
    'Create and append in one
    With tDef
        .Fields.Append .CreateField("Field2", dbDouble)
        .Fields.Append .CreateField("Field3", dbInteger)
        .Fields.Append .CreateField("Field4", dbDate)
        .Fields.Append .CreateField("Field5", dbText, 50)
    End With

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bobby6055Author Commented:
Tim:
I found a VBA at this link.
http://www.access.qbuilt.com/html/vba.html

Can you modify the code to create fields 1 through 5 using one of the VBA described at the link.
0
bobby6055Author Commented:
Also what is the code for adding a Yes/No field.?
0
TinTombStoneCommented:
Hi Bobby, will get back to this tomorrow

Adding a Yes/No field would be

        .Fields.Append .CreateField("Field6", dbBoolean)
0
bobby6055Author Commented:
Tim:
Nothing t worry.. I already tried your suggested code and below is what I came up with for tweaking flexibilities.

The fileds are listed above are of different attributes as such I will like to: -

(a).  create Field1 and set the attributes as you have right defined in your previously posted  code.
(b).  Create Pk and Fk also
(c). Create Field2 through 8 and set each attributes per each field requirement
      For example in Field5 (after creating a "Yes /No" field, I also want to set the attribute to be ether "Yes/No" or "true/false"

This will give me some flexibilities in tweaking each field attribute to my requirement.

In this case, the code should be independent of the other such that I can turn "on or off" the creation of a particular field by remarking it.

For example, if I want to create both the PK and FK, I should be able to that however, if I choose only to create a PK with no Fk defined
or vice versa - it should be made possible.

Thanks.
0
bobby6055Author Commented:
In my question I listed Field5 as a Yes/No field with Short Date as an attribute - it's a mistake.

It should be :
Field5 "yes/No" - attribute (Yes/No or True  / false" (which ever is needed)
0
bobby6055Author Commented:
Tim:
Something else to ask:
Using your posted code, I defined about 17 fields to append to a table and upon running the code I received error code "#1390 - Too many fields defined"
..and below, please find the code I utilized.

Are there limitations to the number fields one can define?
Public Sub AddFieldsTotbl()

   On Error GoTo ErrHandler
   
   Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tDef As DAO.TableDef

    Set db = CurrentDb
    Set tDef = db.TableDefs("tblDepartment")
    
       'create a field, set attributes and append to table
    Set fld = tDef.CreateField("Field1", dbLong)
    fld.Attributes = dbAutoIncrField + dbFixedField
    tDef.Fields.Append fld

     'or
    'Create and append in one
    With tDef
        .Fields.Append .CreateField("Field2", dbDouble)
        .Fields.Append .CreateField("Field3", dbInteger)
        .Fields.Append .CreateField("Field4", dbDate)
        .Fields.Append .CreateField("Field5", dbText, 50)
  
        .Fields.Append .CreateField("Field6", dbText, 35)
        .Fields.Append .CreateField("Field7", dbText, 3)
        .Fields.Append .CreateField("Field8", dbText, 50)
        .Fields.Append .CreateField("Field9", dbText, 50)
        .Fields.Append .CreateField("Field10", dbText, 2)
        .Fields.Append .CreateField("Field11", dbText, 3)
        .Fields.Append .CreateField("Field12", dbText, 4)
        .Fields.Append .CreateField("Field13", dbText, 3)
        .Fields.Append .CreateField("Field14", dbText, 3)
        .Fields.Append .CreateField("Field15", dbDate)
        .Fields.Append .CreateField("Field16", dbText, 4)      
        .Fields.Append .CreateField("Field17", dbText, 19)   
        .Fields.Append .CreateField("Field18", dbText, 4)
        .Fields.Append .CreateField("Field19", dbText, 2)
'       .Fields.Append .CreateField("Field20", dbBoolean)       
        .Fields.Append .CreateField("Field21", dbText, 10)
        .Fields.Append .CreateField("Field22", dbText, 10)

    End With
    
  CleanUp:

   Set fld = Nothing
   Set tDef = Nothing
   Set db = Nothing
   
   Exit Sub

ErrHandler:

   MsgBox "Error in AddFieldsTotbl( )." & vbCrLf & vbCrLf & _
       "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
   Err.Clear
   GoTo CleanUp
   
End Sub

Open in new window

0
bobby6055Author Commented:
Tim:
The above code did work on tables with few fields and also works on some tables that are dont have too many fields per my recent test.
0
TinTombStoneCommented:
OK. with the Yes/No you will need to dump the code for the Boolean field

.Fields.Append .CreateField("Field20", dbBoolean)  

And use

    Set fld = tDef.CreateField("Field20", dbBoolean)
    tDef.Fields.Append fld
   fld.Properties.Append fld.CreateProperty("DisplayControl", 3, 106)

this will set the Yes/No field to be a checkbox which would be the default

0
bobby6055Author Commented:
Tim:
In my earlier question under ID:35228177.

ia sked for the ability to be able to create differrent fields with their attributes (such as, text, Autonumber, checkbox, pk and fk.

Could you please put a sample together for me.

In this case, the code should be independent of the other such that I can turn "on or off" the creation of a particular field by remarking it.

For example, if I want to create both the PK and FK, I should be able to that however, if I choose only to create a PK with no Fk defined
or vice versa - it should be made possible.

0
bobby6055Author Commented:
something like this...Is this how to do it? ...below

    'create a field, set attributes and append to table
    Set fld = tDef.CreateField("Field1", dbLong)
    fld.Attributes = dbAutoIncrField + dbFixedField
    tDef.Fields.Append fld

   Set fld =tDef.CreateField("Organization", dbText, 30)
   tDef.Fields.Append fld
   fld.Properties("Required").Value = True
   fld.Properties("AllowZeroLength").Value = False

  Set fld = tDef.CreateField("Field20", dbBoolean)
    tDef.Fields.Append fld
   fld.Properties.Append fld.CreateProperty("DisplayControl", 3, 106)

0
TinTombStoneCommented:
Hi Bobby

Sorry, been rather busy.  Here is an example that will create two tables, add the fields, set indexes and Primary Keys, alter properties, then relate the two tables together.

You would need to use an IF or select case block around the index/PK/autoincrement lines to selectively add them

Hope this ties it all together

Sub CreateAndLinkTables()

Dim db As DAO.Database
Dim tDef As DAO.TableDef
Dim tdef2 As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim rel As Relation
Dim idx As Index


'create Table1
    Set db = CurrentDb
    Set tDef = db.CreateTableDef("Employees")
   
    'Create 1st field - Long, AutoIncrement
    Set fld = tDef.CreateField("EmployeeID", dbLong)
    'set Autoincrement attribute
    fld.Attributes = dbAutoIncrField
    tDef.Fields.Append fld
   
    'create 2nd field - Text
    Set fld = tDef.CreateField("EmployeeName", dbText, 50)
    tDef.Fields.Append fld
   
    'Create field 3 - Yes/No - Checkbox
    Set fld = tDef.CreateField("InPension", dbBoolean)
    tDef.Fields.Append fld
   
    'create 4th field - Currency
    Set fld = tDef.CreateField("EmpSalary", dbCurrency)
    tDef.Fields.Append fld
   
    'create index and PrimaryKey
    Set idx = tDef.CreateIndex("pk")
    idx.Fields.Append idx.CreateField("EmployeeID")
    idx.Unique = True
    idx.Primary = True
    tDef.Indexes.Append idx
    'append table
    db.TableDefs.Append tDef
   
    'create and append field properties - sets yes/no field as checkbox
    tDef.Fields("InPension").Properties.Append tDef.Fields("InPension").CreateProperty("DisplayControl", 3, 106)

'create Table2
    Set tdef2 = db.CreateTableDef("Orders")
    'Create 1st field
    Set fld = tdef2.CreateField("OrderID", dbLong)
    'set Autoincrement attribute
    fld.Attributes = dbAutoIncrField
    tdef2.Fields.Append fld
   
    'create 2nd field
    Set fld = tdef2.CreateField("EmployeeID", dbLong)
    tdef2.Fields.Append fld
   
    'create 3rd field
    Set fld = tdef2.CreateField("OrderDate", dbDate)
    tdef2.Fields.Append fld
   
   
    'Create index and PrimaryKey
    Set idx = tdef2.CreateIndex("pk")
    idx.Fields.Append idx.CreateField("OrderID")
    idx.Unique = True
    idx.Primary = True
    tdef2.Indexes.Append idx
   
    'append table
    db.TableDefs.Append tdef2
   
    'create Relationship
    Set rel = db.CreateRelation("EmployeeID", tDef.Name, tdef2.Name, dbRelationUpdateCascade)
    'append pk field
    rel.Fields.Append rel.CreateField("EmployeeID")
    'append fk field
    rel.Fields!EmployeeID.ForeignName = "EmployeeID"
    'append relationship
    db.Relations.Append rel
   
    'clean up here
   
    Application.RefreshDatabaseWindow

End Sub
0
bobby6055Author Commented:
Tin:
Thanks for your additional help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.