HELP, please: Error by appending a new table

HELP, please: Error by appending a new table

Post by JESS » Fri, 08 Mar 2002 15:43:45



I want to try to append a new table in a SQL server
database . I am using Visual Basic 6 to access the
database.

However when I get an error:"OBJECT OR PROVIDER IS NOT
CAPABLE OF PERFORMING REQUESTED OPERATION". What's up?
I know that the connection is right and I am logged as
administrator

Dim OBJCAT As New ADOX.Catalog
Dim OBJTBL As New ADOX.Table

Call EstablishConnectionDSN

OBJCAT.ActiveConnection = g_objConn

OBJTBL.Name = "Prueba"
OBJTBL.Columns.Append "FirstName", adVarChar, 25
OBJTBL.Columns.Append "LastName", adVarChar, 25

OBJCAT.Tables.Append OBJTBL (this generates the above
error)

Set OBJTBL = Nothing
Set OBJCAT = Nothing

Thanks

 
 
 

HELP, please: Error by appending a new table

Post by JESS » Fri, 08 Mar 2002 15:43:47


I want to try to append a new table in a SQL server
database . I am using Visual Basic 6 to access the
database.

However when I get an error:"OBJECT OR PROVIDER IS NOT
CAPABLE OF PERFORMING REQUESTED OPERATION". What's up?
I know that the connection is right and I am logged as
administrator

Dim OBJCAT As New ADOX.Catalog
Dim OBJTBL As New ADOX.Table

Call EstablishConnectionDSN

OBJCAT.ActiveConnection = g_objConn

OBJTBL.Name = "Prueba"
OBJTBL.Columns.Append "FirstName", adVarChar, 25
OBJTBL.Columns.Append "LastName", adVarChar, 25

OBJCAT.Tables.Append OBJTBL (this generates the above
error)

Set OBJTBL = Nothing
Set OBJCAT = Nothing

Thanks

 
 
 

HELP, please: Error by appending a new table

Post by Val Mazu » Fri, 08 Mar 2002 22:08:50


Hi,

As I know you cannot add table to SQL Server using ADOX.
SQLOLEDB provider does not support that feature. From my
expirience you can add table only to Access database. What
you can do is to add table using CREATE TABLE statement
and execute it from your VB code as usual SQL statement.
Another way to add table to SQL Server is to use SQL DMO
library.

Val

Quote:>-----Original Message-----
>I want to try to append a new table in a SQL server
>database . I am using Visual Basic 6 to access the
>database.

>However when I get an error:"OBJECT OR PROVIDER IS NOT
>CAPABLE OF PERFORMING REQUESTED OPERATION". What's up?
>I know that the connection is right and I am logged as
>administrator

>Dim OBJCAT As New ADOX.Catalog
>Dim OBJTBL As New ADOX.Table

>Call EstablishConnectionDSN

>OBJCAT.ActiveConnection = g_objConn

>OBJTBL.Name = "Prueba"
>OBJTBL.Columns.Append "FirstName", adVarChar, 25
>OBJTBL.Columns.Append "LastName", adVarChar, 25

>OBJCAT.Tables.Append OBJTBL (this generates the above
>error)

>Set OBJTBL = Nothing
>Set OBJCAT = Nothing

>Thanks

>.

 
 
 

1. Problem with appending one table to two tables - referential integrity - help please

Hi there,

First of all, we are using Access 97 clients and SQL 6.5 backend.

What I'd like to do is use an append query to append data from one table to
two other tables. I've already setup a query to show all the fields from
the two tables so I can use that query in the append to destination.

Problem: CustomerID in the Customers table is linked to CustomerID in the
Bookings table - and it is not an auto-number, it is entered by hand from
firstname/lastname fields. I've gotten around that problem by using an
autonumber in the table where I want to append from - and include that
autonumber field in the append query. It seems to work alright if I have
only fields from the Customers table (where customerid is the primary key),
but whenever I add fields from the booking table (which is linked from
customers table via customerid, but the primary key is bookingid - not
really used much) I get the Access 97 key violation error.

There must be some way to add the customerid to both tables at the same
time without the key violation / referental integrity error message. I've
tried already to put no customerid in the append query - doesn't work.
Append CustomerID to both CustomerID fields in the append query - doesn't
work. Append CustomerID only to the Customers table - doesn't work. Append
CustomerID only to the Bookings table - doesn't work, but at least the
error message is different - field not updateable - which is logical of
course.

I tried to define the relationship as cascade update related fields, which
I believe should fix that problem, but I can't seem to select it. My guess
is that's because it comes from the SQL server. On the server side the
Customers.CustomerID is the primary key in the Customers table and a
foreign key in the bookings table.

Thanks for your time & help! :-)

        Thomas

--
***********************************************************************
Thomas Fischer             The Road goes ever on and on
Planet Holiday               Out from the door where it began.
                                    Now far ahead the Road has gone,
Anacott Travel Network   Let others follow it who can!
Internet Reservations      Let them a journey new begin,
Tel:  +66-76 263-239      But I at last with weary feet
Fax: +66-76 263-586      Will turn towards the lighted inn,
                                    My evening-rest and sleep to meet.
http://www.planetholiday.com                      - J.R.R Tolkien
***********************************************************************

2. foxpro 2.7 wish list

3. Help - Problem appending new table

4. SQL Anywhere and ReportSmith

5. Help on appending new records in SQL Tables

6. West Coast-Progress V6.3->V7 chr-WSI

7. Error 3264 - Cannot Append TableDef or Index PLEASE HELP

8. PICK Programmer needed in SC

9. need help please:joined tables,add new entries based on one table columns

10. Create new table as select Help PLease!!!!

11. Error when Creating New Publication .. Please Help

12. Select into new table and append