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?