Sorry about that BlueSpruce. Thanks for all of your help. I very much appreciate everything you have written. As I have said repeatedly, this application is for a special group of people. They are not techies.
Sorry about that BlueSpruce. Thanks for all of your help. I very much appreciate everything you have written. As I have said repeatedly, this application is for a special group of people. They are not techies.
Not true. I solved this a long time ago, but lost in the noise.
FYI. I wrote none of this code, and let AI do it.
1. Add an Is_Sensitive field to all tables so that you can mark records as sensitive.
Code:
Sub LoopThroughUserTablesAddSensitve()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
' Set the database object to the current database
Set db = CurrentDb
' Loop through each TableDef object in the TableDefs collection
For Each tdf In db.TableDefs
' Check if the table is a user-defined table (not a system or temporary table)
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
' Print the name of the user-defined table to the Immediate Window
' Debug.Print tdf.Name
AddBooleanField (tdf.Name)
End If
Next
' Clean up objects
Set tdf = Nothing
Set db = Nothing
End Sub
Sub AddBooleanField(TableName As String)
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
' Set a reference to the current database
Set db = CurrentDb
' Set a reference to the table where you want to add the field
' Replace "YourTableName" with the actual name of your table
Set tbl = db.TableDefs(TableName)
' Create a new field object
' dbBoolean corresponds to the Yes/No data type in Access
Set fld = tbl.CreateField("Is_Sensitive", dbBoolean)
' Append the new field to the table's fields collection
tbl.Fields.Append fld
' Optional: Set a default value for the new field
' fld.DefaultValue = "No" ' Sets the default to False
' Clean up objects
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
'MsgBox "Boolean field 'Is_Senstive' added to 'YourTableName' successfully.", vbInformation
End Sub
2. Tag records as sensitve.
Run the code to delete all records in all tables that are sensitive.
Code:
Sub LoopThroughUserTablesDeleteSensitive()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
' Set the database object to the current database
Set db = CurrentDb
' Loop through each TableDef object in the TableDefs collection
For Each tdf In db.TableDefs
' Check if the table is a user-defined table (not a system or temporary table)
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
' Print the name of the user-defined table to the Immediate Window
' Debug.Print tdf.Name
If TableHasField(tdf.Name, "Is_Sensitive") Then DeleteSensitiveRecords tdf.Name
End If
Next
' Clean up objects
Set tdf = Nothing
Set db = Nothing
End Sub
Function TableHasField(ByVal TableName As String, ByVal fieldName As String) As Boolean
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
On Error GoTo Err_Handler ' Enable error handling
' Get the TableDef object for the specified table
Set tdf = db.TableDefs(TableName)
' Iterate through the fields collection
For Each fld In tdf.Fields
If fld.Name = fieldName Then
TableHasField = True ' Field found
Exit Function
End If
Next fld
TableHasField = False ' Field not found
Exit_Handler:
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function
Err_Handler:
' Handle the error if the table does not exist
If Err.Number = 3265 Then ' Item not found in this collection (table doesn't exist)
MsgBox "Table '" & TableName & "' not found.", vbCritical
TableHasField = False
Else
MsgBox "An error occurred: " & Err.Description, vbCritical
End If
Resume Exit_Handler
End Function
Sub DeleteSensitiveRecords(TableName As String)
On Error GoTo DeleteSensitiveRecords_Error
Dim db As DAO.Database
Dim strSQL As String
' Set the current database
Set db = CurrentDb
' Construct the SQL DELETE statement
' Replace "YourTableName" with the actual name of your table
strSQL = "DELETE FROM [" & TableName & "] WHERE Is_Sensitive = True"
' Execute the SQL statement
' dbFailOnError ensures that an error is raised if the operation fails
db.Execute strSQL, dbFailOnError
' Clean up
Set db = Nothing
MsgBox "Sensitive records deleted successfully from " & TableName, vbInformation
On Error GoTo 0
Exit Sub
DeleteSensitiveRecords_Error:
If Err.Number = 3200 Then
MsgBox "Could not delete sensitive records from " & TableName & " because related records exist and referenitial integrity is enforced."
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteSensitiveRecords, line " & Erl & "."
Debug.Print TableName & " " & vbCrLf & strSQL
End If
End Sub
I added a little error checking to tell you if it cannot delete a record that has related child records.
Thanks, Pete. I had to reply to so many posts this morning that I forgot yours.
Folks wanted to beat me up for many reasons, I always try to thank everyone. Unfortunately, they don't know what I accept and what I save for later.
I actually don't reject anything. But I just don't have the programming skills to implement all the suggestions.
I have no idea why some folks are so much for splitting. I read occasionally in UGs that someone is not using a split database. Those people never get beat up the way I get.
I understand why developers do not use MVFs. I use them a lot in simple ways. You have taught me more about them in the past two weeks than I learned in almost two decades. Thank you.
I will try to add your code above to my database as soon as I can. I understand about 70 percent of it.
This thread reminds me of the Woody Allen movie Sleeper - specifically reminding me of the comment that watching old Howard Cosell interviews was like a form of torture. Or What's Up, Tiger Lily - where in one scene a protagonist says "I'd call you a sadistic sodomistic necrophile, but that would be beating a dead horse." This thread is the "gift" that keeps on giving.
Not everyone needs a split db, but your specific scenario warrants it (almost. If the tables were settled).
1. You absolutely need to split a db if you are in a multi user db where people are simultaneously working with the same data.
2. Or where you want users to be able to work on their database but you plan to push out changes to the forms, reports, queries, and code. This way they can continue to work without interruption while you can develop enhancements and fixes. Then push them the update.
However in your case that is not really the scenario because you plan to continue to modify tables as well.
Not everyone needs a split db, but your specific scenario warrants it (almost. If the tables were settled).
1. You absolutely need to split a db if you are in a multi user db where people are simultaneously working with the same data.
2. Or where you want users to be able to work on their database but you plan to push out changes to the forms, reports, queries, and code. This way they can continue to work without interruption while you can develop enhancements and fixes. Then push them the update.
However in your case that is not really the scenario because you plan to continue to modify tables as well.
Pete,
Number 1 does not apply at all. My brother lives in northern Florida, I have a sister in Miami, a son in Denver, a brother in Southern California and one in Massachusetts. They won't be using the same datasets. The database is probably the same as MS Money.
Number 2 is about the same situation. If someone in a household comes down with another disease, they will have to mail me their .Accdb file so I can add the disease, symptoms, meds, etc. to it. Or I may be able to tell the person how to copy and paste objects over the phone.
Most, if not all of the forms in the Personal Finance section are structured so the user can add other expenses and sources of income to that section. The Grocery domain, for example, lets the user add new stores with their locations and value propositions. Value propositions can be discounts on certain products such as gasoline, and they can be delivery of products and afterhour services.
Number 2 is about the same situation. If someone in a household comes down with another disease, they will have to mail me their .Accdb file so I can add the disease, symptoms, meds, etc. to it. Or I may be able to tell the person how to copy and paste objects over the phone.
If the references tables are not editable and you manage what goes in the lists, then the reference tables could stay in the front end but then you cannot enforce referential integrity.
I then would then keep the source reference tables in the front end, but when the user gets a new front end it updates their reference tables from the authoritative tables. Then the users always have the updated lists without requiring to recover the databases and manually update them.
If the references tables are not editable and you manage what goes in the lists, then the reference tables could stay in the front end but then you cannot enforce referential integrity.
I then would then keep the source reference tables in the front end, but when the user gets a new front end it updates their reference tables from the authoritative tables. Then the users always have the updated lists without requiring to recover the databases and manually update them.
Some lookup tables can remain in the FE. It depends on whether or not the household needs to update them or not.
If the information in a lookup table comes from the internet, then that table needs to be updated. WIth thousands of diseases and new ones being discovered all the time, lkuDiseases needs to be updatable, but not until someone in that family gets the disease. I will need to think about where to put lkuDiseases, in FE or BE? All 1,000 images will be in the FE because they are in the forms.
This is why I don't want to split yet. I can't make these decisions until the database is finished and I have defined everything. There's no problem with forms and reports.
I tried once to create a relationship map for my database with 4,000 tables, and Access locked up after about an hour. Once again, I design databases using the 5 Ws
FYI, you do not have a relationship diagram, you have an image of default query joins.
There is no referential integrity enforced on any of those relationships. So what's the point? This diagram only serves to set the default join type and all are inner joins.
FYI, you do not have a relationship diagram, you have an image of default query joins.
There is no referential integrity enforced on any of those relationships. So what's the point? This diagram only serves to set the default join type and all are inner joins.
You are right Pete. There's very little value in a graphic like this. I rarely look at these things. Years ago, I used Visio. But I really didn't use it to the max either.
There's too much to these technologies for me to understand. So I do what I can to build useful applications, knowing that I am just scratching the surface. I thank folks on AWF and those who were on UA for comments. My favorite way to learn is to study screenshots of forms and reports. I try to attend all the Access User Groups meetings that I can. I attended local meetings for about 15 years.
@DakotaRidge I don't want to divert this thread to COVID but on the news just now was the results of recent studies that are blaming the proliferation of new COVID boosters on the mutation of COVID and the fact that the more boosters you get, the more variants of COVID you are sickened by and probably the more likely you are to die from one of them. Eventually, if you are sickened by enough COVID mutations, one of them is going to get you. So, yes, if you are compulsively getting every new booster that comes down the pike, you should be very worried about dying from COVID. But hiding away in your house isn't going to save you unless you never open your door to anyone.
Thanks, Pat. I just talked to my brother in Florida about all the stuff that is happening in the world. He mentioned multiple shootings, multiple hurricanes, widespread flooding and fires. Being a volcanic geologist, I mentioned the likely eruption in Alaska.
As you know, I track cases of Covid and food recalls, it is never ending.
When you read those reports, you have to consider who is saying things. There's always two sides. I put new locks on my front door last month to keep me inside more. They are working
This is why I don't want to split yet. I can't make these decisions until the database is finished and I have defined everything. There's no problem with forms and reports.
You are right Pete. There's very little value in a graphic like this. I rarely look at these things. Years ago, I used Visio. But I really didn't use it to the max either.
There's too much to these technologies for me to understand. So I do what I can to build useful applications, knowing that I am just scratching the surface. I thank folks on AWF and those who were on UA for comments. My favorite way to learn is to study screenshots of forms and reports. I try to attend all the Access User Groups meetings that I can. I attended local meetings for about 15 years.
Yes, Duane. One of the things that I learned circa 1998 was that databases are never finished. The same goes for any technology. Look at Word, Excel, and all the others.
What I should have written is "When I distribute my database." My database will never be finished. You nailed it.
You are right Pete. There's very little value in a graphic like this. I rarely look at these things. Years ago, I used Visio. But I really didn't use it to the max either.
You kind of missed the point. The relationship diagram is invaluable if you use it to enforce referential integrity. It has very limited value if not. Then you have default joins but no way to enforce data integrity.
You kind of missed the point. The relationship diagram is invaluable if you use it to enforce referential integrity. It has very limited value if not. Then you have default joins but no way to enforce data integrity.
Yes, Duane. One of the things that I learned circa 1998 was that databases are never finished. The same goes for any technology. Look at Word, Excel, and all the others.
What I should have written is "When I distribute my database." My database will never be finished. You nailed it.
But you said you can’t make decisions about splitting until the database is finished. Apparently you will never split and quite possibly never deliver a working application that will be worth all the effort you are putting into it.
But you said you can’t make decisions about splitting until the database is finished. Apparently you will never split and quite possibly never deliver a working application that will be worth all the effort you are putting into it.
What can I say, I misstate things from time to time.
I just sent my family an email showing them how they will be able to track their grocery purchases. I want them to be able to track purchases at all stores, and then view only purchases made at Walmart and other stores in their area. Today, I added three checkboxes to the table to help folks track the ways they try to save money. I suppose developers will say that I should not have added more fields to the source table and then changed several queries.
But you said you can’t make decisions about splitting until the database is finished. Apparently you will never split and quite possibly never deliver a working application that will be worth all the effort you are putting into it.
I was hoping he would let MajP normalize the tables, split the app, and return it to him so he could see the benefits and continue building out his app, but it doesn't look like he's going to go there. That's why I gave up trying to help and ignored him.
