Tag Archives: MS Access Tables

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 – Export to MySQL or Another RDMS

Access Export to MySQL

I’ve been migrating a few databases away from Access recently, moving towards more stable RDMS’.  I thought I’d quickly share 2 bits of code that I created to try and simplify the process.  These are geared towards exporting to MySQL, but can easily be adapted to any RDMS of your choosing with minor tweaks.

I didn’t like the idea of using random applications found online for which I have no clue what’s under the hood, how or what is being done with the data… So I set off to create a ‘simple’ VBA solution myself.

Both of these bits of code are not polished off (don’t even have error handling!), and don’t support all the data types, but for me that isn’t a concern as I don’t use attachments, multivalued fields, …  (One more reason never to use them in the first place, they make exporting much more complicated)

Continue reading →

Determine If A Table Is Local Or Not

Sometimes, it can be useful to be able to determine if a table is local or not.

Yes, you can query the MsysObjects system table for those object with a Type = 1 as explained in my article:

MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, ...)
It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows: SELECT MsysObjects.Name AS…

Continue reading →

but then you need to create a recordset, and iterate over it, …

Why do that when there’s a much simpler solution!

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 →

Taking Screenshots In Your Access Database

Camera

In one of my applications, I wanted to enable my users to easily be able to take a screenshot of an Access database object of their choosing.

Now, there are some truly great tools out there, such as:

That said, my need was for an integrated tool with no installation! So what was one to do exactly?

Continue reading →

Access – Copy/Paste Into a Table

I thought I’d quickly post a little performance tip for copying and pasting data to populate tables.

A Quick Note About This Tips
The following tips doesn’t truly apply when copying and pasting small datasets, but is aimed at copying and pasting large amounts of data.
Continue reading →

MS Access – Determine Database Type

Sometimes we, as developers, need to determine certain fundamentals about the database that is running.  One of which is the type of database that the users is currently running.  Is it an mde/accde?  What is the file extension?  I thought I’d briefly touch upon this question today.

The Current (FE) Database Extension

Continue reading →

MS Access – Back-End Environment Switcher

Any experienced developer knows all to well that you need to segregate Production and Development versions of a database.  Moreover, most will even have a 3rd environment, a Testing environment.

Now, one can always use the Linked Table Manager, with all its flaws (both in design and in functionality), and manually go through the dialogs and relink the tables, but often we see questions on forums regarding a better method for people, such as developers, who need to do this regularily.

So, I decided to see what I could put together and this is what I came up, The Back-End Environment Switcher, with for the typical setup.

It has three components:

  • Menu
  • The Environment Switcher Settings
  • The Environment Switcher

The Menu


Continue reading →

MS Access – VBA Relink Tables

I know, you’re asking why in God’s name would you create a new table Relinker when there are a great number already available!

The answer is simple, it could be done better.

The Problem

I do a lot of remote development which means I need to relink to remote database through slow VPN connection and I was wasting a great deal of time while relinking took place.  As such, I did a few tests and notice that most, if not all relinker, including the built-in Linked Table Manager (LTM), performed the same operations

  • For each table
    • Open the source database
    • Relink the single table
    • Close the source database

The Better Approach

The fact of the matter is that most of the relinking time is actually spent Opening and Closing the source database, over and over, and over!  What a waste.  I knew this could be optimized to only open a source database once and relink all its’ tables and so I created my Table Relinker.

My table relinker works in a slightly different way

  • For each source database
    • Open the source database
    • Relink all the tables associated with that source database
    • Close the source database

This way each database is only opened once!

Continue reading →