Solved Automated search for Database Corruption Assistance

[jdraw]

Further to Pat's comments /advice, several on the forum(s) have evolved from enthusiastic user to DIY programmer/analyst/maintainer/bottle-washer and cook. However, if this database is critical to the organization, then it is time to recognize the importance of the data and database. Since the company has highlighted/recognized its IP value, it would seem prudent for them to train you to better handle your "database activities" and their critical data. I'm sure the company wouldn't take a landscaper or a janitor and put that person in (part time/after hours) charge of Finance --that's just an analogy to stress the role of you/your database in the organization.

You haven't told us the industry the company is in, nor whether or not there are other databases and groups within the company. Perhaps you could tell us a little about the work in general terms (not divulging any secrets). To me it seems the concern with IP and dependence on msAccess that there may be more to your environment than this thread represents.

Your position at the moment reminds me of someone learning to sail. You learn by doing,but you have to get wet from time to time to understand how sails, sheets, weight transfer work together to propel the boat where you want to go.

There are some excellent youtube videos, some great books, many forums that can help. Google can be your friend. But I think the company should support your efforts with some training. Remember, training puts info into your head and the training and experience helps you as well as the company.

We are trying to be helpful.

 

[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?

 

[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."

 

[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!!!

 

[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 Where...

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:")

 

[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).

 

[Marshall Brooks]

@Pat Hartman:
Last week I added code to only allow the front end to open from the user's Desktop, the U:\ Drive, or one of my documents folders.

That worked fine, except as I said, the users could map any folder to U:\ and open the database from there. (Most of them would not know how to.)

I changed the code to only allow opening from "\\Network_Name\Folder\<UserName>" and the database would open from there, but not from the U:\drive.

I added a function to determine what network U:\ was mapped to and allow the front end to open from U: if it was mapped to the correct network name.
So now it will open from U: if and only if U: is mapped to the correct folder.

But some users had a fully-qualified path - i.e. "\\Network_Name.domain.com\Folder\<UserName>" so I either had to change the drive mapping for them or change the front end to account for any possible domain/subdomain mapping.

98 messages is way too many to read through to find out what you are actually talking about

At least this forum is linear, so I can't reply to post #23 and there is a new message on Page 2.

 

[Marshall Brooks]

Just want to add one quick item.

I said earlier that the PrimaryKey was duplicated in some cases and I thought this was a sure sign of corruption as I didn't think Access would allow a duplicate PrimaryKey.

I'm not sure it will, but what I had was a table with an autonumber field NAMED PrimaryKey with a value of "Indexed - Duplicates Allowed", but it was not SET as a PrimaryKey.

I think I'm going to check each table in the backend and rebuild the PrimaryKey field and set it as PK and no duplicates and Indexed.

 

[isladogs]

Sounds like someone didn't know what they were doing when they created the table.
PrimaryKey is a bad name for a field. Surprised its allowed

By definition, PK fields cannot have duplicates.
Although I've heard of this happening, I've never experienced it in over 25 years of using Access