Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

To clarify - I'm not sure it comes across this way, but I'm not arguing, I'm prioritizing.

I'm not saying "PrimaryKey" is working fine as a field name and I'm going to use it in all my tables and in every new database I work on.

I'm saying the database has possible corruption and no PK defined on many of the tables. I'm going to fix that and some pseudo security issues like blocking the navigation pane and the ribbon.

After that, I will look at things like spaces in table and field names and fields with somewhat reserved words in the names.

And yes, I also get your comment on the longer I wait, the worse it will be. That's somewhat why I can't fix it immediately. If I only named the field PrimaryKey and never used it, I would just have to get exclusive access to the backend and change the field name. I don't reference the field often, but I know it is used in 2 or 3 queries per table, in 3 or 4 VBA subroutines, etc. (And - to your point - if I wait I may create a new VBA subroutine and need to reference that field, which creates another place I need to remember to change when I fix it.)

 

[Pat Hartman]

The Indexes listing is always table-bound, so identical names are not a problem.

Are you sure about that? I seem to remember having had some issue with either indexes or relationships when I imported tables from two different databases to the same SQL database because it caused a duplication of index or relationship names.

 

[Pat Hartman]

I'm not saying "PrimaryKey" is working fine as a field name and I'm going to use it in all my tables and in every new database I work on.

"PrimaryKey" will NOT cause a problem since it is not a reserved word. It is simply a poor choice because it is meaningless in the same way that ID is meaningless.

Putting off name changes is more poor practice. If you know they need to be changed, change them sooner rather than later. OR, leave it for your successor to clean up the mess.

If you build your queries efficiently by using Select fld1, fld2, fld3, etc rather than Select *; then you will include the PK name in every query so it becomes a real pita.

 

[ebs17]

Are you sure about that?

Quite. In the specifications, the maximum number of indexes refers to one table at a time. The automatic naming of the indexes when they are created by the table design includes the names of the fields used.
If you name fields in different tables the same (foreign key, timestamp and some other things), nothing breaks down. The qualified use is carried out with the table name (or table alias) anyway.

Relationships would be a different topic as they connect two tables and thus have a scope in the whole backend and not just in one table.

 

[Marshall Brooks]

I have less questions than I expected, but I'll get to those next:

The backend was rebuilt from text files and all tables (except one) now have a primary key defined. Thanks to all - especially @Pat Hartman.

Everything went smoothly. I expected to come into a bunch of error messages today, but everything seems to be working fine.

Next step is securing the interface. Plans:

I'm relying fairly heavily on @isladogs' CAI Form: https://www.isladogs.co.uk/control-application-interface/index.html

• I want to hide the navigation pane. I don't want to use a typical "SwitchBoard" Pane, but I want something similar to Colin's frmNavigation Pane. Not only does this hide my tables and queries, but it also fixes an annoyance with the default Nav Pane - i.e. with multiple forms enabled: Open FrmA. Open FrmB. Click back to FrmA - the Navigation Pane will still have FrmB highlighted.
• I want to hide the toolbars.
• I want to disable the Shift-Key Startup bypass.

More questions and issues in separate replies below:

 

[Marshall Brooks]

This is something I should have foreseen, but it caught me by surprise.

2/3'rd of my tables are copied locally from the backend to the frontend. The existing table contents are cleared and then the data is copied from the backend tables.

All of the tables that had a PrimaryKey field now have that defined as the Primary Key.

One table did not have a Primary Key so I added a field for it and then I got an error when it tried to import the table into the local front end table.

I'm thinking this is one of those situations where I need to add the field to the front end, add the field to the back end, and then release both the front end and back end co-ordinated at the same time so that the two versions are in synch.

The table that doesn't have a PK is the table with the FE version number and the location of the master copy of the front end. It only has one record.

For now, I took the PK field back out, but I don't know if there is any point in trying to add it.

 

[Marshall Brooks]

With the new navigation pane, our database will work better with tabbed windows and no tab labels. I found this thread https://www.access-programmers.co.uk/forums/threads/default-overlapping-forms.307143/ but it isn't entirely clear what controls this (embedded in the file or registry key).

