Create table with DAO

[EKelly]

Hello all

I've searched through a lot of previous strings for this topic and I can't find what i'm looking for so i'm hoping someone can help me. I am simply trying to create a new Access table through DAO in VB. When I try to append my newly defined table I get an "Invalid Argument" error. I just can't see what im doing wrong! Can anyone point me in the right direction?

Public Function teplCheck(D As Database)
    Dim Td As New TableDef, fld() As New Field
    Dim Idx() As New index, I As Integer

    ReDim fld(1 To 3), Idx(1 To 2)

    Td.name = "Tepl1"   ' Set the table name.
    
' Create Fields.
    fld(1).Attributes = DB_AUTOINCRFIELD    ' Counter field.
    For I = 1 To 3  ' Set properties for fields.
        fld(I).name = Choose(I, "Name", "Description", "Default")
        fld(I).Type = Choose(I, DB_TEXT, DB_TEXT, DB_INTEGER)
        fld(I).Size = Choose(I, 50, 200, 5)
        Td.Fields.Append fld(I)
    Next I

    ' Create Indexes.
    Idx(1).name = "@0"
    Idx(1).Fields = "Default"

    Idx(2).name = "@1"
    Idx(2).Fields = "Name"
    For I = 1 To 2
        Td.Indexes.Append Idx(I)
    Next I

    ' Create Table.
    D.TableDefs.Append Td
    
End Function

The database object i pass this function is an open access database. The table was opened using

Set sysdb = OpenDatabase(projectPath & "SystemDB.mdb", False, False)

Thanks
Erin

 

[vladk]

Why are you using DAO instead o ADO?

 

[EKelly]

It is an large existing system that I am editing and that was the way it was written.

 

[vladk]

Did you try CreateTableDef method?

 

[EKelly]

Im not familiar with that method?

 

[vladk]

EKelly,

This is CreateTableDef Method example from HELP. Hope you will find it useful.

This example creates a new TableDef object in the Northwind database.

Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub


vladk

 

[EKelly]

That sorted it for me!

Thanks for your help vladk.