Programming Archives - Life Beyond Tech

How to Programmatically Relink Tables in Microsoft Access / VBA

January 15, 2019July 19, 2025 Andrew Comeau 2 Comments

Several years ago, I designed a Microsoft Access application called JobSearch Plus for managing job leads during an employment search. It was a split application, meaning that the tables were stored in one Access file, the back-end, and everything else was in the front-end file with table links to the other one.

JobSearch Plus was also meant for distribution and I had no idea where any of the users were going to store the files on their computers. Access uses absolute file paths to specify the source database for a linked table. This meant I needed some way to determine if the back-end existed and what it’s file path was.

On it’s own, relinking Access tables in VBA is just a few lines of code.

 Dim dbCurr As Database
 Dim tdfTableLink As TableDef
 
 For Each tdfTableLink In dbCurr.TableDefs
   tdfTableLink.Connect = ";DATABASE=" & (Insert new file path)
   tdfTableLink.RefreshLink
 Next

This is fine if you know exactly where the new file is but my program could be anywhere on the user’s computer so I had to do some engineering. Also, since this was going out over the Internet to who knows how many strangers with my name on it, it had to work right … always. This is where the fun of actual software development comes in.

Microsoft Access, Programming

How To Create a Dynamic Search Filter on a Microsoft Access Form

January 9, 2019February 18, 2023 Andrew Comeau 10 Comments

Best practice when retrieving data in Microsoft Access or any database, for that matter, is to limit the amount of data that you pull at any one time. This means using the proper filters and limiting the number of fields retrieved. Often, you still end up with a lot of information to look through anyway. That’s when it can help to have a dynamic search filter that will enable you to easily search the records with just a few keystrokes.

One easy way to do this is use the form’s Filter and FilterOn properties along with the KeyUp event as shown here.

How-to, Microsoft Access, Programming

How to Dynamically Set the Report RecordSource in Microsoft Access

January 8, 2019February 18, 2023 Andrew Comeau

One of the steps for creating a report in Microsoft Access is to define where the data is coming from. This is done through the report’s Record Source property which can be a table or standalone query in the database or a SQL statement as shown in the screenshot below.

Usually, this is defined when the report is created and only gets changed if needed. In some cases, however, you might want to use the same report object for many different queries. You could have a mailing list report that you want to use for employees, customers or other groups at different times and the data would be drawn from different tables or even different databases.

How-to, Microsoft Access, Programming

Custom User Controls and Events in C#

October 22, 2018July 13, 2026 Andrew Comeau

One of the interesting features of C# is the ability to add custom user controls to your forms. If you have some specific functionality centered around a group of form controls that you use repeatedly, you can save it as a custom control in a class library and then add it to other applications. This…

C#, Programming

Using JavaScript with ASP.NET

August 8, 2018April 23, 2024 Andrew Comeau

Why Use JavaScript?

There are a few technologies that you must be comfortable with if you want to do any serious web development. HTML for content, CSS for formatting and JavaScript for dynamic programming are recognized as the three foundation skills that any good web designer needs.

ASP.NET programmers might be tempted to do away with the last item given that .NET code can do virtually anything when combined with HTML and CSS but JavaScript is still very important and the reason lies in the different domains in which each language operates.

Programming, Web Design

My Virtual Vacation and Free Stuff

June 16, 2018March 29, 2024 Andrew Comeau

Summer vacation has mercifully arrived for me and my students. Unlike many teachers, I’m at the school through most of the Summer. My students get three weeks off – I get one week followed by a couple weeks of what will essentially be teacher workdays. Judging from the sympathetic looks I was getting from other staff members and the sudden, dramatic drop-off in attendance of my classes, I’m thinking the break didn’t come a moment too soon.

Programming, Resources

How to Convert Between Roman and Arabic Numerals in C#

March 31, 2018March 29, 2024 Andrew Comeau

The Challenge

My latest challenge to my database programming students was to create a C# class that would perform various number conversions, starting with Arabic numbers (0 to 3999) to Roman numerals and vice versa.

I required that all public functions in the class needed to be declared as static, so that they could be called from outside the class without having to create a new instance. Also, the class was to be completely portable; it must not use any functions such as message boxes that would prevent them from copying the entire class into any other type of project and referencing it. Of course, all code must also have error handling and commenting.

My students came up with various solutions and I think some had more help from StackOverflow than others. I know that one of them learned the hard way not to search for “XXX” on Google.

One popular strategy was to use the C# Dictionary class to hold the Roman characters and their equivalents. I don’t always get around to writing up my own solutions to these challenges before I assign them, so I happily swiped that strategy for my own code which was otherwise written from scratch. I ended up using the SortedDictionary class instead.

C#, Programming

How to Create Lookup Fields in C#

March 8, 2018November 9, 2022 Andrew Comeau

I started my (more or less) serious database development training many years ago in Microsoft Access. One of my favorite controls was the Combo Box, a nice little drop-down field that enabled the user to select one of many values but collapsed down onto a single line. The Combo Box control in Access was very configurable and one of the nice features was that you could display one value while actually saving another value to the field the control was bound to. For example, you could display the Customer Name while saving the associated CustomerID that the database would use to reference the customer record.

C# also has a Combo Box control that can do much the same thing but offers a lot more options through code. The .NET version maintains a collection of objects, any kind of objects, and displays whatever text is presented by the object’s ToString() method. The code can then use any other property of the object to carry out operations in the background.

Programming

How to Retrieve Data from SQL Server into C#

March 7, 2018August 2, 2026 Andrew Comeau

The .NET framework has a few different automated methods for managing connections to SQL Server and other databases. The framework has been around since 2003 and various versions have introduced new, improved data management tools while maintaining the old ones for backward compatibility. It’s still possible, however to retrieve data from SQL Server and other…

Programming

How to Convert Between Decimal and Binary in T-SQL

February 21, 2018March 29, 2024 Andrew Comeau

For the past six months, I’ve been teaching night classes in database programming and my students are just about ready to take Microsoft’s 98-364 exam. Now, we’re transitioning over to C# programming for the next phase of the program but I have to keep their SQL skills fresh for the exam. Tonight, it was time to teach them to understand the binary number system. I dropped some hints on it a couple of months back to brace them but now it was time to get it done with once and for all.

Then, as I like to do with this class, I came up with a lesson plan that would incorporate the best of both subjects …

Programming, SQL