add table, then add data *RESOLVED*

[phinds]

I have successfully added a new table to an existing db, using the ADOX "catalog". I am also able to add new data to an existing table using ADO data table statements. What I CANNOT figure out how to do is to combine the two so that I can create a new table and then populate it.

My "add table" code, which works fine to add the table, is as follows, but I can't figure out how to (1) close the table so I can reopen it to add data, or (2) add data in some other way.

I think I can figure out how to do it using SQL, but I really don't want to do it that way. I'm a big fan of ADO / ADOX and would like to stick to those methods and avoid SQL.

    Dim tbl As New Table
    Dim cat As New ADOX.Catalog
    Dim rsNEW As ADODB.Recordset
    '
    ' Open the catalog
    '
    cat.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & App.Path & "\DB1.mdb"
    '
    ' set info for new table
    '
    tbl.Name = "NewTable"
    tbl.Columns.Append "int1", adInteger
    tbl.Columns.Append "int2", adInteger
    tbl.Columns.Append "text1", adVarWChar, 50
    '
    ' append table to the catalog (i.e. add table to db)
    '
    cat.Tables.Append tbl

the code I use to add data to an existing table, which also works, is as follows, but I can't run it after the above because I can't create the rs, since it's already open, and I can't figure out how to otherwise add data.

    Set rsNEW = New ADODB.Recordset
    rsNEW.Open "NewTable", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    With rsNEW
        .AddNew
        .Fields("int1") = 13
        .Fields("int2") = 14
        .Fields("text1") = "hi --- I'm a new data field"
        .Update
    End With

any help appreciated.

[Ecniv]

You could try refreshing the catalog, then refreshing the connection before opening the recordset.

Are you sure its already open? or just doesn't exist?


Vince

[phinds]

Originally posted by Ecniv
You could try refreshing the catalog, then refreshing the connection before opening the recordset.

Are you sure its already open? or just doesn't exist?


Vince

Thanks for your reply.

the rsNEW.Open statement produces an error message saying the object is already open, so I'll try your suggestion but I don't think it's going to make any difference.

Is there a way to CLOSE the catalog? If I could close the connection made by opening the catalog, and then reopen the db, it seems I'd be able to access the newly created table, but I can't find any way to do that. I've searched extensively in this forum but all the threads show how to CREATE the new table, but not how to sucessfully populate it right after creating it.

[phinds]

AH ... I got it. My problem was that my rsNEW open statement was using a connection that was established BEFORE I added the new table, so all I had to do was close and reopen and all is well