Tony's Microsoft Access Blog : Access

Access and PowerShell

Tony — Fri, 27 Nov 2009 17:21:36 GMT

Fellow MVP Richard Siddaway has started a very interesting series of blog postings on using PowerShell and Access.

Jackcess is a pure Java library for reading from and writing to MS Access databases.

Tony — Sat, 24 Oct 2009 21:51:45 GMT

I just came across this interesting Open Source project - Jackcess is a pure Java library for reading from and writing to MS Access databases. Also read the FAQ. Also note the link to MDB Tools which hasn’t been updated since 2004.

Jet Engine History

Tony — Sat, 24 Oct 2009 21:01:48 GMT

A question started a bit of digging and poking about. I’ve always been interested in the history of Access and Jet and the code names used way back when such as Cirrus. The Wiki Microsoft Jet Database Engine article had a footnote referencing a page titled Jet Engine: History

Regretfully all the links mentioned on that page no longer work. Although some of the MS white papers mentioned might still be available by digging.

Which reminds me. There are two versions of the Microsoft Jet Database Engine Programmer's Guide. I have the first edition which is the one with the light blue cloud cover which was sort of the theme of Windows 95. I should purchase the second edition which has a white cover just to ensure it’s part of my collection.

And I should spend a few minutes rereading the table of contents just to refresh my memory so if I ever need some information I’ll at least know that it’s available.

InStrRev

Tony — Wed, 23 Sep 2009 21:32:04 GMT

I’ll be darned. I had no idea InStrRev existed in VBA code. It is new in Access 2000 so it’s been around for nine years.

I had to build this function myself in A2.0 or A97 to get the file name from a string which had the path and file. The code needed to find the right most occurrence of “\” back slash. I’ve been happily using my function ever since.

I actually learned something new about Access this year. <smile>

Queries and the 3061 "Too few parameters. Expected 1." message

Tony — Wed, 23 Sep 2009 04:32:14 GMT

Your query is working just fine referencing a form to select some records:

but when you run it in code you get the dreaded 3061 "Too few parameters. Expected 1." message.

One option is to evaluate the Forms![Form name]![Field Name] expression as per the following examples.

The below example is an execute query.

Set db = CurrentDb
Set qdf = db.QueryDefs("LEMBatchesRpt Append to temp table")
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i

qdf.Execute dbFailOnError

The below example is used in my standard routine which copies the contents of a recordset into an Excel spreadsheet. Due to the requirements of basing the openrecordset on a query to which I added a Where clause I created a temporary query and then did the parameter evaluation

strSQL = "SELECT * FROM [" & strQuery & "]"
If Len(strWhere) > 0 Then _
strSQL = strSQL & " WHERE " & strWhere & ";"
Set qdf = db.CreateQueryDef("", strSQL)
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i
Set rs = qdf.OpenRecordset(dbOpenSnapshot)

….

' Insert recordset into cells starting at left hand side and second row
' Using a copyfromRecordset is much, much faster than stuffing in the cells one at a time.
.range("A2").CopyFromRecordset rs

This is used in a generic routine behind the very flexible reporting form in the Granite Fleet Manager. When the user clicks on the Export Report to Excel command button I look at the record source of the report to fetch the query name. I then build the where clause and execute the above code.

A new version of the Auto FE Updater is available

Tony — Tue, 08 Sep 2009 22:53:11 GMT

A new version of the Auto FE Updater is available at my new, somewhat reorganized website http://www.autofeupdater.com/

Added FileExtension and FileExtensionSimple to the StartMethod. This now means the utility can be used for ADP/ADEs or any other file extension really. This method can be easier for the developer for testing purposes than AutoSelect if the developer is using Access 2003 on their system and has Access 2007 installed as the AutoSelect starts the newest version of Access. Thus causing the annoying wait while Access 2007 installs itself.

Added a debug checkbox to the developers form so you can see what the Auto FE Updater wants to do before you run the utility

Updated website showing the CreateShortCutOnPrograms and CreateShortCutOnCommonPrograms options which have been available for some considerable length of time but weren't documented on the website. Oops.

