Category Archives: MS Access General Information

Access Pivot Tables and Pivot Charts

Surprised to see someone talking about using Pivot Tables and Pivot Charts, aren’t you!

For those that aren’t already aware, Microsoft did away with these, too much protest I must say, with the release of Access 2013.

To Much Protest ....
When I mention “to mush protest” I was referring to the deluge (by Access standards) on Uservoice asking to have the feature reinstated.  There were numerous suggestions all asking for the same thing and 2 of which were the 2nd and 5th most supported requests, yet both were ‘No Current Plan’.  You can view all the details for yourself by perusing:

access.uservoice.com 2020 review
I was recently in a discussion in which I stated that I don’t use or believe in uservoice.com as a whole and was told that I make broad statements without ever backing them up. As some of you know, I have already done a couple reviews of access.uservoice.com in years past: access.uservoice.com A Year In…

Continue reading →

Over the years I’ve seen numerous questions relating to how we could get them back…. So that got me thinking and I came up with a couple possible solutions:

  • use the WebBrowser control to replicate the features
  • automate Excel and create them there instead

In this article, I will concentrate on the latter and perhaps in the future (if there is some interest) I will explore the automation of Excel option.
Continue reading →

Access – Cannot open a database created with a previous version

Access - Cannot open a database created with a previous version of your application

After seeing this one in forums numerous times over the years, and being contacted via my blog regarding this issue, I thought it was long time overdue that I briefly touch upon this error:

Cannot open a database created with a previous version

What Is This All About?

The short of it is that newer versions of Access no longer support older versions of the MDB file format.  They only support the newest 2000 and 2002-2003 MDB file formats.

So if you get the above error message that indicates that your MDB is in an even earlier MDB format, ’97, … 2.0, …

Continue reading →

Making Excel Linked Tables Editable

Many of you are aware of my previous article about linking to Excel spreadsheets

Advanced SQL To Connect With Excel WorkBooks
My previous article explored using plain SQL to connect and work with other Access databases: This time, I thought I’d show you how you can create SELECT statements that can bind directly to an Excel Workbook to work with the data without the need of Linked Table or advanced VBA coding.

In the section entitled ‘A Workaround For Linked Tables’ I provided some VBA code to change the necessary connection property.

Knowing that not everyone is comfortable with VBA, I decided to quickly put together a simple form that you can import directly into any database and use it to configure the connection property for you so you don’t need to know a thing about VBA!

It’s a simple import and use solution.

Access Excel Linked Table Connection Fixer

Continue reading →

Microsoft Access Hyperlinks

I’ve always avoided using Hyperlinks.  There are a number of reasons, but my main complaints are

  • the UI is hidden!
  • multiple extra clicks required!
  • the UI simply isn’t user-friendly
  • it is very easy for users to erroneously edit the wrong values
  • the way it is managed causes extra headaches when trying to automate them
  • Hyperlink can automatically get re-written using relative paths so you loose proper oversight, you can no longer properly report links or sort …
  • Opening hyperlinks can trigger security notices which is not a good user experience

You can find posts and videos going into depth as to why Access Hyperlink fields are less than ideal.

Personally, just like attachments, most experienced developers prefer to simply store hyperlinks as plain text and automate them using simple VBA (Application.FollowHyperlink).

Okay, back to Hyperlinks.  Regardless of my personal views, the simple reality is that many people still use them and so I thought I’d touch upon them briefly today to hopefully clarify a couple things about them.

Continue reading →

How To Work With Microsoft Access RTF Fields

Access RTF Menu

Today, I thought I’d touch upon RTF (Rich Text Field(s)).

If you’ve looked over my

MS Access - Improved HTML
Here’s yet another long standing demo I’ve been wanting to provide the community for a couple years now. Origins Several years ago I developed an e-mailing database.  I originally started out using the Rich Text Format (RTF) provided by Access, but anyone that gets into anything remotely advanced will quickly find out the Microsoft’s implementation…

Continue reading →

