MS Access – Deploying Your Database Front-End to Your Users

We all know that the proper way to setup any multiple-user MS Access database is to:

Split the database (Back-End – Tables; Front-End – Queries, Forms, Reports, Macros, VBA, …)
Place the Back-End on the a server to which all the users have access to
Deploy a copy of the Front-End to each user so they have their own local copy to run the database from.

With this in mind, a very common question in a variety of forums then becomes, how do we deploy a copy of the Front-End to our users and ensure that they always get any updates?

There are a number of possible approaches. such as:

bat/vbs script to blindly copy the master Front-End file to the local computer and then launches the database
vbs script which checks the current version installed and perform an update if/when required and then launches the database
html script which performs any updates and then launches the database
Integrating a version check within your database, or creating an intermediary database to perform updates and launch your database
use specialized software to launch the database which takes care of this

Below are a few good resources that illustrates each of these:

MS Access – VBA – Kill a Process

Every once in a while, programs do not shutdown properly and thus cause headaches. So it can become necessary to terminate the process. Below are a couple different approaches you can employ to terminate, forcibly close, a process/program.

Continue reading →

Cisco AnyConnect – VPN establishment capability from remote desktop is disabled

If you are trying to to use Cisco AnyConnect through a Windows Virtual PC and receiving the following message

VPN establishment capability from remote desktop is disabled. A VPN connection will not be established.

This is what I did to get it functional.

Go to your virtual machines and edit the Settings.

Under Networking, ensure that you are using Share Networking (NAT)
Under Integration Features, ensure that Enable at startup is NOT checked

Now, if you launch your virtual machine Cisco AnyConnect should connect (at least it did for me).

That said, having to disable the Integration Features in this manner, to me, is a serious PITA! You can’t even copy/pastes or utilize your main PC’s hard drive to access files… I don’t know the underlying issues, but this to me is a MAJOR problem.

2015 Microsoft MVP Virtual Conference

Do you want to learn some new tricks from some of the very best technological experts in the world, and best of all, for FREE! Then join us at the first ever Microsoft Americas MVP Virtual Conference!

Fellow MVPs will be sharing their knowledge on a wide variety of MS products and services in this two day bonanza.

So, what exactly is the Microsoft MVP Virtual Conference?

A world-class free online conference that features technical content presented by Americas’ region MVPs that is open to the public

More technical content (Level 200, 300, 400), less marketing
5 tracks: IT Pro English, Dev English, Consumer English, Mixed Spanish, Mixed Portuguese
Event will be broadcast via Lync using L+ which enhances the conferencing capabilities of Lync
Two full days of sessions with simultaneous webcasts running across all 5 tracks
Thursday May 14^th and Friday, May 15^th
Start at 8am PT and running until 6pm PT (Pacific)
Day 1: 45 sessions + Keynote, Day 2: 50 sessions
Keynote on Day 1 to be delivered by Steve ‘Guggs’ Guggenheimer, Corporate VP of DX
On Demand content available via Channel9
This event is not just for MVPs, it’s for everyone!

Come join in the fun! To learn more, and/or register, use the following link:

Microsoft Americas MVP Virtual Conference

FMS Inc – Total Access Detective – Review

One benefit of being an Microsoft Access MVP is the fact that I get to interact with fellow MVPs and have access to certain promotions.

In the past couple of years, I have had the pleasure of communicating with Luke Chung, president of FMS Inc, and benefiting from his offer of NFR licenses of FMS’ software. The more I get the opportunity to try his software, the more I must say I am impressed.

I recently had the need to compare multiple back-end files to identify any differentiations between them.
At first I started thinking that the tasks wouldn’t necessarily be too complex to perform, even manually if need be. But further thought may me realize my initial conclusion was simply erroneous. It is obviously easy to identify missing tables, but identifying missing fields, fields that have changed, properties that have changed, and so much more, all sorts of minor tweaks that occur over the lifetime of a database quickly made any form of manual analysis unrealistic.

Then I remembered once noticing on Luke’s website a tool, Total Access Detective. I made a request to have a license and within a few minutes I had my copy to try out.

Continue reading →

MS Access – VBA – SaveAsText Error 3709

Well the ambiguous error reporting from Access hit me again! It really makes one wonder who at MS comes up with which error to throw when problems occur and how they retain their job.

