Clear out data (1 Viewer)

[DakotaRidge]

Yes I know that. That's why I'm asking him what HIS plan actually is, since relinking is 'out of the question'.

About 80% of that image is lookup tables. As I said when you send me the db I will crush them all down into one single table.

Sorry, Pete, I can only send this database to family members via the US mail or by hand delivery. It has sensitive information. Some of the data falls under HIPAA.

 

[MajP]

@DakotaRidge,
No problem, I already solved that issue. When ready I will send to a link to a google drive where you can upload the database, and I will start to slowly whittle it down. Just delete the sensitive records using the provided code. To make it understandable you need to leave in sufficient data. It is hard to follow the intent if you delete everything.

 

[MajP]

In the mean time you can simply delete out all the data and send me the empty db. I can at least see the forms and proposed tables. The relinking is not an issue as long as your users always put the BE in the same location as the FE. That is easy to ensure. You have an application Folder and inside you have both the front end and back end in the folder. Also you will probably need an Attachments and Images folder for holding those things.
Looking forward to getting started. I am sure it will take me a while, but I am sure I can get it down to a very reasonable size probably 95% reduction of objects. Unfortunately I will be providing vba not macros since macros are a waste of time.

 

[DakotaRidge]

I make one form and load the images dynamically.
Something like this where you can show multiple ship floor plans and "geo locate" tasks to complete.

Solved - User made command button that can be moved with mouse?

haha! We got plenty of liferafts! Thanks MajP, is working good. Attached is idea of the jobs pane. I think when a new dot is moved to the plan/deck then it would ideally open up a new record/job for this new dot that has been added, this would highlight as a new job in the jobs detail pane...

www.access-programmers.co.uk

Pete, my forms are a mix of data, calculated values, graphics (maps), and speech. The aggregated values in the main forms use data in the subforms,

There are boxes that use conditional formatting. For example, if a person's A1C test result is over 7 a box turns red. If the A1C is low then the box will also turn red. And if the A1C is near the low or high end of the normal range for his/her age and race, the box will turn yellow.

The idea is to encourage the person to pay more attention to hos/her test results when they are out of range or near the top or bottom.

Also, if medical test results change over time, boxes will change color. That tells the person that their meds are working or not. If they are not working, the person may use one of the actions suggested by the database. Suggestions come from the medical literature, and the person can edit them.

Other boxes count the number of checkboxes on a subform that are true and the number that are false.

To keep from having to use numerous If-Then statements, I use multiple forms because the measures are different. Take utilities, for example, water is sold where I live in gallons, electricity is sold by the kilowatt hour, and gas is sold in another unit. I could use If-Then to display units on each Utilities form/report, but I prefer using different forms and reports. That way the aggregated information is contextual, the way people think about their expenses.

So each form also shows the cost of water, gas, and electricity each month. Some forms show the amount of a utility used by the month, for the quarter, and for the year. These are simple calculations done on the form. Other expenses like dairy deliveries, groceries, and gas and oil are tracked the same way.

This is why I think I need so many forms. It is not just a matter of showing different graphics. It is also about showing the current analytics at the same time.

 

[DakotaRidge]

@DakotaRidge,
No problem, I already solved that issue. When ready I will send to a link to a google drive where you can upload the database, and I will start to slowly whittle it down. Just delete the sensitive records using the provided code. To make it understandable you need to leave in sufficient data. It is hard to follow the intent if you delete everything.

Sorry Pete, I don't touch Google. I would remove it from my phone, but it is the OS.

 

[Isaac]

Sorry, Pete, I can only send this database to family members via the US mail or by hand delivery. It has sensitive information. Some of the data falls under HIPAA.

HIPAA doesn't even apply to you, you're not a covered entity
Just more BS

 

[DakotaRidge]

That's it, I'm done with trying to help you. This has been a big waste of time, I'm out of this...

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.

 

[AccessBlaster]

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.

Why not empty the data and allow MajP to exam it privately?

 

[NauticalGent]

It took 219 posts to get to this:

View attachment 120917

I cant do that. This like watching Howard Stern - I HAVE to stick around to see what he says next...

I may need a 12-step program...? Nah...

 

[DakotaRidge]

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.

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.

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.

 

[The_Doc_Man]

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.

 

[MajP]

I read occasionally in UGs that someone is not using a split database. Those people never get beat up the way I get.

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.

 

[MajP]

Sorry Pete, I don't touch Google. I would remove it from my phone, but it is the OS.

You can send email, snail mail, or use another file sharing app if you like.

 

[DakotaRidge]

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.

 

[MajP]

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.

 

[DakotaRidge]

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.

 

[MajP]

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.

 

[DakotaRidge]

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.


View attachment 120921

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]

@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

Stay Safe!

 

[DHookom]

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.

Your database will never be finished (I had to control my urge to type that in BOLD, ALL CAPS)!