Hi guys!
@ Moke123, seems that we had the same idea with the ByRef arguments yesterday.
-----------------------------
Many times, we are focusing too much on attempt to pass through the window and we don’t see the open door nearby us.
In our case, by choosing to work with SQL engine, we can retrieve the appropriate information using queries.
At first, we can have a usefull view of the 00_DeleteTables via a simple query (qryFieldSTD):
Code:
SELECT ID, FieldnameStandardized, Delete,
"LK_" & [FieldnameStandardized] AS tName
FROM 00_DeleteTables;
We can simply retrieve the existing LK_* tables via a simple query (qryLK_Tables):
Code:
SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE MSysObjects.Name Like "LK_*";
Now, we can create a left joined query with the above two (qryFieldsLKs):
Code:
SELECT qryFieldSTD.ID, qryFieldSTD.FieldnameStandardized,
qryFieldSTD.Delete, qryFieldSTD.tName, qryLK_Tables.table_name,
IsNull([table_name]) AS IsMissingLK
FROM qryFieldSTD LEFT JOIN qryLK_Tables
ON qryFieldSTD.tName = qryLK_Tables.table_name;
With another simple query, we can retrieve the selected but missing/deleted LK_ tables (qryLKsMissing):
Code:
SELECT qryFieldsLKs.tName, qryFieldsLKs.Delete, qryFieldsLKs.IsMissingLK
FROM qryFieldsLKs
WHERE (qryFieldsLKs.[Delete]=-1) AND (qryFieldsLKs.IsMissingLK=-1);
And, finally, we can retrieve the existing selected tables to be deleted (qryLKsToDrop):
Code:
SELECT table_name FROM qryFieldsLKs
WHERE ([Delete]=-1) AND (Not table_name Is Null);
So, with the assistant of SQL engine, we can avoid the tricky code and its lines can be reduced enough.
In other words, we can simply count the existing tables before and after the deletion, using the query qryLKsToDrop, and find the count of the actually deleted tables using the same recordset:
Code:
Function DropTables() As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
On Error Resume Next
Set db = CurrentDb
'Open a recordset to the existing tables to be deleted.
Set rs = db.OpenRecordset("qryLKsToDrop", dbOpenSnapshot)
With rs
If Not (.BOF And .EOF) Then
.MoveLast
'Get the initial count of existing tables to be deleted.
i = .RecordCount
While Not .BOF
'Try to delete the table.
db.Execute "DROP TABLE " & !table_name
.MovePrevious
Wend
.Requery 'to get the actual count.
If (.BOF And .EOF) Then
'All tables have been deleted.
DropTables = i
Else
'Some tables, for any reason, still remain.
.MoveLast
'Get the difference (initial-current)
DropTables = i - .RecordCount
End If
End If
End With
rs.Close
Set rs = Nothing
Set db = Nothing
On Error GoTo 0
End Function
All the rest code is for the building of the messages. You can check the results by using the form frmDeleteNonLOVs in attachment (DropTables_SQL.accdb).
I hope it was worth the waiting.
Cheers,
John