An approach to sending text messages from within an Access application

Tony — Thu, 06 Aug 2009 20:55:34 GMT

Steve Schapel, fellow Access MVP, answered a question about sending SMSs with a page on his website. An approach to sending text messages from within an Access application I can see this capability being quite useful in some consumer facing applications requiring last minute notifications. One of Steve’s applications deals with school sporting events so last minute SMSs are very handy.

Now it is possible to send emails to the cell phone gateway email accounts. An example might be 78013456789@text.yourcellco.com. However this requires asking the person what cell phone company they use as the domain to the right of the @ sign is cell phone company specific. Also you run a risk of the cell phone deciding that your emails are spams. I would assume this is less of a risk if you use a SMS service which you pay for.

As an aside the SMS outfit that Steve has used with good success is in South Africa. While Steve’s clients in are New Zealand. I find it quite interesting that SMS service is reliable between continents.

Misleading error message - "Microsoft Access Was Unable to Create an MDE Database"

Tony — Thu, 25 Jun 2009 22:48:42 GMT

Someone got this message using Access 2003 but this could also apply to other versions of access. Clicking help on the error message then displays

"This error is usually associated with compiling a large database into an MDE file. Due to the method used to compile the database, a considerable number of TableID references are created for each table. The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).

There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."

(I included the entire message so that folks searching on this message could find this blog posting.)

This posting sounds ominous but unless you have more than 1000 objects in your MDB/ACCDB you're not even close to having this problem. And that assumes that 1000 objects also have VBA code. So, for example, you'd have to have 100 tables, 300 queries and 700 forms and reports that also have VBA code.

The first thing to try is compiling the code to see if there are any errors. Ctrl+G to get to the VBA editor >> Debug >> Compile

Steps to think about when doing a data MDB back up, compact or zip.

Tony — Thu, 18 Jun 2009 22:16:27 GMT

I have a button on my Granite Fleet Manager app which allows the user to do a backup of the backend data MDB. I also have two other buttons which zip and email the backend data MDB to either themselves for backup purposes or to me for support purposes.

However any open forms or reports will lock the back end MDB file so a rename and compact won't happen.

As I do not know which forms and reports the user might have left open when they click on any of the buttons I run through some code that closes all open forms and reports, then run the code doing the rename, compact back to the same file name and, if requested do the zip and email.

' close all forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm
DoEvents
For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
Next rpt
DoEvents

DoEvents seems to be necessary to give things time to close. But maybe not..

Also note that I do keep a status form open during the above operation. I also do not have any list boxes or combo boxes with the row source set to a query or table referencing the data MDB on the form. And I don't have any open recordset or database variables for performance purposes.

You can double check that this code works by stopping the code just after the above and seeing if the ldb file on the data backend mdb no longer exists

Of course if there are other users in the back end then the rename won't work of course. Also see my Backup, do you trust the users or sysadmins? tips page for more info.

An older version of MSCOMM32.OCX has had the "kill bit" flag set.

Tony — Mon, 15 Jun 2009 00:28:56 GMT

The latest Windows Update has set the kill bit flag for an out of date but commonly used version of mscom32.ocx. Microsoft Security Advisory (969898)

Options

- roll back the update. Not recommended however instructions are in the above web page.

- locate the newest version of mscomm32.ocx and distribute to your users/customers. Not recommended as this will require the users to have admin privileges on their system to install the OCX or for the IT staff to do the update. In the meantime your app's serial port logic isn't working

- replace the mscomm32.ocx with API code. Perform Serial Port Communication - The sample code on this page consists of a VB module containing a collection of routines to perform serial port I/O without using the Microsoft Comm Control component. The module uses the Windows API to perform the overlapped I/O operations necessary for serial communications. Recommended

Symptoms

Errors such as

"<your application> doesn't support this ActiveX control".

"no inserted control" (or something like that)

"Object doesn't support this property or method"

When you open the form in design view you will get the following message "There is no Object in this control" and the control will no longer show the telephone icon.

