ADOX & Access 2000

[Jon jeffery]

I have an Access 2000 database (which started as Access
97), which up to now has used DAO to access SQL Server 7
tables via ODBC.

I am trying to update it into an Access Porject ,
connected to SQL Server 7 using ADO. I can connect quite
happily, and use the existing tables (so far at least)
but I have hit a snag when I try and set up a new table
using ADOX. I can set up the table, and its column (it
is a single column table) OK, but when I try and append
it to cat.tables, I get an error message:
Run-time Error: 3251
Object or provider is not capable of performing requested
operation.

My SQL Server is the Desktop version, running on the same
Windows 2000 Professional machine.

I have an additional problem in that when I try
the 'Help' button, it brings up a blank help screen. If
I try Help from the VBA window, I can find an entry for
ADOX Append Tables, but when I click on it, nothing
happens.

Also, I haven't found anything in the Knowledge base.

I have tried to put the same message on the public Access
newsgroup (although as I understand that one is not a
managed Newsgroup as this one is), but evry time I try to
post it (most days for the past week or so) I get a
message saying that the service is temporarily unavailable

Help!

 

[SA]

Jon:

The error you are encountering will happen when try to use
CurrentProject.Connection as the Connection for ADOX. This is because
Access uses an intermediary provider (in access 2000 its the
MSShapeProvider, in XP its the Microsoft.Access.OLEDB.10.0 provider) to
"access" the SQL db.

To resolve, you have to create a new direct connection to the server like
the code below:

HTH

Steve Arbaugh
MS Access MVP
http://ourworld.compuserve.com/homepages/attac-cg

========== Begin Code =========
Function CreateTbl()
Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table
Dim objCol As ADOX.Column
Dim conn As Connection

Set conn = New ADODB.Connection
'Note fill in your server name in the connection string below!!!!
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
End Function

 

[Jon Jeffery]

Many thanks for this solution - I can now successfully
add and remove tables using ADOX.

However, I find that I still have a problem, in that I
don't seem to be able to open the table after adding it.

I have both refreshed the catalogue.Tables, and also done
Application.RefreshDatabaseWindow, but the latter seems
to have no effect - when I look at the database tables
window itself (even after switching to, say, forms and
back to tables), it does not appear - and when I try and
open the table, I get a failure.

What am I not doing?

Jon jeffery

-----Original Message-----
Jon:

The error you are encountering will happen when try to use
CurrentProject.Connection as the Connection for ADOX. This is because
Access uses an intermediary provider (in access 2000 its the
MSShapeProvider, in XP its the

Microsoft.Access.OLEDB.10.0 provider) to

 

[Jon Jeffery]

Further to the last note - I can now open the table in
code (I am not yet used to ADO), but can still not see it
in the database window.

Jon Jeffery