Category Archives: Programming

Troubleshooting MS-Access Error 31532 – Unable to export data.

Microsoft Access can sometimes seem to have its own obstinate personality, throwing errors that persist no matter what you try. I wanted to share a recent troubleshooting experience to show some of the steps that you can take when a function is just not working as expected and the decision process involved in fixing it.

Continue reading

Deploying MS-Access Table Design Changes to Remote Files

A New Challenge for Access

I designed a Microsoft Access database for one of my clients and it’s split into two files – a front-end file with all the forms, reports and code and a back-end file with the tables. That’s pretty standard.

During the development process, I needed to make adjustments to the table design – new indexes, fields need to be added and removed, relationships changed. That’s usually no problem if I have remote access to the client’s system and can login and make the changes. In this case, I don’t. Even to deploy a new program version, I have to send it to the client and let them unpack the files to their desired locations.

It’s not the ideal situation but it’s what I have to work with. When it comes to making design changes in the back-end tables, I could try to guide the client rep through the process but I’m not risking that. I’d rather have a routine that will automate it and ensure that everything is done as painlessly as possible.

Continue reading

How to Programmatically Relink Tables in Microsoft Access / VBA

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
 End If

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 more 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.

Continue reading

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

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.

Continue reading

How to Dynamically Set the Report RecordSource in Microsoft Access

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.

Continue reading

Coming in 2019 – CodeScholar Software Development Training!

CodeScholar Logo

CodeScholar is a new type of software development training specifically for educators and self-educators. Coming in 2019 from ComeauSoftware.com and OcalaITPros.com.

A simple Google search turns up hundreds of options for learning programming, many free, but where do you start?  There are many excellent reference sites and YouTube channels out there but many focus on the mechanics of a single language without explaining the broader concepts and best practices. Many are written by developers for (aspiring) developers and might leave you with a sink-or-swim feeling as you pick your way through incomplete answers and abandoned message threads that don’t quite apply.

As a programming instructor, I’ve spent the last couple of years searching the web for the best resources I could find for my students as they struggled to understand concepts such as database normalization and object-oriented programming.  A textbook or two and classroom lectures are not enough when you’re trying to reach a variety of students from different backgrounds.  Seemingly limitless online resources become very limited when it comes to information on a less popular topic such as pass-through queries or deadlocking and you’re trying to find something that will provide enough detail but won’t utterly confuse the students or cause then to tune out from boredom.

Continue reading

Custom User Controls and Events in C#

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 is a great example of code reuse and saves a lot of time.

I’m starting to get the hang of creating demo videos with Camtasia and other tools and my latest videos show how to create user controls and implement them within Visual Studio.

Part 1:

Very often, you’ll want your custom controls to communicate with the host application, passing data back and forth and one way to do this is to have your control raise custom events at specific times.  The host application listens for these events and responds as it needs to.  This is a very powerful ability that enables your classes to interact in more dynamic ways.  Once you understand how to raise and catch custom events, you’re no longer dependent on the C# event model and your applications can signal and respond to any condition you want them to.

The second video builds on the first part to show how to add custom events to your classes.

I encourage you to follow along with the videos and try creating the controls yourself. Also, experiment with your own ideas so that you’ll better understand the concepts.

If you would like to read more about creating custom events in C#, you can find more detail on the Microsoft website at the following links:

How to Raise and Consume Events
https://docs.microsoft.com/en-us/dotnet/standard/events/how-to-raise-and-consume-events

Handling and Raising Events
https://docs.microsoft.com/en-us/dotnet/standard/events/index

 

Using JavaScript with ASP.NET

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.

Continue reading

My Virtual Vacation and Free Stuff

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.

Continue reading

How to Convert Between Roman and Arabic Numerals in C#

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.

Continue reading