Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Tools, Utilities and more…


Recently, I watched Free Tools for Microsoft Access recorded by Crystal Long and Adrian Bell (Microsoft Access MVP’s) on Channel 9.  Upon looking at comments below it dawned on me there is no central location for all those great free tools!  How is anyone to find them?  After all, you may not know they even exist let alone how helpful they can be to you in your development.  So… I have added a new page just for them.  (Okay, not all of them are free but most are.)

If you know of a free tool for Microsoft Access send me an eMail.

7 total views, 1 views today

Spreadsheeto Excel Made Easy

A new link has been added to the Microsoft Excel Tips links on the left and what a site…
Spreadsheeto

Second best websites dedicated to Microsoft Excel!  (Big *THANK YOU* to Mikkel Sciegienny for dropping me a line).  It has some really cool stuff like creating charts, easy drop downs, conditional formatting (and they make it so easy) and more!

And all FREE, it’s a great site for beginners right on up to advanced Users.  You get sample code, clear definitions and, in some cases, downloadable sample files.  Truly, a great site!

 

19 total views, 1 views today

Software Review – Stellar Phoenix Access Database Repair

Review done by Priyanka Chouhan

Stellar Phoenix Access Database Repair software claims to repair damaged or corrupt Access database files in a non-destructive and safe way. The software can repair both MDB and ACCDB files as well as individual database components including tables, linked tables, deleted records, modules, reports and macros.

Some of the features of Stellar Phoenix Access Database Repair are:

  • Can repair MDB and ACCDB files
  • Repairs records, tables, linked tables, queries, modules, forms and reports
  • Supports MS Access 2013 / 2010 / 2007 / 2003 / 2002 / 2000
  • Compatible with Windows 8 / 7 / Vista / 2003 / XP
  • Easy to use interface
  • Powerful algorithm to scan and repair corrupt files

With so many claims, the software definitely sounds alluring. That is why, I decided to personally test the software and see if it really stands on its claims and I might be spoiling the climax but I have to say that the Stellar Phoenix Access Database Repair is absolutely amazing and it is a must for every DBA that works regularly on Access Database. File corruption is extremely common in database no matter how many precautions you take and with a software like this handy, it wouldn’t really matter much anymore.

 Minimum System Requirements

Processor: Pentium Class

Supporting Operating systems: Windows 8 / 7 / Vista / XP

RAM: 1 GB

Free Space: at least 50 MB of free space

MS Access Version: 2013 / 2010 / 2007 / 2003 / 2002 / 2000

 

And here are the details of the test environment where I ran this software

Processor – Intel Core i7 2.60GHz

RAM– 8GB

Operating System – Windows 10

Free Space – 588 GB

Outlook version used – 2013

Installation Steps

Here are the steps to install the software, I found them pretty easy to follow

  • Download the setup file StellarPhoenixAccessDatabaseRepair.exe and click on it to start the setup process
  • A licence agreement dialogue box will open up. Once you are finished reading it, select ‘I accept the agreement’ option and click on Next button to continue

  • In the new ‘Select Additional Tasks’ dialogue box, check the required boxes and click on the Next button

  • Now, in the ‘Select Destination Location’, browse and provide the path of the folder where you want to install the setup files and click on Next

  • The Start Menu dialogue box will open and you should browse and provide the path where the program shortcuts will be saved

  • Finally in the ‘Ready to Install’ dialogue box, review all the settings. In case you want to modify some, you can go back and do that. After you have reviewed the settings, click on ‘Install’. This will start off the installation process

  • After it is completed, you will be notified that the software has finished installation

There is a demo version of the software and a paid version. This is a great way to download the software and test it, before you decide to buy it. There is no time limit to use the demo version but you can use it to only preview the repaired files. If you want to save those files, you would need a paid version for that.

According to the license agreement, you can either use it as an individual or with other users but not simultaneously.

Steps to use Stellar Phoenix Access Database Repair

  • Open the Stellar Phoenix Access Database Repair software and go over to the menu bar on the top. Go over to the ‘File’ menu and click on ‘Select Database’

  • A new dialogue box will open in front. There is an option to select the drive where the software would start scanning for corrupted MDB and ACCDB files. There is also an option to select file type and to search inside sub folders.
  • This makes it very convenient to locate corrupted files and after selecting them. After the drive and file type has been selected, click OK.
  • Select the files that need repair and click on the ‘Repair’ option to start the repair process

  • After the scanning is over, the software will list all the repaired tables, records, indexes, and relations from the files that were selected. The best part is that the software displays them in a tree structure which makes it extremely easy to track down specific data that you might be looking for
  • The ‘Find Item Tree’ option comes in handy when you want to preview details of a specific item. All you have to do is, type in the item name in the search box and click on Find

  • By default, all the repaired items are checked and you can save them by going to the File menu and clicking on Save. You can either save it at the default location of the file or choose your own
  • Click OK and the file will be saved. In case you only want to save specific items, you can uncheck the rest and save them.
  • There is also an option to save logs of the repair process which can be used to analyze the repair process

