Solved Automated search for Database Corruption Assistance

[The_Doc_Man]

You mentioned validation code and The_Doc_Man mentioned constraints. I'm not sure most of the data supports this - lots of it is text fields, but when is it implemented. In other words - there are for example 40-70 fields per record per table. Do I have code on the Form BeforeUpdate event to validate all 40 fields, or do I have code on each field to validate it and if it passed that I don't need to check again, or do I check both places.

I want to address this specific statement in a narrow way. You mentioned having people who don't use forms. You CANNOT assure correct data going through datasheet view without either constraints or data macros, and from what I understand, data macros are limited. The best way any of us has ever found to assure proper content and conformance to procedure is to totally block table view, datasheet view, and query view (when the query is not read-only). In Form view you can implement a Before Update event to verify that every field is legit. As long as you do not block people who are bypassing Form-based control of their actions, ... forgive the language but you are screwed. With a form you can look at each field AND you can assure that proper procedure is being followed. In datasheet view - particularly since your tables seem to not balk at it - your users can paste data randomly (based on the symptoms you read out to us earlier.) NO control. NO protection.

As to where those "###" sequences occur, if the field in question is limited to the same number of ### as you see in it, then you probably got that from trying to paste a record from a table that had wider fields. I'm going to take a wild-eyed guess that it could happen when someone tried to do a COPY (CTRL/C) of a record followed by a PASTE (CTRL/V) of that record into another table, but that person wasn't certain of which table was the target and pasted records that were too long for the fields in the actual target table.

 

[Marshall Brooks]

@jdraw ...

Without giving too much away. I work for a defense contractor. Their really is nobody in the company to train me.

For example - see - https://www.access-programmers.co.uk/forums/threads/determine-if-m365-via-vba-how.328145/ The company deployed M365. My nightly Excel extracts that run unattended stopped working b/c Excel was waiting for someone who wasn't there (me) to add a security label to a file that was being created in the background so the prompt/Window to assign it wasn't visible half the time. I opened a priority support ticket with IT and asked on this forum. IT assigned it to some unrelated group that maintained another database that was going to be retired. They had no clue and kicked it back to be re-assigned. Before IT got back with me, I had the code working and closed the ticket, but if I hadn't, I'm pretty sure they would have said "Well, the macro works if you don't run it unattended and enter the data manually." We're going to close the ticket and mark this resolved since it isn't a work stoppage."

https://www.access-programmers.co.uk/forums/threads/microsoft-access-security-notice.326977/ - Coworker reported the issue to IT. They said since it eventually worked after you clicked OK 7 times, it wasn't a work stoppage and they were going to close the ticket.

See - https://www.access-programmers.co.uk/forums/threads/the-vba-project-cannot-be-read.326838/ - I contacted an administrator of another database in another division of the corporation. He knows relational databases and is good, but he isn't that up on VBA and couldn't help me. He recommended I look at the corporation's SLACK channel for Access programming, so I did. There were about 6 members on the SLACK channel and the most recent post was from about 6 months ago with someone asking to send him a PM and he would tell them about some about some great freeware for keeping front end databases up to date (that I suspect was not company-approved for installation) and didn't seem as good as the BTAB code I was using. I replied back with a link to Bob Larson's page. As far as I know, nobody saw it or replied.

All the help that I am likely to get company-wise will likely to come from right here.

OTOH - we did recently hire someone in my department that has an SQL background and doesn't like Access and doesn't really know VBA, whereas I am decent with Access and pretty fluent in VBA and know little to nothing about SQL, so if we can pool our knowledge ...

 

[Marshall Brooks]

In Form view you can implement a Before Update event to verify that every field is legit.

I'm naive here, but I know if I have Field BeforeUpdate events, they fire in both Form and DS view. You are saying that Form BeforeUpdate events do NOT fire in DS view?

If so, I'll get that blocked.

I'm going to take a wild-eyed guess that it could happen when someone tried to do a COPY (CTRL/C) of a record followed by a PASTE (CTRL/V) of that record into another table, but that person wasn't certain of which table was the target and pasted records that were too long for the fields in the actual target table.

D@mn - Excuse MY language, but you might well be onto something. Seems to me like that could explain how a record from a completely unrelated table ended up in another table without disappearing from the main table - which is what I fairly often see with this.

Also explains - I check ALL the tables, but I've NEVER seen bad or missing data except in the five primary tables - the most used ones that the users have access to DS view of.