Smart Access Magazine

Tony — Tue, 02 Jun 2009 02:23:35 GMT

Garry Robinson, fellow Access MVP, has purchased the Smart Access Magazine. there was 300 articles/2000 pages written by over 100 of best Access professionals in the world under the guidance of editor Peter Vogel.

(Although I do recall emailing a few authors some corrections and never hearing back from them.)

DbUtilities - Transfer object permissions from a secured database to a new database container

Tony — Sat, 23 May 2009 23:57:46 GMT

The first item "is a fantastic re-write of DbUtilities which, as an Add-in, makes it easier to transfer the object permissions from a secured database to a new database container. " Author: Jeff Conrad (aka "Access Junkie"), former Access MVP and now a test for Microsoft.

Sandra Daigle's Microsoft Access Database Samples as well as other useful utilities. Including "a way to open and manage multiple instances of a single form using a collection class"

Redimming an array 1,000,000 times takes 4.63 seconds

Tony — Fri, 22 May 2009 02:31:30 GMT

The following question was asked at StackedOverflow.How do you redimension an array in VBA?. One response was some detailed code showing how to redim an array efficiently by doubling the number of elements. This took 0.41 seconds for a million records while redimming 1,000,000 took 4.63 records.

I don't think I've ever had over 100 elements in an array. Maybe 1,000 once or twice. And now I'm not at all concerned about efficiency while redimming.

Note that I only occasionally visit that website to answer questions. My biggest complaint is that you never know what comments or postings are new and haven't been answered. Very much unlike using a NNTP newsgroup reader rich client. But it's interesting to poke about to see what's new has been answered.

It's also interesting to read what bull**it has been spouted by folks who view Access with a lot of disdain. David W. Fenton is doing a fine job of answering such comments.

Latest Access 2003 hotfix - 970623

Tony — Fri, 22 May 2009 00:58:22 GMT

"When you try to import all objects from an Access 2003 database into a new one, you receive the following error message: There isn't enough free memory to update the display. Close unneeded programs and try again."

Access 2003 hotfix package April 28, 2009 - 970623

Office 2010 The Movie

Tony — Wed, 13 May 2009 02:57:34 GMT

Office 2010 The Movie Interesting video that. But notice the link to the Office 2010 blog.

Microsoft Office 2010 Technical Preview

Tony — Wed, 13 May 2009 02:25:32 GMT

The Microsoft Office 2010 Technical Preview is a limited, invitation only program which will provide you with the opportunity to experience early, pre-release versions of Office 2010 which will include the following applications: Word 2010, Excel 2010, Outlook 2010, PowerPoint 2010, OneNote 2010, Access 2010, InfoPath 2010 and Publisher 2010.

Exceeding 2 Gb MDB file size (and a bit of TGIF humour)

Tony — Fri, 08 May 2009 23:14:50 GMT

Exceeding the 2 Gb MDB file size gives you an exceedingly misleading error message:

In Jet 4.0 one message that is received when the 2 Gb file size is exceeded is invalid argument. This is, of course, utterly confusing and can mean other things such as corruption.

In A2007/ACE (I was running in an ACCDB) I got the following message

3049 Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.

running the following code. (Watch for line wrap.)

Const FillText As String = "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD
LJASLDJK ALSJKD LAJSD lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    "ASdlkjSLDJKADSLJADSLJ ASDLJK ALSDJ LASJKD LJASLDJK ALSJKD LAJSD
lJKA SDLJK ASDLj ASDLj ALSDJ LJ " & _
    " AS:LK:ASDK A:D :AKSD :ASDK:AKSD :AKS D adfasdf asd fasdf asdf"
Sub Filltable()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
tagTop:
    rs.AddNew
    rs!field1 = FillText
    rs.Update
    DoEvents
    GoTo tagTop
End Sub

Now this code runs for several hours so be patient. I only ran it inside Access 2007 and ACCDBs. I'll run it in Access 2003 just to double check that message. (Out for steak, Caesar salad and mugs of red wine with friends.)

