Bergesch Computer Services

Sorry, we will work on this more as time allows (and we remember to do so) :-(

Access database stuff

We have used Access databases for more than two decades. We will expand this page as we get time, but be aware, we can only scratch the surface and share some knowledge here. Access databases can become extremely complex.

While there are a lot of templates available for download from Microsoft and other sources, we find most of them are too generic or to specific to use out of the box. And that of course is the nature of custom databases. So if you are going to use Access databases, you will want to learn how to design and program them. It can be a bit overwhelming at first but dig into things and see how it works. It's a great way to keep that gray matter young ;-)

Perhaps the best thing over these many years with Access is it's ability to tap in, extract and even modify data from other databases. Left image.

But that all went away as of Access 2013 (right image). There is no option for external database anymore! Damn-it!

You can however, run two versions of Office/Access on the same computer but Office '13's updates do, on occasion, cause problems with the older version making it necessary to "repair" the older version.

E.g., We had issues where Access would crash out when we attempted to modify code. The only fix was to do a repair on the earlier version then ignore the updates from Microsoft Office.

Sorely missing from Access is a calendar!

So we'll start with a simple calendar you can use, play with and modify as desired... If you like this and/or modify then market it, we wouldn't object to a little cut towards our 401... Access '10 or later

Here is the download - currently very little error checking and it was written for a specific purpose.
Included in the zip are two files, the database itself and a word document explaining it a bit...

Jacks or Better

If you like to play once in a while, here's our database. The algorithms are certainly not casino standards, but a fun pass time anyway... Access '10 or later

Structure

As you build reports, forms, queries, etc., there is a recommended naming convention for each item:

Tables begin the name with tbl
- Note: We use many temporary tables and begin the name with the tilde sign (~) then have code to delete all those temporary tables upon exit (see code at bottom).
Queries qry
Forms frm
Reports rpt

The reason is to simply easy identification but also to avoid having a query overwrite a good table...

So below are various subjects...

Calendar
Tables
Queries
Forms
Reports
Modules
- Specific codes

Tables

While tables look like spreadsheets they are not. The newer Access versions can do simple totaling on raw tables but anything more than a simple column total must be done by queries.

Tables can be internal (local within the database) or external (attached) which are tables that reside in another database or other file.

A simple table design...

Results with storage of data that may look like this.

This very simple table stores the data as shown but the interesting thing is the Autonumber field. This is a numeric field that simply keeps each record's identification sequentially. This is not a required field and depending on the table's usage, may not be necessary.

You'll see the Autonumber field is a "key" field meaning every record must be unique and since it's Autonumber, you can't even enter a number yourself.

Queries

Modules

Modules are simply libraries where a collection of code is stored. Code stored in modules are generally available globally to the database but forms and reports can have all kinds of code as well, but would be unique to that form or report.

Specific codes

Below is a variety of codes (snippets) we use in databases. Some of this code goes back a decade and may not be as "efficient" or clean as could be, but they should work fine...

We can't possibly teach code here but will offer you some potentially handy pieces. For more information on coding, search Microsoft's site and carefully search the Internet. Couple sources we use are:

Delete temporary tables

In our databases, we build temporary tables to populate special forms and so on but it's nice to clean up afterward so we use this code to do so... Note: Code assumes the temporary table name starts with the tilde (~), e.g.,
~tablename

On Error Resume Next
Application.Echo True, "Deleting temporary tables..."
DoCmd.SetWarnings False
Dim tbdf As TableDef
Static tbls(256) As String
Static Entries As Integer

Dim i As Integer
ReturnVal = Null
Dim CDB As Database
Set CDB = CurrentDb
Entries = 0
For i = 1 To CDB.TableDefs.Count
    If Left(CDB.TableDefs(i).Name, 1) = "~" Then
    Application.Echo True, "Deleting temporary tables: " + CDB.TableDefs(i).Name
    DoCmd.DeleteObject acTable, CDB.TableDefs(i).Name
    End If
Next i