Would CTRL-C and CTRL-V do the same thing in Form View, and if so, how do I prevent it?

Thank you - I think we might be closer to a solution.

 

[Marshall Brooks]

Is there any way to make DS view read-only, and/or at least lock out CTRL-C and CTRL-V?

 

[Pat Hartman]

Excel Rows that are in the current report and were not in the previous ones and I don't remember adding, or records that clearly from the data don't belong in that table.

I guess Excel has nothing to do with anything.

Their really is nobody in the company to train me.

I'm not sure I know anyone who hasn't essentially learned Access for themself including me. My difference is that I had 25 years of development experience with other platforms so I understood database design and development discipline going in. Access was a mystery. But learning Access is just part of the problem. When you are dealing with production data, YOU are responsible for keeping it safe since the users are normally clueless. You don't get to be clueless since the hammer will come down on you if the incorrect data causes some mistake by some other business entity who is relying on what this application supplies. You might be at a crossroads in your career path. Are you interested in software development or do you want to stick with your actual job? Something's got to give. Either you are prepared to dig in and fix the problem or someone else needs to step in. You've been told by pretty much everyone in this overly long thread that "fix up" after it breaks is the wrong approach.

Is there any way to make DS view read-only, and/or at least lock out CTRL-C and CTRL-V?

When you change the form view from single to DS, you inherit all the properties of the single view form. If the single view version is updateable, then the DS version is updatable. I thought I made that clear earlier. Any code you have in the form's BeforeUpdate event for the single view will run exactly the same way when the form is in DS view. The issue will arise when you select multiple rows. What happens if you select two rows and cntl-copy and then select four rows and cntl-paste? I don't know. I even posted a database that I strongly recommended that you download and understand after viewing the videos. You MUST understand what events run, in what order when you cut and paste a group of records. I am not going to do it for you. I gave you the tool you need to do it. We can't see the database. Even the column names are a secret so you are on your own. But this action is very likely what is causing much of your damage.

The way to protect against this is to stop the form from opening in DS view.

 

[The_Doc_Man]

Would CTRL-C and CTRL-V do the same thing in Form View, and if so, how do I prevent it?

That would depend on the form, but offhand I would say no, they would have very different effects. This presumes that the form is not set up as a pseudo-datasheet view, but rather that its fields are scattered across the screen in something aesthetically pleasing and/or functionally grouped in some way. You prevent cut/paste "accidents" by making it impossible to enter a view where that strategy would work. IF you no longer allow people to do "anything they please" then they will hate you for a while, but when the DB stabilizes with fewer "unrecognizable format" events, you will love yourself and your users may eventually see the increased stability as a sign that you knew what you were talking about.

 

[The_Doc_Man]

As a separate note, I worked as a defense contractor for 28 1/2 years before I retired in 2016. I understand the U.S. Navy environment all too well. Don't know which branch you are dealing with, but let's just say "been there, done that, wore out the T-shirt."

 

[Pat Hartman]

Would CTRL-C and CTRL-V do the same thing in Form View, and if so, how do I prevent it?

Yes, they would do the same thing since what those key combinations do is dependent on what is selected. You can't select multiple records in single form view though.

Just stop the form from switching to DS view. If they really want a form with that view, give them one that does not allow updates.

 

[Marshall Brooks]

Thank you all.

This sounds weak, but it isn't.

I sent an E-mail to the team to cease using DS view.

Next revision to the front-end, today or tomorrow, will disable DS View.

Still need to rebuild the back end tables.

