Access 2007 After One Hour – Initial Highlights

I’ve been using Access 2007 for about an hour now, quickly developing a small database to store information on what periodicals I’ve been reading for my thesis, and when I have accessed them. Quick review – it is fairly impressive! Highlights are:

  • The Ribbon Gui common to all Office 2007 products is used very well in Access 2007
  • Graphical Calendar Lookup for Date Fields.
  • Split View Forms – Graphical Form at top, Datasheet at the bottom.
  • Ability to standardised formatting of controls on a form, meaning left, top, width and height values are consistent for groups of form controls! This
Read the rest

Access 2003 – Importing Queries results in bad query aliasing.

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

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

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

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!

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