Delete current record from front and back end

[danitrin via AccessMonster.com]

I'm not sure where to post this, so I'm trying here first. I have inherited
a database that is split between a front end and a back end. In both the
front and the back in there is a table called tblABLExamHistory. I'm using
the code to delete records from this table. However, this code only deletes
the record from the front end. Each time a form is opened in the front end,
it refreshes the data in the front end with the data in the back end, which
causes the record that was just deleted from the front end to reappear
(because it's not been deleted from the back end). HOw can I go about
getting the record to delete from both places? I'm not insterested in
redesigning the database, I just want to make this feature work.

Dim strSQL As String
Dim db As DAO.Database
If MsgBox("Are you sure you want to delete this record?", vbQuestion +
vbYesNo, "Delete Record") = vbYes Then
Set db = CurrentDb
strSQL = "DELETE * FROM tblablExamHistory WHERE [ablexamId] = " & _
Me.txtABLExamID
db.Execute strSQL
End If
DoCmd.Close

 

[Tom van Stiphout]

On Mon, 11 May 2009 12:52:45 GMT, "danitrin via AccessMonster.com"

That would be highly unusual. Take a very careful look at the table in
the front end (FE): does it have an arrow in front of the icon? That
would indicate this is a linked table, linking to the real table in
the BE. Such link stores the location of the BE table, and you can see
it in the Linked Table Manager. Perhaps it is not the location that
you thought this link was pointing to.
If there is not an arrow indicating a linked table, more than likely
there should be.

-Tom.
Microsoft Access MVP

 

[Arvin Meyer MVP]

Databases which are split, should never have the same tables both linked and
local. As long as there is no
duplicate data, you can append the front-end records to the back-end, then
delete the front-end. If the records are duplicated, just delete the
front-end table and link to the back-end one.

As usual, when doing drastic changes, always work on a copy.

 

[danitrin via AccessMonster.com]

The tables are not linked. The data in the back end is the same as the front
end. I don't know why it was designed this way, but I have no plans of
rebuilding or linking the tables at this point. I just need to get the code
to work with what I have right now. I'll keep playing around and see what I
can figure out. thanks

 

[Tom van Stiphout]

On Mon, 11 May 2009 14:25:41 GMT, "danitrin via AccessMonster.com"

With all due respect, but that is short-sighted. It is not possible
for mere mortals to keep two tables in sync. Today you're struggling
with a Delete query, tomorrow you may find out that one table is being
Updated and the other is not, etc. If there is a Really Good Reason
things are the way they are then you would know that, or there would
be a comment in the code to that effect, or the original developer
would be able to tell you so.
That's why we are pushing for a fix, not a band-aid.

-Tom.
Microsoft Access MVP

 

[danitrin via AccessMonster.com]

I understand. The developer did not make any comments in any of their code,
which make it difficult for me to work on. I'm only try to fix this issue as
a favor so it's not up to me to correct the issue at this point. Thanks for
all of your help though.

 

[Arvin Meyer MVP]

If the back-end tables are not linked to the front-end, you have no way to
write to them from a form. I suspect that the database was split and the
front-end of the split was inadvertently deleted.

It makes no sense what-so-ever to have 2 sets of tables with the same data.

 

[Tony Toews [MVP]]

That would be highly unusual. Take a very careful look at the table in
the front end (FE): does it have an arrow in front of the icon? That
would indicate this is a linked table, linking to the real table in
the BE. Such link stores the location of the BE table, and you can see
it in the Linked Table Manager. Perhaps it is not the location that
you thought this link was pointing to.

If you let the mouse hover over the table name you should see the BE
path and name pop up.

Tony