Converting postcodes to longitudes and latitudes via Mappoint - Microsoft Access application

Using the techniques mentioned on a previous article regarding converting postcodes (well, actually full addresses if required) to latitude and longitude via VBA, the Poorhouse conjured up a Microsoft Access application to do this en masse.

Online file conversion

The Internet is great for sending files around, whether they be documents, pictures, sounds, videos or any other such electro-data. What is sometimes less great is when the proud recipient of your favourite picture of your hilarious office antics, or chain letter that if you don't forward to 7 people immediately you will die a painful death, can’t actually see the file because they don't have the right obscure program needed to open it.

This problem is only exacerbated by the profusion of admittedly cool new technologies where for instance a Nokia phone can bluetooth a sound recording to your Apple Mac via a Palm PC and so on. Who knows what format that will turn up in, or how to open it? If horror-of-horrors you aren't even on your own computer the chances of you finding and installing a program that will let you convert such things is minimal. So luckily you don't have to any more, Media Convert will do it for you via the web.

Fixing corrupt Access files

Microsoft Access, whilst in the Poorhouse's view is often a superfast and supereasy way to get useful results from data sources, is kind of notorious for buckling under pressure. Some of this might be elitist "it makes it easy therefore it can't be any good" type nonsense, but on the other hand it most certainly get a little weak at times when several people are accessing large amounts of data with it. Whilst most often it recovers well and the only serious effect is slowness and annoyance, rarely the database .mdb file itself can become corrupt and, ostensibly, unusable.

Syncing from Palm tasks to MS Access

Next up, the Poorhouse wanted to try some 2-way sync action with the Palm task (aka todo) list.

Being slightly familiar with MS Access, it was decided we'd sync the Palm tasks to an Access equivalent. Hopefully this would provide a good framework to sync them to anything else, as just the code involving the Access API would need to be replaced.

First we needed some synchronisation logic. Some methodology is required so that the sync works; tasks added in either database are added to the other, same with deletion and editing. Moreover, the correct action needs to be done - if two records don't match, which one do we go with?

Run-time error 13 and recordsets in Access

Access VBA and its cryptic error messages eh? Run-time error '13': Type mismatch is probably not unfamiliar to most Access aficionados. And to be fair, it even makes sense semantically. If you're mucking up your data types, you'll see it. If you're foolish enough to try and use numbers where strings are needed, booleans where dates are required and so on, you're asking for it.

Where it can become cryptic is in some of the object libraries, especially when two or more libraries have objects with the same name. A very common example of this is a Recordset.

Converting Access macros to VBA

Microsoft Access contains two main ways of automating non-trivial tasks: macros and Visual Basic for Applications. The former is an Access specific way of building up multiple commands to be run on your database in what is probably a relatively easy way if you just need a simple task doing. The latter is a programming language that you can use throughout the Microsoft Office suite and beyond. Harder to learn, but infinitely more flexible and useful. So much so that rumours had been heard that at some point macros will be phased out of Access.

Linked queries in Access

Back to the (Microsoft) Office...Today's cunning tip is that of linking queries between two Access databases.

It is easy to link tables in Access; that is to say that your Access application can use, edit, add and delete data that physically exists in a different Access application's tables. This allows you to have multiple independent front-ends for the same background data for instance, gain the ability to develop new forms and reports whilst other people are using existing ones, or have an application that uses data from two different databases. Do this via the "Link table" option you get, when you create a new table (Click the Insert menu, then Table). However at first sight it isn't possible to do this for queries.

Word mail merge sometimes doesn't show Access queries

It is common practice to want to perform a mail merge from Microsoft Word, using data from Microsoft Access. Normally, this is extremely easy, especially if you use the Mail Merge Wizard that you can find under the "Tools" menu after selecting "Letters and Mailings" (in Word 2002 anyway). It will lead you to choose first the Access database you want to get the data from, and then the table or query you want to use.

Sometimes however when it asks you what table or query you want to use, the one you carefully handcrafted for this purpose isn't it the list. This happens mostly if it is a parameter query (a query that the user is expected to type something into to generate the data), but sometimes erm...just for fun.

Faking page headers on a Microsoft Access subreport

In a tedious Microsoft Access report featuring pages and pages of meaningless numbers in a table you quite often want the table headings (the bit labelling the column) to repeat itself at the top of each page. That way the recipient has no excuse for not knowing what a hundred pages of mind-bogglingly dull numbers mean.

Normally Access provides a Page Header section of the report for exactly this purpose. If you can't see it then in the report design view, go to the View menu and select "Page Header/Footer". Whatever you write in this section will appear at the top of each page.

docmd.transfertext doesn't work so well either

Undeterred by the previous significantly buggy annoyance of docmd.transferspreadsheet, the Poorhouse was commanded to transfer some more facts 'n' figures; this time in the ultimate old-skool format, a flat text file. The info within was to be featured in a Microsoft Access table.

In order to transfer data from a text file - for example a comma separated values (csv) file or a fixed length file – Access VBA includes the command TransferText. The syntax is as follows:
TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

Syndicate content