Posts Tagged ‘Microsoft Access VBA’

Microsoft Access Tips for VBA Developers

Friday, May 27th, 2011

Microsoft Access developers often use the same code to perform functions in many procedures. One recurring task is to find if a form exists, or a report exists or if the form or report is loaded. Here are a suite of Microsoft Access functions that will help you check for the existance of forms and reports. There are five functions.

  • Check if a form is loaded
  • Check if a table exists
  • Does a report exist
  • Does a field exist in a table
  • Does an external file exist

Read more about them on our latest Microsoft Access Database Development page

Microsoft Access. Adding a field to a linked table

Friday, January 28th, 2011

Ever wanted to add a field to a linked Access database table from the frontend?  Perhaps you have copies of your software running in different environments and want to release a new frontend but not have to manually update every backend.  You can use VBA to create a field.  You need to import the backend table into the frontend, add the field, then export it to the backend database.  After that you need to relink the table.

To see the code click here. You can cut and paste the code into your own module, link a table and use the intermediate window to create a new field.

If you would like to add some comments or suggestions, leave a comment below.

Point of Sale system in Microsoft Access

Friday, December 17th, 2010

We have done some strange Access development but this must be one of the most unusual. We helped a sandwich shop in Tokyo develop a Point of Sale system in Access. Just to prove it really happened, they sent us the before and after pictures.
This is what happened in the past. All the orders were handled manually.

POS system in Microsoft Access

This is the screen displaying each order, the quantity of sandwiches ordered and made for the day and delivery schedule.

POS System in Microsoft Access

The POS system also calculates total cost, generates invoices and lots more.  If you are ever in Tokyo to “The Earl” sandwich shop and see Microsoft Access POS system in action.

Free Access Database compare tool

Friday, October 1st, 2010

Recently we wanted a tool to compare two Access databases and identify what fields and indexes were different.  Looking at what was available to purchase was a price shock.  It should not be that expensive to buy a tool as simple as a database comparison.

The answer was to write the program ourselves.  For all you Access buffs out there, you can download the tool for free from here.  If you try it out, give us some feedback.

SQL in Access VBA

Thursday, September 23rd, 2010

Here is a trap we fell into.  In a VBA procedure we constructed an SQL statement.  In that statement we had a Boolean field which had to be set true.  We have simplified the statement but it went something like this.

strSQL = “INSERT into tblCustomers(CustomerName, Active) VALUES(“”” & txtCustomerName & “””, True);”

The resulting string for a customer called Acme would have been “INSERT into tblCustomers(CustomerName, Active) VALUES(“Acme”, True);”

It worked fine in our testing.  Unfortunately we found it failed in a Portuguese language version of Access.  It took about a week to sort out the problem, but it was the use of “True”.  Not sure if this is a function of SQL not understanding True in Portuguese or if it is a bug in Access.  In any case we changed the True to -1 and all was well.

Hopefully this post will help people with similar problems resolve them without wasting a week of troubleshooting.