Access – Authorized Users Control

As part of Securing your Database I mention:

Lockdown Which User Can Run Your Database

Another easy process to implement is to have a table in your database that lists all the windows usernames that are allowed running the database. At startup, retrieve the current user’s network username and then see if it is in your user table. If it isn’t close the application.

Note: I actually send an e-mail to the db admin when this occurs with certain information (PC name, Username, IP address, Date/Time, …).Daniel Pineault

I have had a couple inquiries about this and thought today I’d explain the process.

There are basically two, very similar solutions. Both approaches involve using a hidden form (you could also do this using an AutoExec macro) that you set as your database’s ‘Display Form’ or automatically open as part of your startup routine.

Continue reading →

Excel – Bug – Microsoft Excel Cannot Open or Save Any More Documents

Software Bug

Well, I started experiencing the following error recently trying to open Excel workbooks directly in the Outlook message I received it in.

Excel - Bug - Microsoft Excel Cannot Open Or Save Any More Documents

Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space.

  • To make more memory available, close workbooks or programs you no longer need.
  • To free disk space, delete files you no longer need from the disk you are saving to.

Now, I’m running on a machine with 8+GB of free RAM, CPU running under 5%, plenty of free HD and the Excel workbook is 975kb in size, so what gives Microsoft!?

Now, I don’t know the root cause, nor do I have the time to troubleshoot yet another Microsoft blunder, but thought I’d share the easy workaround for anyone else having facing a similar issue: save the file to your PC.

Once I dragged the file to my desktop (or other folder) I was able to launch it without issue.

GUI Design Basics

I’m starting this post, which will grow in time, because of feedback through a recent blog poll.

What are some basic design principles that should be remembered when designing an application, any application?

Most design principles are universal and not Access specific.  They can apply just as much for Access Forms & Reports, as Excel Userforms, PowerPoint presentations or … Web Application. Here are a few of my basic recommendations.

I’ve split this discussion up into the following sections:

Continue reading →

Bye Bye UserVoice!

Frog Leaving

I reported it for several years now that UserVoice was a complete waste of time!!!

Let me be more specific, the general idea was a great one, but rather it was the implementation, administration and follow through with the core principle of such a site that made the it a complete waste.

You can check out my findings by looking over:

Microsoft’s UserVoice Announcement

Continue reading →

Access – Retrieve Primary Key Value Upon Insertion

This is a question that frequently comes up in the forums:

How can I get the Primary Key value of an insertion performed through VBA?

The issue here depends on the insertion method adopted.
 

INSERT INTO / DLookUp

Typically, a lot of times the simplest insertion approach is to perform a INSERT INTO query

Currentdb.Execute "INSERT INTO Companies ( Company ) VALUES('Some Company Name');", dbFailOnError

Continue reading →

Access – Connecting to an Azure SQL Database

More and more we want/need to be able to use an Access database and not be restricted to the confines of a local office LAN.  One potential solution can be to migrate the back-end (the tables) to Microsoft Azure and relink the front-end to the Azure SQL database, thus creating what is known as an Access hybrid database.

The beauty here is the back-end database is hosted in the cloud (in this case in the Microsoft cloud) and accessible to anyone that can run Access and whom has an Internet connection.  The drawback here is performance, although query tuning can greatly improve this aspect as well as using Azure SQL database views, and pass-through queries in Access.

The Overall Process

There are 2 key steps to this process:

  1. Creating an initial File DSN
  2. Linking the tables in Access

Continue reading →

Azure SQL – Automate Database Maintenance

Recently, I have been knee deep in doing a MySQL migration to Azure SQL.  I quickly learnt that Azure SQL required a lot more attention than its MySQL counterpart.  So an Index and Statistic maintenance plan was needed, and urgently to keep things humming along smoothly.

Much to my amazement, Azure SQL did not offer the typical SQL Server Agent!  Everything in Azure is extra!!!

So I started doing a little digging and found a couple options.  One potential solution which I was exploring was to create an Automation Account to run a Maintenance Stored Procedure on a daily/weekly basis.  I only mention this as it may be an avenue worth exploring for some.

In my case, because the client I was helping already was using flow and was already using the premium SQL Server connector, I decided to go the Power Automate Flow route to automate the maintenance.  Thus providing a solution at no extra cost to them.

The Overall Idea

My process is very straightforward:

  • Create an Azure SQL Stored Procedure that performs the necessary index and statistic optimization
  • Use Power Automates Flow to run it on the desired schedule

Continue reading →

Access – Encryption and Password Protection

I thought I’d briefly cover the subject of Access database encryption and password protection.

Historically, when we talk about the mdb file format, password protection was pretty much useless and could easily be cracked with free and widely available online tools. With the newer accdb file format things changed and cracking them seems harder. One word of caution here is that I’ve just recently been made aware that a company is developing, or has developed, an Access password cracker that they say can do so nearly instantaneously.

That said, it is my personal opinion that every database should be password protected.  I take things a step further and, not only password protect them, but also place them on an encrypted drive.

Password Protecting Your Database Through the GUI

Continue reading →

Does Microsoft Access Have a Future?

I put the question to you, with what you’ve experienced in the past 3-5 years (since Office365), do you feel that Access will survive?  Or has Microsoft put the nail in its coffin?

It is clear, that the shift to continued development and releases by Microsoft has drastically increased the number and severity of bugs that we are all having to deal with and this is true for both Windows and Office.  A few examples just of recent Access bugs, quickly pulled from this site (note I stopped logging bugs as it was becoming too time consuming, so this list is far from complete), include:

So not only has the quantity of issues increased in recent years IMHO, but what is your impression of Microsoft’s response in resolving such issues?

Are they meeting your expectations and resolving them adequately and in a timely manner?

Is this positively or negatively impacting your desire to continue to use Access?

At the end of the day, are you getting the support you expect from your software vendor?

I think their response to problems is more important than simply the fact that they are releasing bugs.  That said, the quantity and severity of bugs is becoming alarming to many (including myself).

Continue reading →

VBA – Get Office Build Number

Since, the introduction of Office 365 (Now called Microsoft 365), I believe, Office 2016, we now have to use the Office Build Number for troubleshooting the onslaught of bugs we have been facing.

Now, the Build No. can be accessed through the interface:

File -> Account -> About Access

but not all users are comfortable doing this and as a developer it is always best not to put the burden on your users for such information.

So I set out to find out if there was some way to retrieve the Office Build Number through VBA.

Getting the Office Build Number

Once again, it all comes down to knowing where to look, which registry key to read:
Continue reading →