Basic question: If I set up my front end as tabbed windows, will it retain that setting when users download it? (I don't usually have a problem with sending an E-mail saying "The database works better with tabbed Windows and set it from File Options - Current Database", but if I am hiding the toolbar …)

 

[Marshall Brooks]

Basic question of Nav Pane replacement:

Colin says to disable AFR on the subforms and during development. Automatic Form Resizing?

How do I set the form up so that the form is on the left and the main form that I want to display and work on is to the right of the main form?

 

[Marshall Brooks]

I expected to have a question on the quick Access Toolbar - some of it could be useful, but I don't want a duplicate button (even grayed out), and it seems silly to have a VBE button with an .accde database. True, they only see "The project is not viewable", but they see all the module names and that tends to make you think something is broken.

However, I found DoCmd.ShowToolbar "Ribbon", acToolbarNo does a lot more than I expected, it not only hides the menu, it also hides the QAT and changes the title bar, which is what I wanted to do.

But I would prefer if the title bar stayed red, instead of gray - i.e. instead of:

I'd prefer to see:

Is there a way to do this?

Alternately, would there be a way to get rid of the title bar, but have a bar on my form with the database title and minimize, restore, and maximize buttons for the application?

 

[isladogs]

You've mentioned me a couple of times in recent posts though I'm not sure what points need answers.
If I miss anything let me know:
1. If you hide the ribbon completely, AFAIAA the title bar appearance cannot be changed.
2. Yes you can get rid of the title bar completely. See the 'Fill Entire Screen' option in my article linked in post #125. Or use borderless forms with the Access interface hidden.
3. AFR = automatic form resizing. The built in navigation form doesn't play nicely with AFR for reasons explained in one of my articles.
I create my own nav forms instead so I can control their behaviour and use AFR.
See the emulated navigation form in my example AFR app supplied with this series of articles

Automatic Form Resizing 1

Use automatic form resizing (AFR) to ensure that forms and their controls are automatically resized and repositioned for any screen size & resolution. It can also be used to add a zoom feature to Access forms

www.isladogs.co.uk

 

[Marshall Brooks]

@isladogs - Thanks answer anything and everything!!!

I'm trying to fix a lot of things at one shot, so I'm getting in deep water fast ...

I wasn't aware of automatic Form Resizing - it looks good - I'll try it.

Q1 - Does tabbed windows stay with the database FE. For example, if I set my database up to use Tabbed Windows and another user had changed it to use Overlapping Windows, when they download the new version, does it use Tabbed Windows for them, like I set it, or does it use Overlapping windows for them since they had this selected previously for this file name?

Questions on the Nav Pane replacement:

Since I am not using AFR, I have my forms mainly designed to work at 1920x1080. They would probably work at lower resolution with scroll bars and they would probably work at higher resolution and just be smaller visibly. 1920x1080 seems to be fairly standard for us.

Q2 - What really is different in CAI between frmNavigation and frmNavigationEmulated? I know they have different footers and headers, but ...

Q3 - It isn't really necessary, but I like the minimize Navigation Pane option. I'm assuming I could add a button on the FRAToggle and it would change the width of FRAToggle and the left Position of FSubNavigation - Do I also need to set the width of FSubNavigation since I want it to use the entire screen area?

Q4 - I currently have the database open with FrmFormA. With the new code, I would have it open with frmNavigationEmulated. Would I then call FrmFormA from the load event of frmNavigationEmulated, or just another line in the startup code (I'm assuming from frmNavigationEmulated since it becomes a subform.

Q5 - Follow-up to Reply #129 - Would it be possible to do everything from frmNavigation Emulated - i.e. hide the title bar, have a heading bar as part of the form with the file name and path and with minimize, restore, and maximize buttons?

Thanks in advance!

 

[Marshall Brooks]

New question. I downloaded the shift Key bypass code from here: https://www.access-programmers.co.uk/forums/threads/disable-shift-key.51479/page-2#post-1442733

It seems to be working as expected once I open the database twice - as mentioned in several other locations - for example: https://www.isladogs.co.uk/improve-security-2/index.html#SB

Is there a way to avoid the double-open?

It seems like (if anyone knew about this), they could open the front end whenever I updated it and hold down the shift key and change some of the settings (although without access to the VBA code, I'm not sure how much they could change.)

 

[Marshall Brooks]

@isladogs - Follow-up to Q5. I see how in CAI, you show and hide the App Interface (and the title bar). In Reply #130, you mentioned borderless forums.

In CAI, your main form always shows and the button hides or shows the Access Interface.

I copied modDatabaseWindow module from CAI to my database and set my main form borders to "None", and added functions with
SetAccessWindow (SW_HIDE)
and
SetAccessWindow (SW_SHOW)
And it works, but it is hiding the entire Access app, including my form, not just the title bar. How do I hide only the title bar?

 

[Marshall Brooks]

I'm not getting very far with my Nav Pane Replacement form ...

I tried running AFR with my existing form. I got an error on the ReSizeForm Me line ("Sub or Function Not Defined"), even though I copied the modResizeForm module over and I see the function in that code.

I copied the frmNavigationEmulated form to my database and I get an error ("Sub or Function Not Defined") on the On Open Event, even though it is only one line and I commented out the Form Open and Form Load code.

 

[Marshall Brooks]

Made some progress going the other way - i.e. I made a copy of CAI, linked it to my backend, copied some of my forms over to it and changed the buttons on frmNavigationEmulated to call my forms and that works, although it doesn't use the entire window even if I maximize the form. Gives me an idea that what I want is possible, though ...

 

[isladogs]

You appear to be trying to do several fairly advanced things at the same time. Rarely a good idea.
You have also asked so many questions that I don't have time to respond properly as I'm busy with my own projects.

My advice would be to focus on one task at a time.
Study the article(s) for that feature, carefully review the code in the supplied example apps and make sure you understand what each part does.
Only then should you import code into your own app.
When you have one feature working, then go onto the next item.

 

[Marshall Brooks]

I have a new issue unrelated to the development work that I am doing now. When I rebuilt the backend, I removed three tables that were not used or needed any more (and were not accessed by the front end, but existed in the backend. My corruption search code looks like this:

Function DB_Corruption_Check()
    ' https://access-excel.tips/access-vba-loop-through-all-tables/
    ' https://www.access-programmers.co.uk/forums/threads/using-vba-to-search-all-tables-in-a-database.234890/#post-1198193
    Dim Found As Boolean
    Dim tdf As DAO.TableDef
    Dim sTable As String
    Found = False
    For Each tdf In CurrentDb.TableDefs
        If Not (tdf.Name Like "MSys*") Then
            sTable = tdf.Name
            Call SearchTable(sTable, "###", Found)
        End If
    Next
    If Found = False Then
        Box "### Not Found"
    End If
End Function

When I run it now, I get "Run-time error '3078':" the Microsoft Access database engine cannot find the input table or query '<tablename that I deleted>'. Make sure it exists and that it's name is spelled correctly.

How do I tell the backend that this table doesn't exist or how do I remove it from .TableDefs?

 

[Marshall Brooks]

For some reason, I can't see @Gasman 's reply (I got it in my e-mail), but deleting the links from the front end worked perfectly. I forgot the tables were linked in the front end b/c we never access them.

 

[Marshall Brooks]

You appear to be trying to do several fairly advanced things at the same time. Rarely a good idea.

Agree and Disagree. I have a vision for how I want this to work and appear. It is easier and more productive to find a way that gets me close to a solution than to spend several hours on one approach and then find out that that approach won't work.

You have also asked so many questions that I don't have time to respond properly as I'm busy with my own projects.

Fair enough. You aren't the only member of the forum. (Granted, most of what I am working on now are areas that you have the most expertise in). If I don't ask here, I'm simply stumbling on my own and tripping over my own feet and trying to figure out things that someone else can solve with one reply. I'll try to prioritize future questions.
***
I made significant progress this morning. To recap:

• Yesterday, I fixed an error in my User Lockout code that now allows me to work on the database (based on the database being opened from a specific location, even if I have other users locked out.
• I know how to use tabbed window - not sure if that setting stays with the database, but from what I can tell, it does.
• I figured out how to disable the Shift Key bypass - with it disabled unless the database is opened from a specific location.
• I know how to hide the Access Navigation Panel and block F11 to restore it. (Not implemented yet, but I know how to do it.)
• I figured out how to hide the ribbon and toolbars. Not crazy about how it makes the title bar look, but I can't live with it, and I'm sure there is a solution.
• I abandoned the idea of using frmNavigationEmulated and bring that into my database and modifying it and the idea of adding my forms and into CAI and working that way. I ended up just adding my own navigation form based on this website: https://www.webucator.com/article/how-to-create-a-navigation-form-in-microsoft-acces/ It is pretty much working. There is a lot to do, but I think I know how to modify it to work the way I want. Essentially, at this point it is more functional than the Access Navigation Pane, and it allows me to hide the Access Navigation Pane. Still to do:
  • I can't see how to have tabbed windows with labels on the subforms, but I want to get away from this.
  • Everything is cropped, and there is a lot of white space at the top. I think this will be an easy adjustment.
  • Right now the navigation buttons are blue and the selected form is light blue. I want to change this to the active form is green, other forms that are loaded (and behind the active form) are light blue and unloaded forms are gray. I think I can do this, but I found an issue.
  • I want to add additional buttons on the form for some of my macros and some admin functions - the buttons will be hidden for most users. I think this can be done fairly easily.
  • There is a header on the form which I am thinking I can convert to a "psuedo Title Bar".

***
Issues to be resolved (by Priority):

• I don't quite understand how the form is working apart from how I used to operate with the built-in Navigation pane. From what I can tell, if I click on a button on the new frmNavPane that I created, the form load event runs, but the form Activate code DOES NOT RUN. Three examples:
  • Previously, the database opened what I will called frmFormA on startup. Now the database opens frmNavPane on startup and frmFormA is shown as the subform of frmFormA. I'm not sure how it knows frmFormA should be opened first, other than it was the first form in the list.
  • I added "MsgBox "I'm here"" in the FormLoad even of frmFormA and when I select FormA from the button on frmNavPane, I see the MsgBox.
  • I added "MsgBox "I'm here." To the FormActivate event of frmFormA and when I select FormA from the button on the frmNavPane, I DO NOT see the MsgBox. My code looks like this:
    

    Private Sub Form_Activate()MsgBox "I'm Here."
    If MultiForm = False Then
    Dim intx As Integer
    Dim intCount As Integer
    intCount = Forms.Count - 1
    Screen.MousePointer = 11
    For intx = intCount To 0 Step -1
    If Forms(intx).Name <> "FormA" And Forms(intx).Name <> "frmLogoutTimer" Then
    DoCmd.Close acForm, Forms(intx).Name
    End If
    Next
    Screen.MousePointer = 1
    End If
    End Sub

    If a public (global) variable named MultiForm is false, then it closes all the other forms except one hidden background form. If this code were running, it should close frmNavPane as that is not the form in question. I'm not sure how to change the code to: frmNavPane.SubformPanel.Activate - close all other subforms of FrmNavPane. There doesn't appear to be ANY VBA code for the new frmNavPane.
• When I displayed the forms with the standard NavPane, I could right-click on the form and select Close. (I still can). When it is loaded as a subform. I don't see any way to close it - other than opening a new form. I might be able to add a close button on the button that loads the form or add a right-click menu here - otherwise, this negates any advantage to showing multiple forms.
• As mentioned in Reply #133 - I don't know how to hide the Access Title bar and leave frmNavPane open. I know it can be done b/c CAI does it, but I can't make it work. - But I can live without it.
• I don't know how to get AFR working - but I found a spot where the code wouldn't compile, so I can probably figure that out. It would have been useful for us many years ago. We had one users running at 1650x900. Now most everyone is 1920x1080 or better and in Win10, you can use scaling to make everything readable.

All help appreciated!!!

 

[Marshall Brooks]

New issue - I will HAVE to get AFR working with the new form. Since my forms are now subforms, the footer doesn't show at the bottom of the window. Either i have white space at the bottom of the form, or I have to scroll down to see the status bar and what record number I am viewing.

I might be abandoning the Navigation Form idea. I can't seem to make the form more narrow than 2.4583 - not sure why. I can't add command buttons to the Navigation Control (I can in Design View, but they aren't visible in form view. I can't make the Navigation Control shorter than the subform so that I can add a form with buttons below it.

Time to restart!!!