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
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
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”
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.
