Cannot Delete Using ADO and sqlServer 2005

[Barry George]

Greetings,

I am in the process of upgrading an Access application from Access 2000 to
Access 2007 with a backend going from sqlServer 7 to sqlServer 2005.

In the following example, at the .delete statement I get an error stating;

"Could not find server "SERVERName" in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.

The code is supposed to delete all the records from a table and has nothing
to do with a
linked server.

The connection "cnn" has already been established elsewhere.

Dim rs as new adodb.recordset
rs.open "rsTable", cnn, adOpenDynamic, adLockOptimistic
With rs
.Movefirst
Do
.Delete 'Execution stops here.
.movenext
if .eof then exit do
Loop
End with

P.S. This code works with Access 2007 connected to the sqlServer 7 data, but
not the sqlServer 2005 data.

Any suggestions would be greatly appreciated.

 

[Sylvain Lafontaine]

Well, I suppose that you have made an error when you have transferred the
data from SQL-Server 7 to 2005. For example, are you sure that rsTable is a
table and not a View to a table or a View to a linked table? It's also
possible that there is a relationship from the rsTable to something else or
a Delete trigger that is trying to update data to a badly linked server.

You should take a look at what you have actually on the SQL-Server 2005.

 

[Sylvain Lafontaine]

Also, it's usually much more faster to issue a Truncate Table statement than
to delete all record one by one. However, the Truncate Table won't work
with a table that is referenced by foreign keys. (and I'm not sure about
what will happen with any delete trigger).

 

[Barry George]

Thank you so much for your response.

I am sure there are better ways to delete the records in this table than I
have indicated here.

My interest at this time is to find out why this code does not work.

I have further discovered that the code will work on sqlExpress installed on
Windows 2000 pro but not on sqlServer 2005 installed on Windows Server 2003.

Any further suggestions?

Thanks,
Barry

 

[Sylvain Lafontaine]

Sorry but with the information provided, I cannot tell you anything more.
The only thing that comes to my mind would be a permission problem: check
that you have the delete permission on this table for your account on the
SQL-Server 2005.

 

[Tom van Stiphout]

On Fri, 9 Nov 2007 08:16:56 -0700, "Barry George"

The error seems to indicate a linked server *is* presumed by Sql
Server, even if you somewhat arrogantly say that it isn't.
Are there no linked servers listed in sysservers?
Are there no 3-part tablenames used? somelinkserver.dbo.sometable?

-Tom.