Deleting multiple rows with a common field from multiple tables

M

martinmike2

Hello,

I have an employee database with an archive table. I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table. The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct. I have
not tested this yet because it just dosn't look right to me.

Another problem is that some of the tables have multiple records
pertaining to each person. All of the fields have a common field of
[SSN]. Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.

My code is as follows:

Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.Edit
rst.Delete
rst.Update
Loop
Set rst = Nothing
End If

End Sub

this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?

Any help is greatly appreciated.

-Mike
 
M

martinmike2

Hello,

I have an employee database with an archive table.  I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table.  The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct.  I have
not tested this yet because it just dosn't look right to me.

Another problem is that some of the tables have multiple records
pertaining to each person.  All of the fields have a common field of
[SSN].  Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.

My code is as follows:

Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.Edit
    rst.Delete
    rst.Update
    Loop
    Set rst = Nothing
End If

End Sub

this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?

Any help is greatly appreciated.

-Mike
Click to expand...

I just caught one of my problems, I had no DO statement. New code
is:

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.MoveFirst
Do
rst.Edit
rst.Delete
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End If
 
K

Klatuu

A delete query would be less code and much faster.

Dim strSQL As String

strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError

As written, you are expecting SSN to be a numeric field. If it is a text
field (which most time SSN fields are), the syntax should be:

strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN & """"

If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:

Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String

Set dbf = Currentdb
Set tdfs = dbf.TableDefs
strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
For Each tdf in tdfs
Next tdf

Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP


martinmike2 said:
Hello,

I have an employee database with an archive table. I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table. The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct. I have
not tested this yet because it just dosn't look right to me.

Another problem is that some of the tables have multiple records
pertaining to each person. All of the fields have a common field of
[SSN]. Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.

My code is as follows:

Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.Edit
rst.Delete
rst.Update
Loop
Set rst = Nothing
End If

End Sub

this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?

Any help is greatly appreciated.

-Mike
Click to expand...

I just caught one of my problems, I had no DO statement. New code
is:

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.MoveFirst
Do
rst.Edit
rst.Delete
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End If
Click to expand...
 
M

martinmike2

A delete query would be less code and much faster.

Dim strSQL As String

    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
    Currentdb.Execute strSQL, dbFailOnError

As written, you are expecting SSN to be a numeric field.  If it is a text
field (which most time SSN fields are), the syntax should be:

    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN & """"

If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:

Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String

    Set dbf = Currentdb
    Set tdfs = dbf.TableDefs
        strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
        Currentdb.Execute strSQL, dbFailOnError
    For Each tdf in tdfs
    Next tdf

    Set tdf = Nothing
    Set tdfs = Nothing
    Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP



martinmike2 said:
Hello,
I have an employee database with an archive table.  I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table.  The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct.  I have
not tested this yet because it just dosn't look right to me.
Another problem is that some of the tables have multiple records
pertaining to each person.  All of the fields have a common field of
[SSN].  Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
My code is as follows:
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.Edit
    rst.Delete
    rst.Update
    Loop
    Set rst = Nothing
End If
End Sub
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Any help is greatly appreciated.
-Mike
Click to expand...
Click to expand...
I just caught one of my problems,  I had no DO statement.  New code
is:
Click to expand...
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.MoveFirst
    Do
    rst.Edit
    rst.Delete
    rst.Update
    rst.MoveNext
    Loop
    Set rst = Nothing
End If- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Dave,

Thank you for the response. I guess I was a little over confident. I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")
 
K

Klatuu

"Coding Issues" doesn't give me much to work with, but the first thing that
pops out is you don't use the * in an Append query
Other than that, I don't see an error (doesn't mean it isn't there, just
means I don't see it)

"INSERT INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST], [TRANSFERED
TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")

Let me know what is or is not happening.
--
Dave Hargis, Microsoft Access MVP


martinmike2 said:
A delete query would be less code and much faster.

