MS Access – List Back-End Data Sources

Ever taken over a database that was a complete mess and quickly wanted to determine what Back-End data sources were involved? Below is a simple function to enumerate the Back-End data sources in a given database.

Public Sub ListBESources()
    On Error Resume Next
    Dim colTables             As New Collection
    Dim db                    As DAO.Database
    Dim tdf                   As DAO.TableDef
    Dim sBackEnd              As String
    Dim BE                    As Variant

    Set db = CurrentDb

    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
        'Ensure the table is a linked table.
        If Left$(tdf.Connect, 10) = ";DATABASE=" Then
            'Get the path/filename of the linked back-end
            sBackEnd = Mid(tdf.Connect, 11)
            'Ensure we have a valid string to add to our collection
            If Len(sBackEnd & "") > 0 Then
                colTables.Add Item:=sBackEnd, Key:=sBackEnd
            End If
        End If
    Next tdf

    On Error GoTo 0
    Debug.Print colTables.Count & " Data Source(s) found:"
    For Each BE In colTables
        Debug.Print BE
    Next BE
End Sub

Continue reading →

MS Access – Search with Spaces in Terms

MS Access Text Search with SpacesEvery developer at some point or another ends up needing to create a search functionality where their user(s) can enter what they know (terms, text, …) and pull up any matching records.  The problem being that you will quickly come to smack your head against your computer because once you setup a search textbox control and perform the most basic test, you will notice that Access truncates any spaces you enter making your search functionality completely useless.

Having faced this problem many years ago, and having recently been asked by fellow MVPs how to resolve this problem, I decided to create the following demo database.  In it, I demonstrate 2 similar approaches to work around Access’ native behavior and enable proper full text search.

Disclaimer/Notes:

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Download

Feel free to download a copy by using the link provided below:

Download “Access - Search with Spaces in Terms Demo” TextSearchDemo.zip – Downloaded 18236 times – 89.26 KB

Windows 8 – Windows Defender Will not Turn On Error 577

I was recently trying to fix my father’s computer (Windows 8.1) by removing Avast antivirus and reactivating Windows Defender which is natively part of Windows 8.1.

So I thought this would be truly very easy; uninstall Avast and turn on Windows Defender, or so I thought.

After uninstalling Avast (which now has too many pop-ups) I went into the Action Center to reactivate Windows Defender.

Windows_Defender_Action_Center_Warning_MessagesBut no matter what I did (that is, no matter how many times I clicked on the “Turn on now” button, nothing happened- no error, no message, nothing)

So I then went to Avasts website and downloaded and ran their specialized uninstaller, but this didn’t help either.

Then I had the idea to check out the Windows Defender service and make sure it was running.  In fact, it wasn’t.  However, once I tried to start the service, I received an

Windows could not start the Windows Defender Service service on Local Computer.

Error 577 : Windows cannot verify the digital signature for this file.  A recent hardware or software change might have installed a file that is signed incorrectly or damaged, or that might be malicious software from an unknown source.

Windows_Defender_Error_577Then I did some online research and came across a post indicating that one of the possible issues was that Avast (or another program) had changed certain registry settings and set them to 0 (false):

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Defender\DisableAntiSpyware
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Defender\DisableAntiVirus

But even running regedit as the administrator, I could not change these settings and would receive a message:

Access is Denied

 

After taking a break, I finally decided to start uninstalling any other programs that could interfere with Windows Defender; other malware, spyware programs that I routinely install.  So I decided to start by uninstalling Spybot Search and Destroy.  BINGO!  After a reboot, I was able to edit the registry settings,  turn Windows Defender on through the Action center.

So nearly 3 hours after having started, what I thought was going to be a 5-10 minute process, I finally had things working properly.

So for anyone else smacking their heads against their computers trying to enable Windows Defender, take a look at any potential conflicting programs and if you have Spybot installed either disable it or uninstall it.  As an FYI – Spyware Blaster, Malwarebytes Anti-Malware, custom host file did not cause any problems and I left them installed (didn’t need to uninstall them to get Windows Defender up and running again).

Generate a Random String, Random Password in MS Access

Building on an old post MS Access VBA – Generate a Random String, I wanted to post a fully functional sample in which I added full support for multiple string generation.  I use this tool to generate a set of random passwords and then pick one to create a new account password…

The beauty of this tool over many others, is that you run it locally, not on someone else’s server (god knows if your actions are being logged with malicious intent) like all the online generators do (yes, call me paranoid – in today’s world one cannot be too safe).  This way, you remain in control.

