Tips for Casual to Intermediate Developers

Repairing Old and Broken Access Databases: A Step-by-Step Guide

Identifying and Fixing Common Issues:

Old Microsoft Access database applications often exhibit signs of deterioration, including malfunctioning code, sluggish performance, and frequent crashes. If you encounter these warning signs, it's time to take action.

Step-by-Step Repair Process: Follow these steps to breathe new life into your aging Access databases:

Create a New Database Container: Start by creating a new Access database and importing all objects from the old database into it. This process ensures a fresh start and helps uncover hidden issues.
Maintain References: Before importing, ensure that references are identical to the old version. This ensures compatibility and prevents errors during the import process.
Import Objects: Import local and linked tables, queries, forms, reports, modules, and macros into the new database. Ensure proper order and dependencies to maintain integrity.
Recompile Code: Open the code window and recompile all code modules to identify and fix errors. This thorough approach ensures that all code is functional and optimized.
Fix Broken Code: Address any code errors uncovered during the recompile process. Update outdated syntax and resolve missing member errors to restore functionality.
Configure Options: Set up database options to match the original settings. Disable autocorrect to prevent potential issues and ensure smooth operation.
Compact and Repair: Perform a compact and repair operation to optimize database performance and resolve any lingering issues.

Conclusion: By following this comprehensive repair process, you can revitalize your old and broken Access databases, making them snappy and functional once again. If you encounter persistent issues or need assistance, don't hesitate to reach out for professional support.

Don't neglect database maintenance—it's essential for ensuring optimal performance and preventing costly repairs down the line. Just like neglecting oil changes in your car, proactive maintenance pays off in the long run.

Simplify Role-Based Security with Access Controls Management

If you're delving into user logins and role-based security in Access, you'll want precise control over who accesses what and which functions they can perform. While Ribbons provide top-level control, you may need finer granularity, and there are various techniques available. Consider exploring Peter's Software and their LASsie Product for a comprehensive bolt-in solution—it's both refined and potent.

Role-Based Security: Control Access with Ease

Once you've established security measures and global variables to track logged-in users, managing form controls becomes straightforward. Here's a quick method to lock form controls dynamically:

First, mark controls in the form with specific text in the Tag property, indicating the desired action. In the example below, we use "lock," but you can customize this to fit your needs.

Public Sub LockControls(frmName As String)

On Error GoTo HandleErr

Dim cntrl As Control

For Each cntrl In Forms(frmName).Controls

If cntrl.Tag = "Lock" Then cntrl.Enabled = False

End If

Exit Sub

HandleErr:

Select Case Err.Number

Case Else Debug.Print "Error " & Err.Number & ": " & Err.Description & "BasControlManipulation.LockControls"

End Select

End Sub

Usage Example:

Call the LockControls function in the main form's OnOpen event:

If TempVars!UserLevel < 10 Then Call LockControls(Me.Name) End If

If the main form contains subforms, call the second function similarly from the subform's OnOpen event:

If TempVars!UserLevel < 10 Then Call LockControlsSub(Me.Parent.Name, Me.Name) End If

Public Sub LockControlsSub(frmName As String, subfrmName As String)

On Error GoTo HandleErr

Dim cntrl As Control

For Each cntrl In Forms(frmName)(subfrmName).Form.Controls

If cntrl.Tag = "Lock" Then cntrl.Enabled = False

End If

Exit Sub

HandleErr:

Select Case Err.Number

Case Else Debug.Print "Error " & Err.Number & ": " & Err.Description & "BasControlManipulation.LockControlsSub"

End Select

End Sub

With these simple yet powerful functions, managing Access controls becomes a breeze, ensuring secure and tailored user experiences.

Simplify SQL Conversion with ConvertSQL Tool

Last Updated: May 15

Even if you struggle with SQL like I did with multiplication tables in grade school, fear not—there's a handy solution! As a former mechanical engineer turned Access developer, I found myself wrestling with SQL queries without formal training. Then, I stumbled upon Allen Browne's genius tool, which unfortunately is no longer maintained. But fret not, I've got a workaround!

Introducing ConvertSQL: Your SQL Savior

To effortlessly translate complex SQL queries into readable code, follow these steps:

Download and Install:
- Grab the zip file and extract the Access Database ConvertSQL.accdb.
Import the Form:
- In your application, import the form aaaConvertSQL.
Copy and Convert:
- Create your action query and switch to SQL view.
- Copy the SQL statement.
- Open the aaaConvertSQL form and paste the SQL into the upper text box.
- Hit the "Build SQL" button to convert it into code-readable format.
- Copy the transformed statement from the lower text box.
Paste and Profit:
- Simply paste the converted SQL into your code.
- Don't forget to Dimension a string 'strSQL' in your code module.

This nifty tool has been my time-saving companion, even if I'm well-versed in SQL. Plus, it's invaluable for deciphering complex queries. And a quick tip: when using code-readable SQL, opt for constants instead of controls for efficiency.

Action Queries vs. Code-Readable SQL: In most scenarios, I prefer action queries over code-readable SQL. But beware of the default setting in action queries—always check and set the USE Transaction property to No unless required. To avoid this hassle with numerous queries, I've crafted code to automate the process. Download my sample from here.

Get in Touch: Need a hand with SQL conversions or transaction properties? Drop me a note, and I'll gladly assist!

With ConvertSQL by your side, SQL woes will become a thing of the past. Download now and streamline your SQL workflow effortlessly!

Enhance Your Software's Visual Appeal: Global Form Color Schemes with VBA

When updating software versions, adjusting the default color scheme of forms can freshen up the user experience. Instead of relying on Access's limited built-in options, I prefer the flexibility of VBA. Here's a straightforward method to achieve uniformity across forms.

Each form comprises sections, each with a BackColor property. The Detail section is standard, and optional sections like form footer and header can be present. Continuous forms even offer an AlternateBackColor property for shading alternate rows differently. Here's how I streamline the process:

First, create a public function in a module, let's call it BkGD:

Public Function BkGD(ColNum As Byte, Optional frmName As String)

On Error GoTo HandleErr

Bkgrd1 = Nz(TempVars!FormTopColor, 15132390)

Bkgrd = Nz(TempVars!FormMiddleColor, 16777215)

bkgrd2 = Nz(TempVars!FormBottomColor, 15132390)

bkgrd3 = Nz(TempVars!AltRowColor, 15395562)

ExitHere:

Exit Function

HandleErr:

Select Case Err.Number

Case Else Debug.Print "Error " & Err.Number & ": " & Err.Description & "mdl_HandyFunctions"

End Select

End Function

Declare public variables at the module's top:

Public Bkgrd As Long 'form header section

Public Bkgrd1 As Long 'detail section

Public bkgrd2 As Long 'form footer section

Public bkgrd3 As Long 'alternate row color

Invoke this function on startup, fetching color numbers from a table to set users' default colors using tempvars. Then, in each form's OnOpen Event, apply the colors:

Me.FormHeader.BackColor = Bkgrd1

Me.Detail.BackColor = Bkgrd

Me.FormFooter.BackColor = bkgrd2

Me.Detail.AlternateBackColor = bkgrd3

Remember to remove header/footer lines for forms without them. Older databases might have different section names like Detail0, adjust accordingly.

Tempvars offer convenience and reliability. Refer to Juan Soto's insightful post for further details on leveraging tempvars. With this method, maintain consistency and enhance visual appeal effortlessly across your software forms!