Dim strSQL As String

strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError

As written, you are expecting SSN to be a numeric field. If it is a text
field (which most time SSN fields are), the syntax should be:

strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN & """"

If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:

Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String

Set dbf = Currentdb
Set tdfs = dbf.TableDefs
strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
For Each tdf in tdfs
Next tdf

Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP



martinmike2 said:
Hello,
Click to expand...
I have an employee database with an archive table. I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table. The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct. I have
not tested this yet because it just dosn't look right to me.
Click to expand...
Another problem is that some of the tables have multiple records
pertaining to each person. All of the fields have a common field of
[SSN]. Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
Click to expand...
My code is as follows:
Click to expand...
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Click to expand...
Set db = CurrentDb
Click to expand...
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.Edit
rst.Delete
rst.Update
Loop
Set rst = Nothing
End If
Click to expand...
End Sub
Click to expand...
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Click to expand...
Any help is greatly appreciated.

I just caught one of my problems, I had no DO statement. New code
is:
Click to expand...
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.MoveFirst
Do
rst.Edit
rst.Delete
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End If- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Dave,

Thank you for the response. I guess I was a little over confident. I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")
Click to expand...
 
M

martinmike2

"Coding Issues" doesn't give me much to work with, but the first thing that
pops out is you don't use the * in an Append query
Other than that, I don't see an error (doesn't mean it isn't there, just
means I don't see it)

"INSERT INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST], [TRANSFERED
TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")

Let me know what is or is not happening.
--
Dave Hargis, Microsoft Access MVP



martinmike2 said:
A delete query would be less code and much faster.
Dim strSQL As String
    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
    Currentdb.Execute strSQL, dbFailOnError
As written, you are expecting SSN to be a numeric field.  If it is a text
field (which most time SSN fields are), the syntax should be:
    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN &""""
If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:
Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String
    Set dbf = Currentdb
    Set tdfs = dbf.TableDefs
        strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
        Currentdb.Execute strSQL, dbFailOnError
    For Each tdf in tdfs
    Next tdf
    Set tdf = Nothing
    Set tdfs = Nothing
    Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP
:
Hello,
I have an employee database with an archive table.  I am tryingto,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table.  The data that isgoing
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct.  Ihave
not tested this yet because it just dosn't look right to me.
Another problem is that some of the tables have multiple records
pertaining to each person.  All of the fields have a common field of
[SSN].  Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
My code is as follows:
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.Edit
    rst.Delete
    rst.Update
    Loop
    Set rst = Nothing
End If
End Sub
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Any help is greatly appreciated.
-Mike
I just caught one of my problems,  I had no DO statement.  New code
is:
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.MoveFirst
    Do
    rst.Edit
    rst.Delete
    rst.Update
    rst.MoveNext
    Loop
    Set rst = Nothing
End If- Hide quoted text -
- Show quoted text -
Click to expand...
Dave,
Click to expand...

Thank you for the response.  I guess I was a little over confident.  I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")- Hide quoted text-
Click to expand...

- Show quoted text -
Click to expand...

Dave,

I'm sorry about the lack of detail.

When I click on the "transfer" button, I recieve a Run-Time Error
'3078: Cannot find the input table or query.


I checked to make sure it exists and is spelled correctly, it is.
 
M

martinmike2

"Coding Issues" doesn't give me much to work with, but the first thing that
pops out is you don't use the * in an Append query
Other than that, I don't see an error (doesn't mean it isn't there, just
means I don't see it)
Click to expand...
"INSERT INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST], [TRANSFERED
TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")
Click to expand...
Let me know what is or is not happening.
Click to expand...
martinmike2 said:
A delete query would be less code and much faster.
Dim strSQL As String
    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
    Currentdb.Execute strSQL, dbFailOnError
