Posts Tagged ‘Microsoft Access Developer’

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.

Microsoft Access Requery, Recalc, Refresh and Repaint

Tuesday, August 17th, 2010

Access has four methods that seem to be similar and often get new VBA users confused.  Here is what each of the methods does, and how they are different.


This is the most powerful.  It goes to the database and runs the underlying query again.  For example, if you had a form which was used to add people.  On that form you had a combo box that listed people.  Now you open the form and you have 10 people in your table.  You use the form to add another person.  If you go to the combo, it will still only show 10 people.  The 10 who were there when the form was opened.  If you use the Got Focus to fire off a requery on the combo, you will have 11 people.


Recalc does not get new records.  It processes any pending screen changes.  Here is an example.  You have a unit price, and quantity field.  There is a textbox that has a formula that gives a total price (e.g. = me.txtUnitPrice * me.txtQty).  If you use recalc on the Lost Focus event of both quantity and price textboxes, you will recalculate the total price.  It will not do anything to the underlying records.


Refresh is sort of like requery except that it only updates the records on the screen.  If someone has added another record in a multi user environment, it will not get display the new record.  The good thing about refresh is that it leaves the cursor where it was.  If you have a datasheet, and you are on the third row, use requery and you are back to the first row.  Use refresh and you are still on the third row.

I have a particular application that has a subform which is a datasheet.  Each record has a sequence number.  On the main form, I have buttons to move records up or down.  I select a record on the subform, then use the button on the main form to move it down.  What happens is that it grabs the sequence number, finds the next in sequence and gives it a new sequence number (existing number -1) and gives the selected record a new sequence number (existing number + 1).  I now need to resort them so can use refresh which leaves the cursor on the selected record.  If I used requery, it would be back at the start.  If I wanted to move down twice, I would have to find the record and select it again with requery.


Finally we come to repaint.  This is used to refresh the screen without interacting with the database.  Where do you use repaint?  Say you have a label “Update Processing” to warn users that a lengthy process is happening.  If you set the visible property to false when you open the form, you can set it to true at the start of the update procedure.  At the end you set it back to false. To make the screen display the change to the label use repaint after the change to visible.

It can be confusing and cause considerable frustration when you use a shotgun approach to the four terms.  I hope this will make it clearer to people which method to use in which situation.