Now note that you don't want an autonumber key on the table. Just the text field. I suspect by the time you hit five million records the overflow areas of the index will get real, real slow. But I don't feel like testing that.

Humour

Bill Mosca, fellow Access MVP, had the following to say about my FillText string: Here's a site for greeking text...in case you need to fill a longer string without monkey-typing it yourself.
http://www.duckisland.com/GreekMachine.asp

Of course another alternative is Lorem Ipsum. I used a paragraph from that in a reply to someone who stated "Of course, we're going to have to argue that one for a while."

Dreaming of Rails as the Next Microsoft Access

Tony — Tue, 05 May 2009 05:31:01 GMT

An interesting blog posting titled Dreaming of Rails as the Next Microsoft Access (Rails meaning Ruby on Rails of which I know nothing other than having seen the occasional reference it.) Now I disagree with the authors characterization of us as "Office drones". <smile>

Nevertheless interesting reading.

Note that I located this blog entry because I'm now following #access, #msaccess and #microsoftaccess items on Twitter. I'm currently using the Tweetdeck client but I'm not overly impressed with the UI. I also am Tweeting (I think that's the correct verb) my blog postings.

The problems with currency fields

Tony — Tue, 28 Apr 2009 01:48:51 GMT

The Access help on currency fields states:

Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. A Currency field occupies 8 bytes of disk space.

Excellent you figure. Now if your code and queries are doing any multiplying or dividing you just have to remember to round to two decimal places.

And you're done. Not so fast. (Otherwise what's the point of this blog entry.)

Turns out the users can accidentally enter a tenth of a cent and/or a hundredth of a cent. And you won't notice it unless the focus is on the field. And you won't notice unless someone points out a weird rounding problem.

One of my clients MDBs had three of those fields. Worse, one of them was on a parts price and thus would've continued to propagate for years. Another client had the $0.001 on a rate table and it had been transferred to 500 of the 600K records on the table.

There is a KB article on this topic ACC2000: Round or Truncate Currency Values to the Intended Number of Decimals which shows how to put some simple code in the AfterUpdate event of a currency control.

To find those wonky data in the tables use the following query:

SELECT sriInvoiceAmount
FROM ServiceRecordInvoices
WHERE sriInvoiceAmount<>Round([sriInvoiceAmount],2);

And you're done, right? Wrong.

Dates

Dates can have a similar problem when the century portion is hidden by the default Windows date formatting. You will find this when attempting to upsize the Access database to SQL Server as the SQL Server smalldatetime field has an epoch date of 1900-01-01. In a table of several hundred thousand records we found 12 of these records where the value was before 1900. The dates weren't actually used in any computations which is why no one had noticed them.

Internationalization

Whoops. Most North American developers would think in terms of pennies so rounding to 2 decimal places is great. But not so the folks in other countries.

So I created a constant on my hidden Global Options form and placed the value of 2 in it. I referenced that form variable in all my code and queries so I'm set. If I ever sell my software in another country I'm set to do some more investigating on the topic of how to fetch the currency decimal places from the users regional settings and go from there.

(Note that a potential problem could exist if the users had their systems formatted in different currencies or different decimal places. Which is why I'm ignoring this end of the topic for now.)

Currency formatting display.

Finally when I did some searching on this topic I came across several pages on a related topic again of interest to folks whose applications need to support multiple currencies.

Using the Currency field data type - without the hassle
Currency format
Microsoft Access and the Ten-Year-Old Currency Bug

I sure hope there is a final exam on this course

Tony — Mon, 27 Apr 2009 22:02:22 GMT

Project title: Need 15 Queries in Access

"I need someone who can make 15 queries in Access 2007 and have to be done in sql view (this is a must). I want this for school home work and need them fast, as soon as possible. I will provide you with the tables and the queries I need, they are attached so you can tell me if you can do it or not. Payment will be made in full after project is completed. I will not consider anyone without any reviews. Bid only if you can do this project, I need it fast so don't make me waste my time. I wil... "

And if you click through the link Place my bid for this project! you will see the replies. Wow.