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) ASAP.
thanks a lot
Who is Participating?
CraigLazarConnect With a Mentor Commented:
Hi there
I needed to do the same awhile ago and this is how i did it

set up your database object db using your protocol (DAO,RDO,ADo)
then use this statement
DB.Execute "CREATE TABLE MyTable (FirstName TEXT, LastName TEXT);"

Parametrs for the Create Statment
CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part      Description
table      The name of the table to be created.
field1, field2      The name of field or fields to be created in the new table. You must create at least one field.
type      The data type of field in the new table.
size      The field size in characters (Text and Binary fields only).
index1, index2      A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex      A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.
A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.
You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

the above u can set certain fileds that sre created to accept null characters

Hope this helps

cyberpAuthor Commented:
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)


'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


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

    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

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

All Courses

From novice to tech pro — start learning today.