Change SQ Server Owner from Access

[jmcg]

I am trying to call a command to change a table owner to dbo.
Our company set-up means that all tables by default are create with my ID as owner.
However for other applications that use them they need to owned by the dbo.

I have the VBA that creates the table via ODBC and need to know if it possible to call the sp_changeobjectowner procedure from access.

I have tried

db.Execute"EXEC sp_changeobjectowner [ODBC;DRIVER=SQL Server;SERVER=SQLserver1;DATABASE=dbase1.MeOIwner.Table, 'dbo'

but is fails as it it looking for SQL type start of SELECT, INSERT, etc

It there another way to do this?

There are around 40 tables that need to be processed so I need to it to be automated.

 

[jrbarnett]

Something like:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
With cn
  .ConnectionString = "Provider=SQLOLEDB;Server=SQLServer1;Database=DB1;User id=xxx;PWD=yyy"
  .Open
End With

cn.Execute "EXEC sp_changeobjectowner 'dbase1.MeOIwner.Table', 'dbo'"

cn.Close

An alternative - if your user id supports it - is to create the tables with DBO as the owner.
Something like:
CREATE TABLE dbo.tablename

if you can change the code that runs that.

John

 

[jmcg]

Smashing thanks