Create Table at run time using DAO connection in Visual Basic 6.0


Using VB6 database connection (DAO) connection we can not only manipulate data from the existing database but also dynamically create new table according to the necessity of the program. This can be achieved using CreateTableDef method of record set object.

Make sure your are included the appropriate references from References window.

Create a new connection 

Dim MedicalShop as Database
Set MedicalShop=opendatabse(GetAppPath()& “shop.mdb”)

Create the Sub for create table

The the sub will utilizes the Tabledef and Fields objects and by using the CreateTabledef command it will create the new table.

Lets add the body of the procedure as follows

Public Sub CreateTable(tbl As String, fl() As String)
Dim tdf As TableDef
Dim fld As Field
Dim f, I
I = 0
Set tdf = MedicalShop.CreateTableDef(tbl)
For Each f In fl
 If f Empty Then
  Set fld = tdf.CreateField(f, dbText)
  tdf.Fields.Append fld
  I = I + 1
 End If
Next
MedicalShop.TableDefs.Append tdf
MedicalShop.Recordsets.Refresh
End Sub

By default the field type is DbText, you can specify each  with different type by passing an addition argument list or a two dimensional array.

Call the CreateTable method

Dim f(3) as String
f(0)=”Entryno”
f(1)=”Name”
f(2)=”amount”
f(3)=”Narration”

Now call the procedure

Call CreateTable(“salesentry”,f)

Will create a new table with fields and having Text field type.

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.