As written, you are expecting SSN to be a numeric field.  If it is a text
field (which most time SSN fields are), the syntax should be:
    strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN& """"
If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:
Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String
    Set dbf = Currentdb
    Set tdfs = dbf.TableDefs
        strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
        Currentdb.Execute strSQL, dbFailOnError
    For Each tdf in tdfs
    Next tdf
    Set tdf = Nothing
    Set tdfs = Nothing
    Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP
:
Hello,
I have an employee database with an archive table.  I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table.  The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct.  I have
not tested this yet because it just dosn't look right to me.
Another problem is that some of the tables have multiple records
pertaining to each person.  All of the fields have a common field of
[SSN].  Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
My code is as follows:
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.Edit
    rst.Delete
    rst.Update
    Loop
    Set rst = Nothing
End If
End Sub
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Any help is greatly appreciated.
-Mike
I just caught one of my problems,  I had no DO statement.  New code
is:
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
    Set rst = Nothing
Else
    rst.MoveFirst
    Do
    rst.Edit
    rst.Delete
    rst.Update
    rst.MoveNext
    Loop
    Set rst = Nothing
End If- Hide quoted text -
- Show quoted text -
Dave,
Thank you for the response.  I guess I was a little over confident. I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Dave,

I'm sorry about the lack of detail.

When I click on the "transfer" button, I recieve a Run-Time Error
'3078: Cannot find the input table or query.

I checked to make sure it exists and is spelled correctly, it is.- Hide quoted text -

- Show quoted text -
Click to expand...

Never mind. I just realized my mistake. I had the variable
misspelled.
 
K

Klatuu

Sorry, but I don't see the problem from here.
You have tables named EDVR and PERS, right?

What happens if you try to build the query in the query builder?
--
Dave Hargis, Microsoft Access MVP


martinmike2 said:
"Coding Issues" doesn't give me much to work with, but the first thing that
pops out is you don't use the * in an Append query
Other than that, I don't see an error (doesn't mean it isn't there, just
means I don't see it)

"INSERT INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST], [TRANSFERED
TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")

Let me know what is or is not happening.
--
Dave Hargis, Microsoft Access MVP



martinmike2 said:
A delete query would be less code and much faster.
Click to expand...
Dim strSQL As String
Click to expand...
strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
Click to expand...
As written, you are expecting SSN to be a numeric field. If it is a text
field (which most time SSN fields are), the syntax should be:
Click to expand...
strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN & """"
Click to expand...
If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:
Click to expand...
Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String
Click to expand...
Set dbf = Currentdb
Set tdfs = dbf.TableDefs
strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
For Each tdf in tdfs
Next tdf
Click to expand...
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
Click to expand...
:
Hello,
Click to expand...
I have an employee database with an archive table. I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table. The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct. I have
not tested this yet because it just dosn't look right to me.
Click to expand...
Another problem is that some of the tables have multiple records
pertaining to each person. All of the fields have a common field of
[SSN]. Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
Click to expand...
My code is as follows:
Click to expand...
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Click to expand...
Set db = CurrentDb
Click to expand...
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.Edit
rst.Delete
rst.Update
Loop
Set rst = Nothing
End If
Click to expand...
End Sub
Click to expand...
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Click to expand...
Any help is greatly appreciated.

I just caught one of my problems, I had no DO statement. New code
is:
Click to expand...
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.MoveFirst
Do
rst.Edit
rst.Delete
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End If- Hide quoted text -
Click to expand...
- Show quoted text -
Dave,
Click to expand...

Thank you for the response. I guess I was a little over confident. I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Dave,

I'm sorry about the lack of detail.

When I click on the "transfer" button, I recieve a Run-Time Error
'3078: Cannot find the input table or query.


I checked to make sure it exists and is spelled correctly, it is.
Click to expand...
 
M

martinmike2

Sorry, but I don't see the problem from here.
You have tables named EDVR and PERS, right?

What happens if you try to build the query in the query builder?
--
Dave Hargis, Microsoft Access MVP

