err 3251 for cat.Tables.Append tbl

[Guest]

When I try to add new table using ADOx I receive error message:
Run-time error '3251'
'Object or provider is not capable of performing requested operation'

I added reference for Microsoft ADO Ext. 2.8 for DLL and Security as
decribed in Help file.

After that I tried to install 'Instcat.sql' on my SQL 2000 Server (8.00.178)
following the http://support.microsoft.com/default.aspx?scid=kb;en-us;201716
info.

But I continue to have the same problem.

Please help me. Thank you in advance

 

[Sylvain Lafontaine]

Maybe a permission problem.

 

[Guest]

No it is not - I'm admin of app and SQL Server.
I'm able to create the table using DoCmd.RunSQL "CREATE TABLE..."
but not using cat.Tables.Append tbl

 

[Sylvain Lafontaine]

Then why are you trying to use ADOX instead of sql statements? Aren't
things already not enough complicated?

 

[Guest]

OK but it seems to be easier to add new columns with ADOX.

But another question is when you add new table using DoCmd.RunSQL how do you
refresh the tables content to see and to use that table or query?

 

[Gijs Beukenoot]

From <PBigOS :

When I try to add new table using ADOx I receive error message:
Run-time error '3251'
'Object or provider is not capable of performing requested operation'

I added reference for Microsoft ADO Ext. 2.8 for DLL and Security as
decribed in Help file.

After that I tried to install 'Instcat.sql' on my SQL 2000 Server (8.00.178)
following the http://support.microsoft.com/default.aspx?scid=kb;en-us;201716
info.

But I continue to have the same problem.

Please help me. Thank you in advance

This might occur if you set the Connection for the ADOX catalog object
to the connection string returned by CurrentProject.Connnection. You
must open a new connection.
The code below works here:

Public Function CreateTable()

On Error GoTo ErrHandler

Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table
Dim objCol As ADOX.Column
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.ConnectionString = "provider=SQLOLEDB.1;Security Info=False;" & _
"Data Source=<YourServerNameHere>;Integrated Security=SSPI;" & _
"Initial Catalog=Northwind"

conn.Open
Set objCat = New ADOX.Catalog
Set objTbl = New ADOX.Table
Set objCat.ActiveConnection = conn
objTbl.Columns.Append "TestField", adInteger
objTbl.Name = "TestTable"
objCat.Tables.Append objTbl
Set objTbl = Nothing
Set objCat = Nothing
conn.Close
Set conn = Nothing

ExitProc:
On Error GoTo 0
CreateTable = True
Exit Function
ErrHandler:
'Might add some cleanupcode here for objtbl and objCat
' as well as the conn object
CreateTable = False
Resume ExitProc

End Function

 

[Sylvain Lafontaine]

Hum, I would think about using the Application.RefreshDatabaseWin­dow;
however, you may run into synchronisation issue because the call will be
done by ADP but still not totally executed by SQL-Server. So, it is
possible that you will to wait a few seconds before issuing this command.

Also, I remember some problems with the RefreshDatabaseWin­dow command on
A2000 and maybe 2002 too.

 

[Guest]

Thank you very much. It was exactly what I needed.