The software is definitely very effective; it was able to recover each and every corrupted Access database file that I had in my system. On top of that, I really like how fast the software is. It never lagged on my system and the entire scan process was extremely quick even with a large amount of files. Selective Recovery is my favorite feature. There are times when you only want to recover a specific file or just one deleted record but recovering the entire database file for that proves to be extremely time consuming.

All in all, the price tag on Stellar Phoenix Access Database Repair software seems completely justified given how efficient the software is. It can definitely save a lot of my time that could have been very well wasted while trying all the others not-so-effective ways to recover my data. Definitely a must for all Access database users.

40 total views, no views today

eMailing specific Reports to specific Recipients…

Question: Can anyone tell me how to eMail a report with content based data based on the individual?

Answer:
Create a Module, name it anything you like except the name of the Function.

At the top of your Module under…

Option Compare Database
Option Explicit

…place

Public olApp As Object
Public olNameSpace As Object
Public objRecipients As Object
Public objNewMail As Object 'Outlook.MailItem

Then, we need something to initialize Outlook, so, copy/paste the below into the same Module…

Function InitializeOutlook() As Boolean
' This function is used to initialize the global Application and
' NameSpace variables.
    
    On Error GoTo Init_Err
    Set olApp = CreateObject("Outlook.Application", "LocalHost")  ' Application object
    Set olNameSpace = olApp.GetNamespace("MAPI")  ' Namespace object
    Set objNewMail = olApp.CreateItem(0)
    InitializeOutlook = True
Init_Bye:
    
    Exit Function
Init_Err:
    InitializeOutlook = False
    Resume Init_Bye
    
End Function

And now we can copy/paste the Function into the same Module…
Note: After you have copied it you will need to change the Paths, Query, Report and Field Names to match your own.

Function streMailOverdue() As String
On Error GoTo Error_Proc
‘From: http://regina-whipp.com/blog/ 
    
    DoCmd.Hourglass True
    
    'Set global Application and NameSpace object variables, if necessary.
    If olApp Is Nothing Then
        If InitializeOutlook = False Then
            MsgBox "Unable to initialize Microsoft Outlook!"
        End If
    End If
    
    'Create new MailItem object.
    Set objNewMail = olApp.CreateItem(0)
    
    Dim strTo As String
    Dim strSQL As String
    Dim rs As Recordset
    Dim strSubject As String
    Dim strBody As String
    Dim strAttachment As String
    
    'Get the eMails for those who are to receive a Report
    strSQL = "SELECT apAssociateID, apeMailAddress " & _
                "FROM qryeMailAddresses"

    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    'Save Report outside Access
    With rs
        .MoveFirst
            Do While Not .EOF
                DoCmd.OutputTo acOutputReport, "rpteMailReport", acFormatPDF, "\\SERVERNAME\data\eMailReports\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
                strAttachment = "\\SERVERNAME\data\eMailReports\" & !apAssociateID & "-ToDoListFor_" & Format(Date, "mm.dd.yyyy") & ".pdf"
            'Send eMail and Report
            Set objNewMail = olApp.CreateItem(0)
            With objNewMail
                .To = rs.Fields("apeMailAddress")
                .Subject = "To Do List Items Overdue!"
                .Body = "See attachment..."
                    If strAttachment <> "" Then
                        .Attachments.Add strAttachment
                    End If
                .Send
            End With
        .MoveNext
        Loop
            'Delete the Reports
            If Dir("\\SERVERNAME\data\eMailReports\*.pdf") <> "" Then
                Kill "\\SERVERNAME\data\eMailReports\*.pdf"
            End If
        End With
        
        rs.Close
        Set rs = Nothing
    
Exit_Proc:
    DoCmd.Hourglass False
    Exit Function
Error_Proc:
    Select Case Err.Number
        Case 287:
          'On Error Resume Next
          Resume Exit_Proc 'ignore the error'
        Case Else:
          MsgBox "Error encountered streMailOverdue: " & Err.Description
          Resume Exit_Proc 'display a message then exit'
    End Select

End Function

Now, on your Form in the Event Procedure of your Command Button place…

Call eMailOverdue

All done!

68 total views, 1 views today

Windows 10 my 2 cents worth…

I have been *playing* with Windows 10 for about a month and half now.  Since then I have removed the ability to upgrade any other machine to Windows 10.  Says a lot right?  However, my reasons for not wanting to upgrade the other machines is not because there was any problem with the in-place upgrade, hardware conflicts or any other issues others are seeing.  So why?

1.

I have older programs that run on Windows 7 64 bit but will not run on Windows 10 64 bit.  Not sure why but having tried to load a few of them and trying to get them to run was good enough for me.  This is a HUGE issue as I use those programs frequently enough that I would need two machines running and then be transferring files back and forth, no sense in that.