The other advantage is that the passwords are saved to a Table so you can continue to work with them easily. One word of caution however is that the table gets wiped clear every time you run the generator so be sure to output any result you wish to keep long term to another table or source (Excel, Word, …).

Random String Generator

Continue reading →

DoCmd.OutputTo in xlsx (Excel 2007+) Format

A common technique for exporting data to Excel is to utilize the  DoCmd.OutputTo Method.

Now, previously (pre Office 2007), the basic format to export a query to Excel format was simply:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLS, , True

BUT, once you port such a database to a post Office 2007 computer, you start to get it exported as a “Microsoft Excel 5.0/95 Workbook (*.xls)” format and such a file will thus open Excel in compatibility mode possibly given the user compatibility errors/messages when they go to save the file after working with it.

So unless you have to ensure legacy compatibility, it is strongly advisable to update your code to export the data in “Excel Workbook (*.xlsx)” format, the new Excel file format thus eliminating any compatibility issues. Hence, we simply need to change the OutputFormat format variable to a more appropriate format (acFormatXLS -> acFormatXLSX). So your code would thus become:

DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, , True

Alternately, you could also use:

DoCmd.OutputTo acOutputQuery, "YourQueryName", "Excel Workbook (*.xlsx)", , True

Looking to export to a different format?

Nothing could be easier! In the VBA Object browser, go to the Access Contants library and look over the various acFormat**** available to you. Such as:

  • acFormatHTML
  • acFormatPDF
  • acFormatRTF
  • acFormatSNP
  • acFormatTXT
  • acFormatXLS
  • acFormatXLSB
  • acFormatXLSX
  • acFormatPS

Taking Things a Little Further

It wouldn’t be very difficult to create a custom DoCmd.OutputTo function which could determine the version of MS Excel installed and the utilize the more appropriate OutputFormat variable. Then you code would be 100% compatible regardless of what your users have installed!

Are There Any Alternatives

Another approach available to us is simply automate Excel. This is the approach I have adopted. In my early development days, I used the built-in DoCmd.OutputTo command, but have long since abandoned it for my own custom export function: Export2XLS. Why? The custom function approach is fully compatible with whichever version of Excel a user has installed, and gives the programmer far more control over what the final export looks like. You can format Cells/Ranges, apply colors, fonts, …

How To Display The Current Database Path/Name In The Access Application Title Bar

A question has surfaced a few times in Access forums regarding how one could display the Path/Name, or both, in the Access Application Title Bar. I previously contributed my two cents worth to the following thread:

http://answers.microsoft.com/en-us/office/forum/office_2007-access/how-to-show-the-path-of-the-access-file-on-the/15065cb1-1e71-e011-8dfc-68b599b31bf5

that said, I decided to put together a simple concrete example to dissect and learn from.  So feel free to download the following Access 2007 accdb sample (although the code will work in prior and future versions as well).

Display The Current Database Path In The Access Application Title Bar

Basically, all the code is contained within a single standard module and executed by the AutoExec macro upon launching your database.

Count of the objects in your MS Access Database

Have you ever needed to get a simple count of the number of :

  • Tables
  • Queries
  • Forms
  • Macros
  • Reports
  • Modules

I recently needed to get some summary data regarding a database I was working on.  The following single line VBA commands will get you the count(s) you are after.

'Number of Tables
CurrentDb.TableDefs.Count 'includes system tables
'Number of Queries
CurrentDb.QueryDefs.Count
'Number of Forms
Currentproject.AllForms.Count
'Number of Macros
Currentproject.AllMacros.Count
'Number of Reports
Currentproject.AllReports.Count
'Number of Modules
Currentproject.AllModules.Count 'does not include object modules

Easy as can be!

How to Report SPAM in Canada – Canada’s anti-spam legislation (CASL)

The Good News for Canadian Residents

To follow suit on my recent post entitled: SPAM from Ricky Alexis, Rick Barry, Erica Semente and Lex Jones, the Canadian Government has since passed the Canada’s anti-spam legislation (CASL) which went into effect July 1st, 2014 giving us all one more recourse against these SPAMmers.

First off, here are a few resources on the subject:

Canada’s Law on Spam and Other Electronic Threats – Home – Canada’s Anti-Spam Legislation
Spam Reporting Centre
Submit Information to the Spam Reporting Centre

