asked on

creating Access tables dynamically

i'm trying to create a Access table dynamically at runtime.
i'm using createtabledef but i want to know how to create a primary key then add 5 records at runtime and fixed the primary key so it cannot be CHANGED, added or deleted.
Futhermore, i want most of the other fields to accept zero length string ? How to declare that in the code ?

i'm displaying the table using DBgrid with data control.

other problem, when i'm running the program. I encoutered this error, "Item not found in this collection."
what does that mean ?
and the debuggere highlights this line below :

Dim db As Database
Set db = DBEngine (0)(0)

please...help ASAP.
thanks a lot

ASKER CERTIFIED SOLUTION

CraigLazar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberp

ASKER

Currently i'm using this code below, so how am i able to set the field to accept zero-length string ? thanks for helping.

btw, do u think u can help me with the error i mentioned above.
the funny thing is... i used the same code in 2 forms and i only changed the parameter for the table name. the second one works...while the main one ..doesn;t .
*************************************
Set the value of "DBName", then call
"CreateDB" to create the database
Place the following in a Module:

public DBName as string 'Path of database
public MainDB as Database
private MainTD as Table
private MainFD as Field
private MainID as Index

Public Sub CreateDB()

'Create the database using DBName as the database name
Set MainDB = DBEngine.Workspaces(0).CreateDatabase(DBName, dbLangGeneral, dbVersion30)

BeginTrans

'Create a table in the database
Set MainTD = MainDB.CreateTableDef("name of database")

'Create the fields
Call CreateFieldDB("Path", dbText, 255)

'Create an index
Call CreateIndexDB("IdPath","Path",True)

'Append the table to the database
MainDB.TableDefs.Append MainTD

CommitTrans

End Sub

Private Sub CreateFieldDB(FieldName As String, FieldType As String, FieldLength As Integer)

On Error GoTo ErrHandle

'Create the fields for the Client Information table
Set MainFD = MainTD.CreateField(FieldName, FieldType, FieldLength)
MainFD.AllowZeroLength = True
MainTD.Fields.Append MainFD

Exit Sub

ErrHandle:
Select Case Err.Number
Case 3219 'AllowZeroLength cannot be True
Resume Next 'Leave as is...AllowZeroLength=False
End Select

End Sub

Private Sub CreateIndexDB(strIndexName As String, strFieldName As String, bUnique As Boolean)

'Create an index
Set MainID = MainTD.CreateIndex(strIndexName)
MainID.Fields.Append MainID.CreateField(strFieldName)
MainID.Unique = bUnique
MainTD.Indexes.Append MainID
End Sub

CraigLazar

Hi Cyberp
i have not tried the method u have listed above before. I used the method i gave. It is quick to set up. Try my sample code in a dummy project and just create a dummy database. Then as above u have options to set the field values to null in the create statement.
also go into vb and there is a nice sample there also check the vbonline, unfortunately i do not have my source code with me at the moment.
Hope this helps

Craig