?
Solved

Creating tables via ADO in Access 2007

Posted on 2008-06-24
4
Medium Priority
?
1,073 Views
Last Modified: 2013-11-29
I'm trying to create a table using the attached VBA code.  However, when I try to execute it I get the following error

run-time error
-2147217859 (80040e3d)

The error occurs at line 30 "cat.Tables.Append tbl"  I've used the locals window and it states that the catalog object is no longer valid
Public Sub CreateInvoiceTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
   
    'Create and connect a catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
     
    On Error Resume Next
    
    'If the table already exists, delete it
    cat.Tables.Delete "tblInvoice"
    On Error GoTo 0
    
    'Create the table definition in memory
    Set tbl = New ADOX.Table
    tbl.Name = "tblInvoice"
    
    'Create the new columns
    tbl.Columns.Append "InvoiceNo", adVarChar, 10
    
   
    'Create the remaining columns
    tbl.Columns.Append "InvoiceDate", adDBDate
    tbl.Columns.Append "CustomerID", adInteger
    tbl.Columns.Append "Comments", adVarChar, 50
    Debug.Print tbl.Name
    'Append the new table to the collection
 
    cat.Tables.Append tbl   'Error occurs here
    
  
    
    cat.Tables.Refresh
    Application.RefreshDatabaseWindow
    
    'Clean up
    cat.ActiveConnection = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    
 
End Sub

Open in new window

0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 21861186
try this

Public Sub CreateInvoiceTable()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
   
    'Create and connect a catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
     
    On Error Resume Next
   
    'If the table already exists, delete it
    cat.Tables.Delete "tblInvoice"
    On Error GoTo 0
   
    'Create the table definition in memory
    Set tbl = New ADOX.Table
    tbl.Name = "tblInvoice"
   
    'Create the new columns
    tbl.Columns.Append "InvoiceNo", adVarWChar, 10
   
   
    'Create the remaining columns
    tbl.Columns.Append "InvoiceDate", adDate
    tbl.Columns.Append "CustomerID", adInteger
    tbl.Columns.Append "Comments", adVarWChar, 50
    Debug.Print tbl.Name
    'Append the new table to the collection
 
    cat.Tables.Append tbl   'Error occurs here
   
 
   
    cat.Tables.Refresh
    Application.RefreshDatabaseWindow
   
    'Clean up
    cat.ActiveConnection = Nothing
    Set tbl = Nothing
    Set cat = Nothing
   
 
End Sub
0
 

Author Comment

by:chtullu135
ID: 21861328
I ran the code you sent and it works.  I did a line by line comparison with my code and could find no difference.  I must be missing something.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21861375
the lines with '<<<<<  were changed

   tbl.Columns.Append "InvoiceNo", adVarChar, 10      '<<<<<<
   
   
    'Create the remaining columns
    tbl.Columns.Append "InvoiceDate", adDBDate         '<<<<<<
    tbl.Columns.Append "CustomerID", adInteger
    tbl.Columns.Append "Comments", adVarChar, 50    '<<<<<<
0
 

Author Comment

by:chtullu135
ID: 21861544
Thanks for the info.  To be honest I've never run across adVarWChar before.  I'll research it.  Thanks again
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question