When is an Access database not a database?

I doubt that the user manual for the screwdriver shows how to use it on anything but fasteners. But you understand this tool well enough to have used it in some unconventional way – like prying, chipping, or scraping something. And chances are, it worked.

Microsoft Access is also a tool – a database tool. The user manual (remember those!) explains how to use it to build databases. But that doesn’t mean it can’t be used for anything else.

Let’s build something in Access that isn’t strictly a database in the traditional sense of the word. I’m hoping that once you see this example, you’ll look at this extremely popular tool in a different light and think of some creative ways to put its features to use in your own organization. After all, you probably already own it. Why not use it!

If we create a new Access database file, is that file a database? What if we never add any data to this file? It would seem disingenuous to call a file a ‘database’ if it doesn’t contain data. But then, why would we even create an Access database file if we weren’t going to store any data in it? Good question – let’s take a look at one use case. And remember, I’m not saying these examples are the only, or best way to approach these solutions. But I want you to realize that this is just a tool, not unlike that screwdriver you’ve used to pry stuff or scrape paint.

I’ve created a new Access database file named ‘AccessUnconventional.accdb’. This is the file we’ll work with through this post. There are no objects in this file at this point, as you can see.

The first thing I want to show you is that we can build a form to use as a user-interface for virtually anything we want. So I’ll create a new form object and add a couple of buttons to it and then we’ll make each of the buttons do something to make them more useful. I’ve also added a label control next to each button so we can inform the user what the buttons will do.

At this point, the buttons don’t do anything. I will use VBA (Visual Basic for Applications) code behind each button to give it a purpose.

It’s always a good idea to assign names to your controls, so I’ve named each of our buttons. Their names are now cmdButton1 & cmdButton2 respectively. Let’s make cmdButton1 open the Windows Calculator application. We’ll make cmdButton2 open a new blank Word document.

After changing each buttons’ caption property and editing the label captions to be more informative, I saved the form with the name ‘frmUnboundInterface‘. Here is what it looks like now –

The VBA code to open the Calculator looks like this:

Private Sub cmdButton1_Click()
    Shell "calc.exe", vbNormalFocus
End Sub

To open a blank Word document, I wrote a basic VBA procedure called OpenWordAndNewDocument –

Sub OpenWordAndNewDocument()
    Dim objWordApp As Object, objWordDoc As Object

    Set objWordApp = CreateObject("Word.Application")
    Set objWordDoc = objWordApp.Documents.Add(Template:=objWordApp.NormalTemplate.FullName)

    With objWordApp
        .Visible = True
        .Activate
    End With

    ' Clean up
    Set objWordDoc = Nothing
    Set objWordApp = Nothing
End Sub

And then call that procedure when the button is clicked –

Private Sub cmdButton2_Click()
    OpenWordAndNewDocument
End Sub

All we are doing here is using the Access program to build an interface that we can program to do just about anything we want.

Remember – there is NO DATA in this file.

Now, let’s say we have some Excel reports we want to make easily available to users. By adding a few controls to our form, adding a list of documents we want them to be able to open, and a little bit of VBA code behind the new button, we can have a nice little report menu for our users.

For this example, I’ve added a combo box control, which I named ‘cboFileList‘, and another command button, which I named ‘cmdOpenSelectedFile‘, to our form. The combo box control needs to list all the documents we want to allow users to choose from. The easiest way to do that is to add a small table to the file and have the combo box query this table to display the choices.

I created a table called ‘tblAvailableDocuments’ to store the file path and file name for each choice, plus I added a field to indicate whether a document was still active or not. This allows us to simply make a file inactive in the table so it won’t show up in the combo box anymore.

Once the combo box is hooked up to the table, the user can choose one of the file names

and then click the cmdOpenSelectedFile button to open the selected file.

The code for the cmdOpenSelectedFile –

Private Sub cmdOpenSelectedFile_Click()
    Dim xlApp As Object, xlWorkbook As Object
    Dim strFullPath As String, strUNCPath As String, strFileName As String
    
    strUNCPath = Me.cboFileList.Column(1)
    strFileName = Me.cboFileList.Column(2)
    
    strFullPath = strUNCPath & "\" & strFileName

    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(strFullPath)

    xlApp.Visible = True

    Set xlWorkbook = Nothing
    Set xlApp = Nothing
End Sub

An interesting fact about this file is that it’s an Excel report linked to data in a remote SQL Server database, NOT this Access file. The data being displayed has nothing to do with the Access file we are working in right now.

Now, one could argue that adding ANY table to the file makes it a database. I won’t argue that technically, there is now data in this file. But it’s not like we are collecting and querying real data from people, transactions, machines, etc. The table is more like a setting table or lookup list. So we are still using Access as something other than a database.

Let’s say you don’t want some snoopy user to be able to use this new button. With a single line of code, you can disable the button if that user is the one who is logged in –

Private Sub Form_Open(Cancel As Integer)
    Me.cmdOpenSelectedFile.Enabled = (Environ("Username") <> "RichPowers")
End Sub

Imagine how you could extend this interface to be THE place that users go to get certain reports or documents. You could add a tab control and have separate tabs for different topics/departments/functions, etc.

In all of the examples, we still haven’t really added any true ‘data’ to the database. Sure, we added a list of documents, but that’s more of a reference table. And frankly, that list could be something that’s maintained in a SharePoint list, or even another Excel file!

As to the question of whether this file is a ‘database’, I would say no it isn’t. But it’s a great canvas on which you can build an interface to do almost anything you can imagine. You could do this in a Teams App. You could have a Web App built to run in a browser. And I’m sure there are plenty of other ways that you could build this. But if you’re looking for someplace to put your programmable buttons to share with other in-house users, Access may be just what you are looking for.

If you need help programming those buttons, we’d love to help. Contact The PC Help Desk to hear more about how we can help you get more value out of the software you already own.