Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 13th, 2007, 08:45 AM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Appending a Table to a Catalog using ADOX

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
Reply With Quote
  #2 (permalink)  
Old June 13th, 2007, 12:42 PM
Authorized User
 
Join Date: Mar 2007
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...

Reply With Quote
  #3 (permalink)  
Old June 13th, 2007, 02:41 PM
Registered User
 
Join Date: Jun 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #4 (permalink)  
Old June 15th, 2007, 01:58 PM
Authorized User
 
Join Date: Mar 2007
Location: , , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending Records from One table to another Tallon Wolle Pro VB 6 4 September 9th, 2008 11:29 AM
Create a Table using ADOX JDrown Access VBA 7 April 12th, 2006 03:52 AM
appending a field to a table Dmouse Access ASP 1 February 29th, 2004 04:18 AM
I can't use ADOX.Catalog to get views or users. yangfanatp2p VB Databases Basics 11 December 11th, 2003 11:35 PM
Appending Records from One table to another Tallon Wolle Pro VB 6 1 November 20th, 2003 02:26 PM



All times are GMT -4. The time now is 10:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.