How to add new fields to Access table in VB6


Let me show how to add a new field into an existing access database Table using Visual Basic 6.0.Using the tableDef and Filed object you can create new fields from within the code . 

Firstly, you need to create Database and Recordset object, make sure the DAO access object library is enabled/added to the project.

Create the UpdateTableField Sub

Sub UpdateTableField(tb As String, new_fld As String)Dim CHECK As Boolean
CHECK = False
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Set tbl = DB.TableDefs(tb)
  For Each fld In tbl.Fields
    If fld.Name = new_fld Then
      CHECK = True
    End If
  Next
If CHECK = False Then
   Set RS = Nothing
   With tbl
   Set fld = .CreateField(new_fld, dbText)
   fld.DefaultValue = “”
   .Fields.Append fld
  MsgBox “new Field Created !, check table”
  End With
Else
  MsgBox “Field already exist !”End If

The ‘TableDefs’ method will fetch the table schema from the database which hold the field information and then fire the checking for table field. dbText hold the default data type. 

Use the sub

Dim DB As Database
Dim RS As Recordset
Private Sub Command1_Click()
Call UpdateTableField(“Table1”, “RoomID2”)
End Sub
Private Sub Form_Initialize()
Set DB = OpenDatabase(“F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb”)
End Sub

A new field “RoomID2” will be created in the ‘Table1‘ Table.
Download the Source Code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.