As I discovered through my own recent woes with SPAM and trying to report every single SPAM e-mail to the same Hosts and Registrars was that they were:

  • slow to respond, if they did at all
  • simply reactive and definitely not proactive (even after reporting the same person 40-50 time they do not freeze the account, simply freeze the offending domains, so the SPAMmer just keeps on SPAMming using other domains)

Now, if you check out the 3 links provided above, especially the 3rd one, you will see that Canadians now have 2 means to report SPAM e-mails: either by using the online form or by simply forwarding the SPAM e-mail to spam@fightspam.gc.ca

I find it much easier to simply forward the e-mail.  When forwarding the SPAM e-mail, include:

  • the e-mail header (in outlook, open the e-mail and click on Options and then you should see an Internet headers textbox.  Simply copy/paste the content into your e-mail you are sending to spam@fightspam.gc.ca)
  • the e-mail body (content of the message) itself
  • I also start off with a paragraph explaining why I consider the e-mail to be SPAM (you can also consult their online form for some of the reasons they commonly use)

Below is SPAM received Date/Time from Domain/E-mail Address of Sender. I have no affiliation with the sender, never inquired, never requested information, never subscribed or registered, nor have I ever given them any form of consent to receive any form of communications from them.

If you have any questions, please do not hesitate to contact me for more information.

  • Be sure to include your name should they ever which to contact you (they will already have your e-mail address from the e-mail you are sending.

Remember, be polite (they are not the ones sending the SPAM, they are trying to help you stop the SPAMmers), be concise and give them all the information you can to simplify their job.

 

The Bad News

You will not be contacted unless one of the CASL enforcement agencies (the CRTC, the Competition Bureau and the Office of the Privacy Commissioner of Canada) requires additional information about the nature or details of your submission.

So you will have no way of knowing if they received your e-mail, since no confirmation is ever sent out and you will have no way of knowing what, if anything is being done about your complaint!  I must say that in 2014, this is moronic!  They could easily have implemented an automated confirmation e-mail to let you know your e-mail was received and they could easily have a summary webpage listing domains and complaint statuses.

The CASL enforcement agencies will not investigate all submissions, but the information you provide is an essential component of the intelligence gathered by the Spam Reporting Centre and may enable these agencies to target their enforcement activities.

Another element that I do not understand.  What is the criteria for investigating or not?  Why would I take the time to send information if you won’t actually do anything about it?  How will I know if I am wasting my time if you don’t notify me that my submissions are not valid?

 

Conclusions About the Canada’s anti-spam legislation (CASL)

I know the legislation has scared a lot of small businesses regarding needing consent to send out e-mails… but has it truly done anything to scare true SPAMmers?!  I seriously doubt it.

Also, most of the SPAM I receive seems to originate outside of Canada, what authority does Canada have on foreign businesses!?

Furthermore, since there is no transparency on behalf of CASL, no follow-up, no communications, … I am a little concerned.  On top of the fact that they are being selective on which complains they treat.

That all said, I am however trying it out for the time being in the hopes of seeing some positive results.  It can’t hurt and it just take 30 seconds to forward the e-mail with the header.

Hopefully, if everyone starts forwarding all the SPAM we receive, the government will see just how bad things are and maybe, just maybe they can do something about it.  Then again, the Internet is a Wild Wild West and since when is Canada the Sheriff?

Time will tell.

 

Further Reading(s)

You may wish to read the CBC’s article on the subject: Canada’s new anti-spam law: Can it really clean up your inbox?

How to Send Multiple Attachments within a Single E-mail Using VBA

This question keeps coming up piratically on a weekly basis:

How can I include multiple attachments within a single e-mail?

or

How can I send out an e-mail containing multiple attachments?

or

How can I include external documents as attachments in my e-mail?

The simplest, and most used, e-mail technique used in MS Access is to use the built-in SendObject method and for basic e-mails, or e-mails containing a single MS Access object, it works beautifully.  That said, it is majorly lacking when it comes to wanting to include multiple items as attachments, or wanting to use external documents as attachments.

Now, why in all these years Microsoft hasn’t added such functionalities is an entirely other discussion and well outside the realm of this post.

All that you need to know is there are a couple viable alternatives to enable us to do exactly what we need.  What is even nicer is that most, if not all, of the alternatives can be ported to any VBA program.  So they are even better IMHO since you can create an e-mail module and then import it into Word, Excel, Access, … and it will work as is.

So let us look over a couple options:

Continue reading →