Third party drivers that have not been upgraded will cause major, crippling issues.  Am I supposed to replace all my equipment, really?

To boot, there is no Windows 10 Upgrade Tool to tell which programs/hardware are incompatible, nope, it just uninstalls it or breaks your machine, really?

2.

Having a machine update on its own is disturbing.  We all have come to know *Patch Tuesday* as a *Let me see what’s broken now Tuesday*.  Spending the day trying to figure out which update did what causes me serious downtime.  And then to add to my stress… waiting another week (or more) for a fix is beyond crazy.  Should one just hang out a shingle… *Business closed due to Windows updates, will reopen when fixed!*, really?

3.

Installation of Office 2016 Click-to-Run, in some cases, removes Office 2013.  Umm, as a developer that supports ALL versions of Office, this is a huge inconvenience.  I either need a virtual machine or I have to switch to the .MSI installation or I’m back to two machines running.  Even two machines is an issue, what if I go to a Client that has both?  What then?

 

4.

Widgets – I have family and clients all over the world.  Calling them can be challenging with all the different Time Zones, so… on my Windows 7 Desktop sits clocks for each location of my family, no can do in Windows 10 with buying a bunch of different clocks.  Really?

On the same note, I kept the weather widget, just for fun so I can also see the weather where they are, so again… really?

5.

Cortana – I do not want some artificial *intelligence* peeking at my Contacts or my eMails.  I do not want it remembering where I searched.  On that same note, this should not be an hour exercise to turn off.  Really?

6.

Mail tile that shows me an envelope with a number, really?  You can get a live feed from Twitter (when it’s working) but you can’t get a live feed for my incoming mail.  Really?

7.

Flip 3D gone.  Loved that and made it so much easier when, if you’re like me, you 10+ windows open.  It was in my top 5 reasons for moving to Windows 7.  Now, I’m back to using my keyboard, really?

8.

Flat Icons – Feel like we went back to the 1980’s or is the 1990’s, really?

9.

Renaming of Programs on Program Apps menu and I still can’t figure out how to move them around, I don’t want them in ALPHA order and I don’t want Tiles.  There is a system my madness and now doing what I do requires extra clicks.  On that same note, what happened to right mouse click > Send To Desktop?  I want my Shortcuts on my Desktop.  I have about 30 of them there now and I use them.  To do that in Windows 7 one has to navigate to the actual file, really?

10.

Edge – Why?  Just tell me why because I hate it, quickly installed Internet Explorer.

While I understand the issues that go with supporting multiple versions of anything, whether it be Windows or Office and that Microsoft may not want to do it but there are those of us that still do it.  It’s our business.  So for Office, a choice should be an option as it was in previous versions of Office.  For Windows 10, at a minimum, let us choose whether we want without having to jump thru hoops to say no and let us choose whether we want to install updates/patches automatically or not.  Again, all which was available in previous versions of Office and Windows.

Just in case you’re interested, my opinion of Windows 10…
Slick UI  (Yep, that’s it!)

If you have not upgraded yet and still want to, a few things I would suggest…
Confirm your hardware is compatible – That little icon that says your machine is ready is not quite accurate.  Do your own research by either visiting the manufacturer’s website or going to the Microsoft Forums and searching.

Visit…
Windows 10 Specifications
Known issues with Office and Windows 10

Do the same for your software, especially if it’s older software.  The fact that it runs on Windows 7 64 bit or 32 bit is not a prerequisite for it running on Windows 10.  This has been especially problematic for Anti-Virus software.

If you’re on a metered connection be careful, see…
http://lifehacker.com/enable-metered-connection-to-delay-windows-10-updates-1723316525

However, this won’t help for the initial download.  You will need to figure out a plan yourself to get it initially installed.

Connection speed – I have a relatively fast connection, 100 mbps, so my download and install was done in less than an hour.  Bare that in mind when getting ready to download and install.

After it installs the password I did have set was no longer valid.  Instead I say my eMail and that is the password it wanted, ugh.  I did change it that which does not affect your password to your eMail but some warning would have been nice as that is not the eMail I would have chosen.  (I guess I don’t understand why it couldn’t leave my previously supplied password.)

If you don’t…
Third party tool (most effective)
GWX Control Panel app

OR…

Manual
This is what I did to stop the nagging and the attempts to download…

Uninstall these updates (found in the Microsoft Windows section):

KB2952664
KB2976978 (usually found on Windows 8.x machines)
KB2990214
KB3021917
KB3035583
KB3044374 (usually found on Windows 8.x machines)
KB3068708

Do NOT reboot when it asks you.  After you are done go to Windows Update and search for updates, and you will see them listed again. Right mouse click on each one individually and select Hide update.  Then go to Windows Update under Control Panel and select Change Settings and uncheck Give me recommended updates the same way I receive important updates.  I have also changed my Important Updates to Check for updates but let me choose whether to download and install them.

Side note: While those updates come in as Optional and Important, once installed they are shown as Recommended.

64 total views, no views today