ODBC: Delete Query how to?

[Ed Warren]

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here. (Yes I
really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but cannot
figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[Guest]

You may not full admin permission to "delete" data especially to an ODBC
linked database. Please check with your SA.

 

[John Spencer]

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

 

[Ed Warren]

Thanks John, but that does not solve the problem. Oh, well, back to the
drawing board!

Ed Warren.

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here. (Yes
I really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[Ed Warren]

I have full DBA permissions on the SQL Server and Full admin on the Client,
but thanks anyway.

Ed Warren.

You may not full admin permission to "delete" data especially to an ODBC
linked database. Please check with your SA.
--
Caloyski

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here. (Yes
I
really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot
figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[John Spencer]

Does your SQL Server table have a TimeStamp field (SQL 2000 terminology - I
think it got changed in 2005 to something like RowVersion or RowID or
RowNumber)?

I've found having the TimeStamp field (not a datetime field with that name)
necessary to updating records using an ODBC connection from Access. IF I
recall correctly, it is also needed when deleting records.

Thanks John, but that does not solve the problem. Oh, well, back to the
drawing board!

Ed Warren.

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here.
(Yes I really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[Ed Warren]

Thanks, John
It does not, I will add same and see if that is the problem. Right now I'm
thinking about just getting around the ODBC and going direct via ADO and
execute a stored procedure.

Ed Warren.

Does your SQL Server table have a TimeStamp field (SQL 2000 terminology -
I think it got changed in 2005 to something like RowVersion or RowID or
RowNumber)?

I've found having the TimeStamp field (not a datetime field with that
name) necessary to updating records using an ODBC connection from Access.
IF I recall correctly, it is also needed when deleting records.

Thanks John, but that does not solve the problem. Oh, well, back to the
drawing board!

Ed Warren.

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here.
(Yes I really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[John Spencer]

Post back and let us know how you solved your problem. Executing the stored
proc will probably be fastest, but ...

Thanks, John
It does not, I will add same and see if that is the problem. Right now
I'm thinking about just getting around the ODBC and going direct via ADO
and execute a stored procedure.

Ed Warren.

Does your SQL Server table have a TimeStamp field (SQL 2000 terminology -
I think it got changed in 2005 to something like RowVersion or RowID or
RowNumber)?

I've found having the TimeStamp field (not a datetime field with that
name) necessary to updating records using an ODBC connection from Access.
IF I recall correctly, it is also needed when deleting records.

Thanks John, but that does not solve the problem. Oh, well, back to the
drawing board!

Ed Warren.

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to my
backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here.
(Yes I really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.

 

[Ed Warren]

BLUSH!!!

Sometimes you trip over the really stupid stuff.

I had 'lost' my key field in the transfer from MS Access to SQL server.
Once I rebult the primary key and relinked the tables with the primary key
everything works as it should.

Thanks for the help though, I learned something(s) about ODBC.

Ed Warren.

Post back and let us know how you solved your problem. Executing the
stored proc will probably be fastest, but ...

Thanks, John
It does not, I will add same and see if that is the problem. Right now
I'm thinking about just getting around the ODBC and going direct via ADO
and execute a stored procedure.

Ed Warren.

Does your SQL Server table have a TimeStamp field (SQL 2000
terminology - I think it got changed in 2005 to something like
RowVersion or RowID or RowNumber)?

I've found having the TimeStamp field (not a datetime field with that
name) necessary to updating records using an ODBC connection from
Access. IF I recall correctly, it is also needed when deleting records.


Thanks John, but that does not solve the problem. Oh, well, back to
the drawing board!

Ed Warren.

Try

DELETE DistinctRow [CLERK INVENTORY].*
FROM [CLERK INVENTORY];

No guarantees, but that often solves problems with deleting records.

I have switched my backend from Access to SQL Server Express 2005.
Since I need to keep my forms, reports, code, etc. I have linked to
my backend tables via ODBC
All is good, except my DELETE query no longer works.

E.g.

DELETE [CLERK INVENTORY].*
FROM [CLERK INVENTORY];


Returns the Error MsgBox("Could not delete from specified tables")


I'm new to the ODBC world, so I'm missing something very basic here.
(Yes I really want to empty the table)
I have written a procedure in SQL Server 2005 Express to do this but
cannot figure out how to call it via the ODBC and/or VBA code.

Thanks

Ed Warren.