you’ll know I’m not a huge fan of RTF.

Why?

Well, RTF is simply a crippled implementation of proper HTML.  I’ve always thought that if you’re going to do something, then do it properly or don’t do it at all! In this instance, that would mean implementing proper HTML, but that’s obviously not the view Microsoft takes of things!!!

At the time of writing my Improved HTML article, RTF could not do basic HTML things like:

  • Justify text
  • Insert links
  • Insert tables
  • Insert images/media
  • Format text
    • Strikethrough
    • Superscript
    • Subscript
  • Use predefined formats (p, h1, h2, h3, …)
  • and the list goes on, and on, and …

That’s neither here or there for today’s subject matter though.

I know many people use RTF because it is convenient, so I thought I’d cover a couple quick issues/questions that sometimes come up in forums.

Continue reading →

Stop Using Special Characters!

Blackboard - Know the Rules

If you’ve read my posts:

Access Best Practices and Troubleshooting Steps
I thought I’d make a quick post to go over some basic Access development best practices that every developer should be aware of.

I’ve tried to emphasize the fact that Reserved Words and Special Characters were to be avoided at all cost.

Why Exactly?

Both Reserved Words and Special Characters simply cause all sorts of extra headaches (in, amongst other things, Queries & VBA) and can lead to odd and unexplainable behaviors from the database itself.

Continue reading →

Optimizing Access Database Performance

Here’s a Massive Question to Answer:

How can I make my database faster?

This always makes me think of a quote from Access MVP Alumni John W. Vinson

Database performance optimization can be a bit of a black art.John W. Vinson

Black art indeed!  The simple fact of the matter is there is no one answer that solves the issue for every database and will miraculously speed up your database.  The reality is that there are a multitude of potential aspects that can be perfected, improved upon, tweaked, each contributing to improving the overall performance.  There are db admins that spend their careers only doing optimization.

 

Where To Start

Today, I will cover those aspects that I have found can have the biggest impacts on performance improvements, mainly:

Let us briefly touch upon each.

Please note, that I am purposely omitting proper database design in the present discussion.  For me, proper database design is the most critical aspect of database performance, no doubt, but it is way out of the scope of any optimization article and is a subject onto itself.
 
Continue reading →

Microsoft Access AutoExec Macros and Display Forms

Today, I thought I’d explore AutoExec macros a little today.

What is an AutoExec Macro Exactly?

The short answer is it is a macro that is automatically runs when the database is opened, assuming the database is Trusted or had its content enabled!

What Purpose Is an AutoExec Macro?

They are great when you want to run some code prior to allowing the user to interact with the actual database.  They can be used to:

  • Check linked tables and re-link them automatically
  • Enforce security restrictions
  • Setup folder/file structures required by the database
  • Perform automated backups
  • Open/run database objects (tables, queries, forms, reports, macros, …)
  • Import data
  • …

Continue reading →

Is Access a Low-Code/No-Code Solution?

At this past Ignite conference, our new Microsoft Access Program Manager, Michael Aldridge, hosted a session regarding the new Access Dataverse Connector. I briefly touched upon this in my article Access Session At Microsoft Ignite, Yes It Is True! and provided a link to the recording of the session itself Bringing Microsoft Access into the Future: Introducing the Microsoft Access Connector for the Microsoft Power Platform.

In the session, Michael mentions several times the fact that Microsoft Access was a low-code/no-code solution

Access was one of the originators of the low-code/no-code solution development before it was even called that — around 1:20Michael Aldridge

and I wanted to touch upon this statement for one moment.

Important Note!
Before going any further, I wanted to emphatically state that I do not think Michael meant in any way to demean Access or the Access Developer Community in any manner with such as statement. He seems to be very supportive of Access.

We have to put the comment into the context of the presentation being given (Dataverse which is being promoted as low-code/no-code), but I thought it still brought forth a good subject for discussion. So put away your pitchforks and let’s have a frank look at Access for one moment.

Continue reading →