I am trying to have some VBA code create an error table for me when importing data. I duplicated an example from my WROX book (Access 2003 VBA) and modified it for additional columns in the table. Unfortunately, I keep getting this message:

"Run-time error '-2147217859 (80040e3d)': Type is invalid", which the debugger points to the line "cat.Tables.Append tbl" (this is verbatim from the book)

Could anyone point me in the right direction please?

************************************************** ********************
Public Sub CreateErrorTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    On Error Resume Next

    cat.Tables.Delete "Input_Errors"
    On Error GoTo 0

    Set tbl = New ADOX.Table
    tbl.Name = "Input_Errors"

    tbl.Columns.Append "ST", adVarChar, 50
    tbl.Columns.Append "Region", adVarChar, 50
    tbl.Columns.Append "BrNum", adInteger
    tbl.Columns.Append "BrName", adVarChar, 50
    tbl.Columns.Append "FeesCharged", adCurrency
    tbl.Columns.Append "SumofMyBranch", adCurrency
    tbl.Columns.Append "DiscretionWaiver", adCurrency
    tbl.Columns.Append "Discretionary", adCurrency
    tbl.Columns.Append "SustainedAssessed", adCurrency
    tbl.Columns.Append "CourtesyWaiver", adCurrency
    tbl.Columns.Append "File Date", adDBDate

    cat.Tables.Append tbl '<-- this is the line debug points to
    cat.Tables.Refresh

    cat.ActiveConnection = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Sub

Well, I don't normally use ADO, but it looks like it is objecting to one of the datatypes you have used in the tbl.Columns.Append list. Are there any types you have used here that were not in the book example? That might help you narrow it down. Alternatively, you could comment out all but one Append statement, and test for each datatype at a time.

Once you know which one is causing the problem, you can find an alternative and code round the problem.

Hope this helps...

Richard,

Thanks for responding!

Unfortunately, I also copied verbatim the example from the book, and it gave me the same error message for the same line: "cat.Tables.Append tbl"

Any other ideas?

Okay - well, I did say I was working a little in the dark here! I'm assuming that you have a reference to the appropriate libraries - otherwise, you'd get an error much earlier on. One thing that might be relevant is that you have not declared any primary key for your table before creating it.

A quick Google search turned up the following page, which shows examples of creating tables in ADOX. They decalre their objects differently, so you might like to try and change your code to do the same.

http://www.4guysfromrolla.com/webtech/013101-1.shtml

Good luck!

Richard