Access 2003 - SQL Server - Records apparently delete but don"t

Hi
I am using Access 2003 mdb as a front end to an application which uses
SQL Server 2000 as the backend. The two are connected using ODBC.

On one particular table (the Stock table), I have a simple form which
updates stock. When the user presses the delete key, he gets the usual
warning about not being able to undo this command, and the record
disappears from the screen. However, on some records, when he goes
back into the form, the record has not been deleted and is still
there. It doesn't happen on all records, just some. I've not so far
been able to identify a pattern.

How can this be possible? Are there any issues between Access / Odbc /
SQL which could cause this? Perhaps something like dbSeeChanges which
needs to be used when updating a recordset. However I don't use any
code when the user deletes - perhaps I should. There is no "on delete"
code.

Any help greatly appreciated because I feel like I'm going mad!

Thanks

Colin

Re: Access 2003 - SQL Server - Records apparently delete but don"t

Greetings,

There are various arguments for and against ODBC. From my personal
experience -- I have consistently had problems using ODBC between Access
and Sql Server. The problems, as you have noticed are not consistent
problems - always something different. My workaround has been to use
ADO. And there have been arguments for and against using ADO. But,
again, from my personal experience, I have had way more consistent
results using ADO between Access and Sql Server. The beauty of ADO
(classic ADO for this discussion - not ADO.Net) is that ADO supports
both Jet (Access sql) and Transact sql (sql server sql). You can use
the ADODB command object to execute action queries like insert, update,
delete. All you need is a reference to the Microsoft ActiveX Data
Object 2.x Library (ideally 2.5 or greater)

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourserver;Database=yourDB;Trusted_Connection=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Delete from your table where ID = " & txtID
cmd.Execute

Note: in tSql the Delete statement is different than a Jet sql Delete
statement - you can say

Delete From tblx where...
or
Delete tblx where ....

you don't need the * and you dont even need the From keyword. Saying
Delete tblx wont remove the table -- it will just delete all the records
if there is no Where clause to limit the Delete. To remove a table with
tSql you use the Drop keypword -- Drop Table tblx.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Access 2003 - SQL Server - Records apparently delete but don"t

Bobby wrote:
> Hi
> I am using Access 2003 mdb as a front end to an application which uses
> SQL Server 2000 as the backend. The two are connected using ODBC.
>
> On one particular table (the Stock table), I have a simple form which
> updates stock. When the user presses the delete key, he gets the usual
> warning about not being able to undo this command, and the record
> disappears from the screen. However, on some records, when he goes
> back into the form, the record has not been deleted and is still
> there. It doesn't happen on all records, just some. I've not so far
> been able to identify a pattern.
>
> How can this be possible? Are there any issues between Access / Odbc /
> SQL which could cause this? Perhaps something like dbSeeChanges which
> needs to be used when updating a recordset. However I don't use any
> code when the user deletes - perhaps I should. There is no "on delete"
> code.
>
> Any help greatly appreciated because I feel like I'm going mad!
>
> Thanks
>
> Colin

Frankly I find your description (at face value) to be difficult to believe.
In my experience edits, insertions, and deletes against an ODBC source
either work exactly as they should OR you get an error.

Is your form based on this table link directly or is it bound to a query?
If a query does the query use JUST the one table or does it join to others?
If the latter perhaps the record that you see "come back" is not coming from
the table that had the deletion applied to it.

If you open the table link datasheet directly and delete a row does that
work? For that matter are there other users and/or applications that
connect to the same ODBC source? Perhaps the record is just being
re-entered via some other mechanism after your app successfully deletes it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com