I have been recently trying to address a corrupted databases and finally decided that using the undocumented SaveAsText and LoadFromText functions was my last hope before having to start the database over again. So I went ahead a punched in my command:

Application.SaveAsText acForm, "MyFormName", "C:\...\MyFormName.txt"

Upon executing the command I received the following:

Run-time error ‘3709’:
The search key was not found in any record.

Run-time error 3709 - The search key was not found in any record.

What the?! Can’t find what search key in what record? Is this the underlying issues with the form and why is has corrupted in the first place?

No. The issue has nothing to do with an index or any record. The most irrelevant error I have ever seen coming from Access (and I have seen some truly useless error messages being output by MS Access in the past). The problem after a quick test was simply due to the fact that the VBA project was protected and I had not unlocked the project with the appropriate password. So for anyone else trying to perform a SaveAsText and getting this ridiculous error message, ask yourself did you unlock the VBA project. If not, do so and try again.

Now that we solved this problem, hopefully someone at Microsoft could start providing error messages pertinent to the actual problem at hand, but I’m not holding my breath on that one!

MS Access – VBA – Close All Open Forms

In some cases it may be useful to close all the forms within a database.

Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenForms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open Forms in the database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Usage:
' ~~~~~~
' ? CloseAllOpenForms
'   Returns -> True     => Closed all Forms successfully
'              False    => A problem occurred
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-02-17              Initial Release
' 2         2023-02-09              Update variable naming, Error handler, copyright
' 3         2023-03-13              Changed approach as For each frm in Forms skips
'                                       objects
'---------------------------------------------------------------------------------------
Function CloseAllOpenForms() As Boolean
    On Error GoTo Error_Handler
    Dim i As Long

    For i = Application.Forms.Count - 1 To 0 Step -1
        DoCmd.Close acForm, Forms(i).Name, acSaveNo
    Next i
    
    CloseAllOpenForms = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: CloseAllOpenForms" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Then you can simply call it by doing

Call CloseAllOpenForms

If CloseAllOpenForms Then
    'All Forms were successfully closed
    'Your code goes here
End if

Blocking SPAM from Known Domains in Outlook

As per my previous posting on the subject, for some reason, I have been getting hit recently by SPAM.

HARP Refinance <HARPRefinance@businessthere.eu>
Sitting Solution <SittingSolution@pappend.xyz>
SittingSolution <SittingSolution@lendorate.xyz>
BizCashPros <bizcashpros@fuud-eaat.eu>
Diet Notice <DietNotice@laseral.xyz>
Diet Notice <DietNotice@dashend.xyz>
Diet Notice <DietNotice@pemender.xyz>
Diet Notice <DietNotice@huffinter.xyz>
SittingSolution.com <SittingSolution.com@jakery.xyz>
SittingSolution.com <SittingSolution.com@menastad.xyz>
Curves <Curves@lewaster.xyz>
Curves <Curves@mengert.xyz>
6MinutestoSkinny <6MinutestoSkinny@laserbym.xyz>
6MinutestoSkinny <6MinutestoSkinny@lefferal.xyz>
and so on…

Now, originally, I would simply click on the Junk e-mail option to Block Sender. The issue with this is that these SPAMmers never use the same e-mail account twice, so this is completely ineffective.

So by examining the sources of the problem, we can very quickly find a nice pattern, all the SPAM is comming from .xyz and .eu domains. Knowing this we can use Outlook’s Blocked Senders to build a simple pattern to block them all! Here’s how it is done.

On the Home tab on the Ribbon, click on the Junk drop-down

Click on the Junk E-mail Options…

Goto the Blocked Senders Tab, followed by the Add… button

Then simply add the domain suffix for the domain to block in the provided textbox.

Finally simply click OK twice to return back to Outlook.

It’s that easy. Since having taken this step, I no longer have to deal with any of the garbage sent by these perpetual SPAMmers as their e-mails go directly into my Junk E-mail folder. Life is good once again!

Important Note:

In this example I am blocking outright any e-mail from a .xyz or .eu domain. That said, you need to carefully examine which domains are SPAMming you and act accordingly by using their suffix.
Blocked e-mails are not deleted, but rather moved directly to the Junk E-mail folder. As such, you should review the content of that folder from time to time to ensure no legitimate e-mails end up in there.
At any time you wish, you can go back into the Blocked Senders Tab of the Junk E-mail Options… dialog and highlight an entry and simply delete it by clicking on the Remove button. So nothing is permanent and you can undo this process at any time.

