Using ADOX & SQLOLEDB to rename a column

Using ADOX & SQLOLEDB to rename a column

Post by hoss » Fri, 28 Feb 2003 05:49:19



Hi Folks-

VB6 SP5; ADOX v2.7; SQLOLEDB provider

I would like to know if it's possible to use ADOX in VB to
rename an existing column of an existing table in SQL Server
2000, and also, any limitations or caveats related to renaming
an existing field.

When I try setting ADOXcolumn.Name = "something", I get a
run-time error 3251 "Object or provider is not capable of
performing requested operation."

I thought for sure that I have done this before, but am not certain.
Also, I read Microsoft's MSDN library article on the ADOX
"Name" property, and did see that it was read/write.  I also checked
the SQLOLEDB limitations page, and did not see anything about
renaming an existing field...

I do know about SQL-DMO, but was curious if this could be done
using ADOX.

Any thoughts would be great... thanks-
hoss

 
 
 

Using ADOX & SQLOLEDB to rename a column

Post by Val Mazu » Fri, 28 Feb 2003 07:59:15


Hi,

That error means that provider does not support that functionality for ADOX
and you cannot do that manipulation. Basically ADOX has full support only
with Jet provider and it has very limited support with other providers. I
would not rely on ADOX to modify SQL Server objects. There are two ways to
do that. First is to use ALTER TABLE statements and execute them using ADO.
Another way is to use SQL DMO COM library, which is similar to ADOX for
functionality, but was designed specifically for SQL Server

--
Val Mazur
Microsoft MVP



Quote:> Hi Folks-

> VB6 SP5; ADOX v2.7; SQLOLEDB provider

> I would like to know if it's possible to use ADOX in VB to
> rename an existing column of an existing table in SQL Server
> 2000, and also, any limitations or caveats related to renaming
> an existing field.

> When I try setting ADOXcolumn.Name = "something", I get a
> run-time error 3251 "Object or provider is not capable of
> performing requested operation."

> I thought for sure that I have done this before, but am not certain.
> Also, I read Microsoft's MSDN library article on the ADOX
> "Name" property, and did see that it was read/write.  I also checked
> the SQLOLEDB limitations page, and did not see anything about
> renaming an existing field...

> I do know about SQL-DMO, but was curious if this could be done
> using ADOX.

> Any thoughts would be great... thanks-
> hoss


 
 
 

1. Rename Column in Excel using ADO or ADOX

I have created a demographic type application in VB6SP5 that stores it's
data in an Access database using ADO. At the end of the day I am parsing the
information to various sheets in a new Excel book using:

SELECT * INTO [Excel 8.0;Database=" & App.Path & "\" & strStartDate &
".xls.........

I can customize my sheet names as needed but it uses the same field names
that are in the Access database when creating all of the columns in each
sheet. I would like to know if there is a way to rename a column / field in
Excel using ADO/ADOX.

I have tried:

    Set XConn = New ADOX.Catalog
    XConn.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource =
" & App.Path & "\" & strStartDate & ".xls;Extended Properties=Excel 8.0"

and I get:

"Could not find installable ISAM"

I have also tried:

    Set Conn2 = New ADODB.Connection
    Set XConn = New ADOX.Catalog
    Conn2.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=" &
App.Path & "\" & strStartDate & ".xls;"
    Conn2.Open
    XConn.ActiveConnection = Conn2
    Set Tbl = XConn.Tables("Totals")
    Tbl.Columns("ID").Name = "Number"

and I get:

"Object or Provider is not capable of performing requested operation"

I know since Excel is not a true database that using database functionality
will be difficult but I was wondering if maybe there was something I am
overlooking.

Neil

2. Teleport install tape needed

3. HELP ADOX rename a column

4. Temp tables Oracle versus Sybase

5. Precision is Invalid when Adding Column to table using adox (columns.append)

6. returning clause in JDBC

7. How to rename a Access Table using ADOX

8. Shared memory error

9. ADOX, SQLOLEDB and Foreign Keys

10. Access2000 VBA -> SQLOLEDB ADOX Woes

11. Setting column properties using ADOX

12. Field Order using ADOX .columns Help!

13. Define Column Order When Creating Table Using ADOX