creating Access tables dynamically

Posted on 1999-07-12
Medium Priority
Last Modified: 2013-12-25
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
Question by:cyberp
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

CraigLazar earned 300 total points
ID: 1506496
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


Author Comment

ID: 1506497
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


Expert Comment

ID: 1506498
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


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question