Darragh Murray

Data visualiser and analytics professional

All posts by Darragh

Access 2003 – Importing Queries results in bad query aliasing.

Posted on Author DarraghPosted in Ms Access Tips, QueriesLeave a Reply

This is a quick little tip.

I came across a minor annoyance the other day when I was importing Access saved queries from another database and using link tables. If you import your queries before you link your tables, then try run the queries with no tables link – Access will complain that it can’t find the tables, then litter your queries that use those linked tables with aliases like “expr1:” “expr2:” and so on. This means that if you use a lot of saved queries you’re going to waste valuable time fixing them all up.

The trick is very … Read the rest

Ms Access – The Upgrade

Posted on Author DarraghPosted in Ms Access Tips

I’d never thought I’d ever say something so geeky but I must comment that I don’t think I’ve ever been as excited about downloading a product as I am right now. And right now I’m downloading Ms Access 2007. I’m taking advantage of the http://www.itsnotcheating.com.au offer, and buying the new version of Office 2007 Ultimate for $75. That is a bargain.

I’m presently doing a small bit of consulting work, developing in Access 2003, and have stumbled into a small problem. I have never worked with the Runtime version of Access, and now I’m being requested to deploy my … Read the rest

Ms Access – Percentage Field Formatting

Posted on Author DarraghPosted in Ms Access Tips68 Replies

A quick tip to fix what shouldn’t be a problem!

Occasionally, Access can act rather confusingly. I ran into a typical example early this morning, when I was trying to get Access to allow entry of percentages correctly. I’ve done this about one thousand times before and probably solved this problem one thousand times as well, but every time it happens it causes me to scratch my head (mostly in disbelief).

What happens is this: You set up a field in a table, designate it to be formatted as percentage. Right OK – you go to your form design, set … Read the rest

Useful Access Links

Posted on Author DarraghPosted in Ms Access TipsLeave a Reply

In the absence of any real content, I’ve decided to post a few Access and VBA related links here. Some are rather general, some are rather specific. Regardless, they’ve been floating around in a text file called ‘useful stuff to look at’ on my computer for a while. So why not share the love?

http://www.vb123.com/toolshed/02_docs/classyforms.htm – Forms as Class Modules
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html – “Not Another Access Page” Albert D. Kallal page on random Access related tips. The section on implementing simple mail merges is recommended.
http://www.tek-tips.com/faqs.cfm?fid=5010 – Looping through Form Controls.
http://www.fontstuff.com/access/ – Martin Green’s Office Tips. Great section on Access.… Read the rest

Running SQL from Visual Basic Code – Apostrophe problems!

Posted on Author DarraghPosted in Ms Access Tips, VBA2 Replies

I recently encountered a problem that I’ve actually encountered millions of times before – trying to run an SQL string from VBA code which makes use of double apostrophe’s (“”). Naturally, VBA uses these to break from a string literal, but pure Jet-SQL uses them for functions like Format() and Nz().

So if you do something like this:

strSqlCode = “Select format([field1], “Currency”) from table1″
db.execute strSqlCode

You will get a nice friendly Access message indicating that your SQL is faulty. Surely that ain’t the case!

There are several different solutions and here is one I find the most handy … Read the rest

Ms Access Tips

Posted on Author DarraghPosted in Ms Access TipsLeave a Reply

Hello valued reader!

Over the years, I’ve had the pleasure of doing quite a bit of work in Ms Access, and also in Visual Basic for Applications. On countless occasions I’ve benefited from the work of countless others who toil in this Rapid Application Development suite when I’ve encountered a hitch. I’ve always wanted to ‘give back’ some ideas in regards to my Ms Access experiences.

Therefore, I’ve decided to put some effort in putting up a few ‘tips’ or ‘shortcuts’ is an easy to access manner (no pun intended). These will be sometimes a few generic code snippets, sometimes … Read the rest