In the future I plan to disable the navigation pane (not sure if I'll go with a switchboard or maybe subform on each main form - haven't reviewed the switchboard links yet.

I plan to disable the ribbon.

I plan to possibly add an "Admin" form with things like table view and delete record, but it will only be visible to me and one other person.

I'll probably have more questions when I get further into this, but this is the current status.

Are you interested in software development or do you want to stick with your actual job?

Actually, I'm really happy where I am - i.e. I've kept my actual job, but transitioned it more to software development. I'm trying to walk the fine line between being told I need to go back to my previous job and being told to expand the database duties to other parts of the organization.

 

[Marshall Brooks]

I'm back sooner than I thought ...

Mapped Drive question:

I previously added the following code (partial code from the main forms Load Event):

Location1 = "C:\Users\" & Environ("Username") & "\Desktop"
Location2 = "U:"
Location3 = "C:\Users\<myUserName>\Documents\Access Database Development"
If CurrentProject.Path = Location1 Or CurrentProject.Path = Location2 Or CurrentProject.Path = Location3 Then
    'MsgBox "Okay Location"
Else
    strResult = Dialog.Box(Prompt:="Prohibited Location!" & vbCrLf & "" & vbCrLf & "Please copy the database to either your Desktop (Local) or U:\ (Citrix) and re-open." & vbCrLf & vbCrLf & _
    "Database will now close" & "", Buttons:=(0 + 16))
'    Call UnloadAllForms
    Dim i As Integer
    For i = VBA.UserForms.Count - 1 To 0 Step -1
        Unload VBA.UserForms(i)
    Next
    DoCmd.Quit acQuitSaveAll
End If

For our Citrix users, U:\ is SUPPOSED to be mapped to <Network Path>\Data\<UserName>

My code above works, but if someone decided to map another location to U:\, then the code will still allow the database to open. And if they navigate to the network path, then the database will not open.

I tried changing Location2 to Location2 = "Network Path\Data\" & Environ("Username")

And it opens if I navigate to the network path, but it does NOT open if I open it from U:\, which is mapped to the network.

How can I change it to open from either U:\ or "Network Path", but ONLY if U:\ is mapped to "Network Path"?

Thanks in advance!!!

 

[Pat Hartman]

My code above works, but if someone decided to map another location to U:\

That is why it is better to use the UNC notation rather than a mapped drive.

\\servername\path\filename

 

[Marshall Brooks]

Agreed and that was what I was trying to do and if I do that, it won't open from the mapped drive.

To explain - I changed the code to look for \\servername\path\filename.

If I navigate to that, it opens.
If I go to U:\ which is mapped to that location and open it, it says Prohibited location, even though that location is correct.

 

[Marshall Brooks]

Halfway there ... (Actually probably all the way there ...)

How to get a UNC path from Application.Path?

I want to get the path of the active workbook in vba code. ActiveWorkbook.Path does this BUT I need it to retrieve something like this: \\MachineName\ShareFolder\ETC\ETC2 NOT: S:\ETC\ETC2 Whe...

stackoverflow.com

So I can do something like:
Location 1: Desktop
Location 2: Network Path
If CoverttoFullPath (U:\) = Location2 Then
Location3 = "U:\"
Else
Location3 = ""
End If

 

[KitaYama]

How can I change it to open from either U:\ or "Network Path", but ONLY if U:\ is mapped to "Network Path"?

You can use FSO or WScript or WMI to check the full path of a network drive.
Scroll down to the bottom of this page:

Determine the Path of a Mapped Drive | DEVelopers HUT

Let's examine how we can both manually and with plain and simple VBA retrieve the full path of any locally mapped drives.

www.devhut.net

 

[Marshall Brooks]

Works, but doesn't return the fully qualified network path, but I can work around that ...

I used the StackOverflow path, but it works the same way (I think). In addition to changing it to a function, I also had to define A, c, and strFullPath as strings.

Thank you!!!

 

[KitaYama]

Works, but doesn't return the fully qualified network path, but I can work around that ...

I just checked FSO and it works perfect.
Did you add : to the letter?

You should use it like :
FSO_GetMappedDrivePath("U:")

 

[Pat Hartman]

If I go to U:\ which is mapped to that location and open it, it says Prohibited location, even though that location is correct.

Sounds like a security message to me. Trusted locations still apply.

 

[Marshall Brooks]

Works fine, but doesn't return the fully-qualified path.

Hard to explain, but ...

It returns "\\Some Network\Somefolder\Username"
It does NOT return "\\Some Network.domain.com\Somefolder\Username"

I usually use the second line in code b/c it is faster and slightly more accurate, but if they mapped the network name, the fully-qualified path isn't required - but since that is what I was originally checking for, the code failed.

I'm good with this part now.

It's not a security message. CurrrentProject.Path is whatever you opened the database from - i.e. U: or the network path. Apparently Access can't check if U: matches "Network\Folder" so you have to do that for it as @KitaYama said.

I have it working fine now, but it took more than I expected.

 

[Marshall Brooks]

Sent out the revised Front End. Datasheet view is officially disabled.

 

[Marshall Brooks]

Somewhat of a pain. Had to remap one of our users. If you do map to a fully qualified path, it doesn't work.
So in theory either:
U:\
\\Network\folder\username
\\Network.domain.com\folder\username
\\Network.subdomain.com\folder\username
Should work - all of those would locate the same file in the same network location. In reality, I need to update the code to allow for any possibilities (or remap the drives).