martinmike2 said:
"Coding Issues" doesn't give me much to work with, but the first thing that
pops out is you don't use the * in an Append query
Other than that, I don't see an error (doesn't mean it isn't there, just
means I don't see it)
"INSERT INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST], [TRANSFERED
TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")
Let me know what is or is not happening.
--
Dave Hargis, Microsoft Access MVP
:
A delete query would be less code and much faster.
Dim strSQL As String
strSQL = "DELETE * FROM tblSOD WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
As written, you are expecting SSN to be a numeric field. If it is a text
field (which most time SSN fields are), the syntax should be:
strSQL = "DELETE * FROM tblSOD WHERE [SSN] = """ & gSSN & """"
If you really mean "Every" table in the database, you could loop throught
the TableDefs collection and automate the entire process:
Dim dbf As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim strSQL As String
Set dbf = Currentdb
Set tdfs = dbf.TableDefs
strSQL = "DELETE * FROM """ & tdf.Name & """ WHERE [SSN] = " & gSSN
Currentdb.Execute strSQL, dbFailOnError
For Each tdf in tdfs
Next tdf
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing
--
Dave Hargis, Microsoft Access MVP
:
Hello,
I have an employee database with an archive table. I am trying to,
when a person leaves, to remove their data from all of my tables and
send only specific data to the archive table. The data that is going
to the archive table is in a table by itself and is not my problem.
My problem is that I dont think my code is actually correct. I have
not tested this yet because it just dosn't look right to me.
Another problem is that some of the tables have multiple records
pertaining to each person. All of the fields have a common field of
[SSN]. Part of my problem is that I dont know how to delete multiple
rows from a table with VBA.
My code is as follows:
Private Sub cmdTransfer_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.Edit
rst.Delete
rst.Update
Loop
Set rst = Nothing
End If
End Sub
this sequence would then be repeated for each table in the database.
Is there a less cumbersome way?
Any help is greatly appreciated.
-Mike
I just caught one of my problems, I had no DO statement. New code
is:
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblSOD WHERE
[SSN] = " & gSSN)
If IsNull(rst) Then
Set rst = Nothing
Else
rst.MoveFirst
Do
rst.Edit
rst.Delete
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End If- Hide quoted text -
- Show quoted text -
Dave,
Thank you for the response. I guess I was a little over confident. I
am having issues with appending to the archive table with VBA.
CODE:
("INSERT * INTO [ARCHIVE TABLE] ( UIC, SSN, RATE, [LAST], [FIRST],
[TRANSFERED TO], [TRANSFERED NOTES], [CHECK-OUT DATE] )" & _
" SELECT EDVR.UIC, PERS.SSN, EDVR.A_RATE_ABR, PERS.[NAME LAST], PERS.
[NAME FIRST], PERS.TRANFERRED_TO, PERS.TRANSFER_NOTES,
PERS.CHECK_OUT_DATE " & _
"FROM EDVR INNER JOIN PERS ON EDVR.SSN = PERS.SSN")- Hide quoted text -
- Show quoted text -
Click to expand...
Dave,
Click to expand...

I'm sorry about the lack of detail.
Click to expand...
When I click on the "transfer" button, I recieve a Run-Time Error
'3078: Cannot find the input table or query.
Click to expand...
I checked to make sure it exists and is spelled correctly, it is.
Click to expand...
Click to expand...

I figured it out Dave. Thank you for the help though. I had the
variable in the "Currentdb.Execute" portion misspelled. Fixed the
spelling and whammo, started working.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting a single field of a single record 3
Consolidation Table from Different Excel Tabs with Different Format 1
Access 2010 DB not fuctional with multiple users 2
Import multiple Access Queries 0
Importing Contacts From MS Outlook 1
Dealing with a Pivot Table (when I don't want to) 0
Updateing a record with a Null value field, fails? 5
Renumber field 8

Top