Quite the trip for someone who hasn't travelled more than 10 miles from home in five years.
So why take it to Redmond to have it done?
So just what IS your plan to update them when you have added objects. You've said your family won't/can't refresh links from an updated FE to their BE file, so how will they get the updated database without overwriting their existing data? Please explain.
Yes I know that. That's why I'm asking him what HIS plan actually is, since relinking is 'out of the question'.
This project needs to be reimagined if it has any hope of becoming useful.
Almost 200 posts into this, he/we are no closer to a solution to the original question. When he was given an answer to it, there was no follow-thru on his part to actually try it. The developers here tried (in vain) to save him from himself but he seems to take delight in explaining how his unique and wonderful personality somehow insulates him from the same fate his last endeavor suffered.
Yes, arnelgp. That's a small number for me, but Pete states that he can reduce it to 20 forms. I just counted the number of domains and there are more than 84. I just don't see how it can be done considering that I have ten personalities, three races, and three generations of users in five states to deal with. Just dealing with extroverts is a real challenge for me.
Sounds to me you need to write the books, because I have never seen these topics addressed anywhere as being important in database development.
Pete, only 20 forms? I have half that number of full-page maps now. The maps show food recalls, Covid trends, and state tax policies. You'll have to tell me how you can get more than 80 domains onto just 20 forms/reports.
Send me your database, and I promise you I can reduce it by 95%. PM me and put it in a google drive. Since it is so poorly designed I may struggle to make heads or tails of it initially, but I am confident I can radically reduce it. I may have exaggerated. It may take 40 tables and forms max.
Not true. I solved this a long time ago, but lost in the noise.
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 SubSub 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 SubI said as much in my post - a solution was offered but no action taken.
@DakotaRidge - simply put, that is the deal of the century. If you do not take him up on this, then your ignorance will have attained a level that can not be measured with modern technology.
I have posted thousands of examples of access databases, queries, and code examples. Just start searching. How about you post 1 real database example?
Actually, the only advice that I have not taken so far is to split the database and use more code. I see no need to use more code because macros do everything that I need to do. The only code that is needed for the speech. I have had that for almost two decades.Agreed, it is what it is
I make one form and load the images dynamically.
Colin, they probably are not because I use MBTI to define them.Are those split?
