ADO Query

[u0107]

Hello,

Using ADO I have copied a table with another name within the same database.

next I have deleted all records within the copied table so that i have just the structure.

Now I want to add some fields to the structure before I load the table with data.

Anyone done this before?

I am using the following:
Dim tbl As ADOX.Table
Set tbl = New ADOX.Table

With tbl
.Name = "tblnamel"
.Columns.Append "Field1", adWChar
.Columns.Append "Field2", adDate
.Columns.Append "Field3", adDate
End With

But I am encountering a problem where the newly appended columns don't seem to have got added. Later in the logic when I try to add data to the appended columns, I get an error telling me that the columns don't exist in the table.

Any suggestions?

Thanks in advance.

Uttam

 

[dk]

Hi,

You need to append the table to a catalog object e.g.

Sub Test()
    
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Set tbl = New ADOX.Table

    With tbl
        .Name = "tblnamel"
        .Columns.Append "Field1", adWChar
        .Columns.Append "Field2", adDate
        .Columns.Append "Field3", adDate
    End With


    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    cat.Tables.Append tbl

End Sub

 

[u0107]

Thank you for your response.

This method will work for new tables being created.

What I need is as follows:

1) Copying an existing table with a new name. (DONE)
2) Deleting all records in the copy so that only the structure remains (DONE)
3) Appending some fields to this structure.... (PROBLEM AREA).
4) Appending some data into the new enhanced Table structure (PROBLEM TILL 3 IS SOLVED).

Net, net, Could you please let me know the changes that I need to incorporate to append fields to an already existing structure?

Thanks in advance

Uttam

 

[dk]

Hi,

My apologies, I misunderstood your initial question. This code demonstrates how to add columns to an existing table:-

Sub test()

    Dim tbl As ADOX.Table
    Dim cat As ADOX.Catalog

    'Create a catalog and link it to the current database
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection

    'Reference the table we want to work with
    Set tbl = cat.Tables("tblnamel")

    'Add the new columns
    With tbl
        .Columns.Append "Field1", adWChar
        .Columns.Append "Field2", adDate
        .Columns.Append "Field3", adDate
    End With


End Sub

 

[u0107]

Just perfect! Thank you very much..

Regards,

Uttam

================