Creating a new table in Access...

[Dan0331]

None of the books on VB6 go into creating a new table using VB. Does anyone know the code to do so?

Thanks for the help!

[RogerH]

Hi,

create table test (ID autoincrement, stringfield char (50), longfield long)

Syntax can vary, especially data types. Look for the create table command in your database doc.

Roger

[MartinLiss]

Here is a routine you can call. It adds a field to a table and if the table doesn't exist, it will be created, so for example if the routine is called three times with the same sTablename, but 3 different sFieldName values, you will wind up with one table that contains 3 fields.

Public Sub AddField(sTableName As String, sFieldName As String, nFieldType As Integer, nFieldSize As Integer, bRequired As Boolean, bAllowZeroLength As Boolean)
'***************************************************************************
'Purpose: Add a new field to a table
'Inputs:  sTableName - The target table name. If it doesn't exist it
'                      will be created
'         sFieldName - The name of the new field
'         nFieldType - The number representing the type of the new field
'                      1 = Yes/No            7 = Double
'                      2 = Byte              8 = Date/Time
'                      3 = Integer           9 = (not valid)
'                      4 = Long             10 = Text
'                      5 = Currency         11 = Long Binary (OLE Object)
'                      6 = Single           12 = Memo
'         nFieldSize - The size of the new field (ignored if not appropriate
'                      to the field type)
'         bRequired - Require field if True
'         bAllowZeroLength - Allow field to be zero length if True
'Outputs: None
'***************************************************************************

    Dim tdTableDef As TableDef
    Dim fldFieldToAdd As Field
    Dim bFound As Boolean
    Dim nCtr As Integer
    
    On Error GoTo ErrorRoutine

    'Search to see if table exists
    For nCtr = 0 To gdbTargetDB.TableDefs.Count - 1
        If UCase(gdbTargetDB.TableDefs(nCtr).Name) = UCase(sTableName) Then
             bFound = True
            Exit For
        End If
    Next

    If bFound Then
        Set tdTableDef = gdbTargetDB.TableDefs(sTableName)
        Set fldFieldToAdd = tdTableDef.CreateField(sFieldName, nFieldType, nFieldSize)
        If nFieldType = 10 Then fldFieldToAdd.AllowZeroLength = bAllowZeroLength
        fldFieldToAdd.Required = bRequired
        gdbTargetDB.TableDefs(sTableName).Fields.Append fldFieldToAdd
    Else
        'Table doesn't exist
        Set tdTableDef = gdbTargetDB.CreateTableDef(sTableName)
        Set fldFieldToAdd = tdTableDef.CreateField(sFieldName, nFieldType, nFieldSize)
        If nFieldType = 10 Then fldFieldToAdd.AllowZeroLength = bAllowZeroLength
        fldFieldToAdd.Required = bRequired
        tdTableDef.Fields.Append fldFieldToAdd
        gdbTargetDB.TableDefs.Append tdTableDef
    End If
    
ErrorRoutine:

    If Err.Number <> 0 Then
        ' Your error processing
    End If

End Sub

------------------
Marty

[This message has been edited by MartinLiss (edited 01-03-2000).]

[Dan0331]

Hi Roger, and Marty,

Thanks for your help. Your code did the trick!

Thanks again.

[Dan0331]

Actually, I just found a bug. When I use the same format that Roger used just changing the names, and lengths I get an error message when I move to the next line.

"Expected end of statement" with the name of the table to be created highlighted.

Any suggestions?