Stellar Phoenix Access Database Repair Review

I was approached by Stellar Phoenix to review their Access Database Repair software.

After performing a few tests, here are my thoughts on the matter:

Password Encrypted Files

The first hurdle that I faced was the fact that I encrypt most of my databases with a password. Sadly, Access Database Repair cannot work with such files! To me this is a major limitation.

Recovering Deleted Records

One of the nice feature was the fact that Access Repair offers the ability to recover deleted records. That said, the way in which it works had me very confused. It offers no way to reinserted the deleted records back into the originating table, but rather one must save the recovered database in which a new table will be created with the deleted records. It is not the way I would have built the software, but it does work and then you can use queries to recover the records of interest.

As per other freely available recovery techniques, it is impossible to recover deleted records once a database is compacted.

File Corruption

File corruption comes in all sorts of shapes and forms. One form of corruption that I recently was faced with was the fact that an unknown user had erroneously open an MS Access database in MS Word (why, I have no clue). By doing so, the MS Access database became corrupted and no longer functioned. I then checked with fellow MVPs and was surprised that this occurs more often than I thought. So I decided to try to recover a recently corrupted database using Stellar Phoenix, but it was unable to recover the file stating:

I also tried to repair a database which had a corrupted form. What the exact nature of the form corruption was is unknown to me, but I do know that it became corrupted. Sadly, Access Database Repair was unable to fix the problem.

I then sent an e-mail to my point of contact at Stellar Phoenix to ask for clarifications and below was the list of corruptions that they claim Access Database Repair Addresses:

Symptom 1: Cannot open a form or report
Symptom 2: Number of records varies, depending how the data is sorted (index corruption)
Symptom 3: Some table rows show ‘Deleted’
Symptom 4: Memo field contains strange characters.
Symptom 5: “An error occurred while loading Form_FormName”
Symptom 6: “Error Accessing File. Network Connect May Have Been Lost”
Symptom 7: “AOIndex is not an index in this table”
Symptom 8: Key field is no longer primary key, and relationships are gone

Conclusions

I’m not sure what to think. I will need to do further testing to truly know how effective a tool Access Database Repair truly is.

My other thought on the matter is that in many cases, the solutions to many (if not all) of the symptoms is freely available and various resources exist to help. For instance:

For me, corruption, the possibility of corruption, simply reinforce the critical importance of Backups for all files (not just MS Access databases)! By implementing a proper backup strategy, the repair of corrupted files becomes unnecessary.

Update 2017-12-01 – Deleted Record Recovery

I decided to give the software a go as I always feel bad giving a product a bad review. Sadly, things did not go any better. I had a file in which the user had erroneously deleted records, so I thought this would be a prime test. The db itself was fine and still fully usable, but some records were deleted and no compaction had been performed yet. I selected the file, it ran it’s analysis and I received the following (look at the console message at the bottom)

This file is not a valid MS Access file.

So I don’t know what else to say. It doesn’t seem to work even with a healthy accdb?

more to come…

Smart Inderter Not Working in Office 2010, 2013

If you do any serious VBA development, you should have heard, and installed, Office Automation Ltd’s Smart Indenter. If you haven’t you should! It is an exceptional FREE tool that helps properly indent your code to help with code legibility & standardization. It offers numerous customizable options to suit your specific needs.

That said, I recently tried installing on a machine with Office 2013 and it would not work!? What to do.

After so going back and forth with Mz-Tools (another must have VBA developer tool), I finally got it operational.

Now, I do not know exactly why or how, but the only conclusion I could come up with is the order of installation somehow fixes whatever the underlying problem is/was. You must install Mz-Tools first and then Smart Indenter. Smart Indenter on its’ own does not work, nor does installing Mz-Tools subsequently. After trying different variations, the one that appear to have “fixed” the problem was to uninstall the Smart Indenter & Mz-Tools, then reinstall Mz-Tools followed by Smart Indenter.

So the conclusion drawn from my experience, it would appear, is that Mz-Tools must be installed first!

If you have any insight into the nature of the problem (perhaps even an alternate solution) or if you can confirm my fix, please post a comment.

Update

Please refer to: Smart Indenter on Win 10 with Access 2013/2016 for another potential solution to getting Smart Indenter operational.

BAT/VBS/HTML Approach

Database Approach

Specialized Software (Paid)