Create Access Table with ADO in Excel 2000
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Create Access Table with ADO in Excel 2000

  1. #1
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    858
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    From Excel VBA, I am trying to create a table in an existing Access database.

    I had been using ADO in the project. The only MSDN articles I could find on creating a table used ADOX. In Excel 2002, I added a reference to the Microsoft ADO Ext 2.7 library, and the code works. This is code that creates a new ADOX.Catalog, defines a new ADOX.Table, then does cat.Tables.Append.

    When I try to run this on Excel 2000, it does not work. The reference to ADO Ext 2.7 is missing. The same DLL is there, but it is an older version. I tried copying the new DLL over the old DLL, but that does not work.

    I've got to believe there had to be a way before Excel 2002 to create a table in an Access database programatically.

    I've found documentation on how to do this with DAO. Is there any hassle (other than increased overhead) in having 98% of the project use ADO, but this one-time call to create the table use DAO?

    Any suggestions greatly appreciated.

    Bill
    View a collection of recent Excel articles in the Excel Daily News

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    From Excel VBA, I am trying to create a table in an existing Access database.

    I had been using ADO in the project. The only MSDN articles I could find on creating a table used ADOX. In Excel 2002, I added a reference to the Microsoft ADO Ext 2.7 library, and the code works. This is code that creates a new ADOX.Catalog, defines a new ADOX.Table, then does cat.Tables.Append.

    When I try to run this on Excel 2000, it does not work. The reference to ADO Ext 2.7 is missing. The same DLL is there, but it is an older version. I tried copying the new DLL over the old DLL, but that does not work.

    I've got to believe there had to be a way before Excel 2002 to create a table in an Access database programatically.

    I've found documentation on how to do this with DAO. Is there any hassle (other than increased overhead) in having 98% of the project use ADO, but this one-time call to create the table use DAO?

    Any suggestions greatly appreciated.

    Bill
    Hi Bill,

    Other than the overhead (as you said) the only problem might be if the objects haven't been properly identified when you were Dimming them e.g. if you had:-

    Dim r as RecordSet

    Both DAO and ADO have a Recordset object and the type used would be determined by which came first in Tools, References. Obviously, this can be easily clarified by doing this:-

    Dim adoRS As ADODB.Recordset
    Dim daoRS as DAO.Recordset

    Sorry if that was obvious

    Anyway, here is some code which creates a table in an Access 2000 database using Excel 2000. I've used ADO version 2.5 (2.1 also works) and ADOX version 2.5 (I don't have version 2.7 at the moment).

    I hope it helps,

    Code:
    Option Explicit
    
    Sub CreateTable()
    Dim adoxCatalog As ADOX.Catalog
    Dim adoxTable As ADOX.Table
    Dim adoCN As ADODB.Connection
    
    
    'Establish connection to the database
    Set adoCN = New Connection
    With adoCN
        .Provider = "Microsoft.Jet.OLEDB.4.0"   '4.0 for Access 2000, 3.51 for Access 97
        .ConnectionString = "C:temptest.mdb"
        .Open
    End With
        
        
    'Now associate our connection to a Catalog object
    Set adoxCatalog = New ADOX.Catalog
    Set adoxCatalog.ActiveConnection = adoCN
    
    
    'Create the table definition
    Set adoxTable = New ADOX.Table
    With adoxTable
        .Name = "CreatedByADOX"
        .Columns.Append "ItemID", adInteger
        .Columns.Append "ItemDescription", adVarWChar, 100
        .Columns.Append "ItemValue", adCurrency
        .Keys.Append "PrimaryKeyItemID", adKeyPrimary, "ItemID"
    End With
    
    
    'Append the table to the database
    adoxCatalog.Tables.Append adoxTable
    
    
    'Clean up and close
    
    End Sub


    [ This Message was edited by: dk on 2002-03-13 05:08 ]

  3. #3
    New Member
    Join Date
    Aug 2006
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MsAccess from MsExcel2003

      
    I am new to this ADOX stuff! Any recommendations for a compile error; "User Defined Type not identified" when attempting to run the above CreateTable() code? I have MsExcel2003 & MsAccess2003 installed from Office2003.
    Thanks
    AJ

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com