MS Access and DBF files with a long filename (Run-time error 3011: The Microsoft Jet database engine could not find the object)
Couple days ago I was asked by my friend to help with automating one of his office tasks. The task was very simple – every weekend their old database system produces number of DBF files in DBASE IV format and also there is a main registry file in MS Excel format. Registry file holds all information about customers, including, let me say, some kind of region id and region name fields. Each DBF table includes only region id and other details. The task was to generate appropriate Excel file for each DBF table, adding extra region name by reconciling region id.
I feel that MS Access will be great for coding such simply converting utility. Last projects I worked with were a complex Java EE and Oracle related things. It was a long time ago when I last touched MS Access, so it was a good chance to play with old plain database tool.
Indeed, in a short time I was able to create table structures, defined index fields to speed up joins, designed simple dialog form and bang out VBA code to import XLS registry file and DBF tables, created reconciling SQL query and finally could export query result to Excel file. Everything worked well, but, sometimes Jet Engine cried out with an exception saying that there is some object could not be found.
Run-time error 3011 : The Microsoft Jet database engine could not find the object. Make sure the object exists and that you spell its name and path name correctly.
Strange, how this can be happen? How it can not find table what definitely exists here?
After some researches I understood that problem was with DBF filenames. It seems that Jet Engine thinks DBF filename should be strictly in old DOS format – no more than 8 characters in filename. Otherwise it throws exception. Hah, sometimes our source tables had long filenames and this was the issue. The first idea was to say my friend to rename DBF files manually, the second idea was to create some script for automate renaming process. But I drop both because each generated Excel filename should remain as source DBF, but with extension XLS.
On the web I found several ways for converting long filenames to short ones. May be, it is good idea to convert long filenames using Windows API or parsing dir /x command output. But, for this simple utility I used more simple way (simple, but not the best of course).
A simple workaround for achieving this was to look in source folder for DBF files, store found filename in a variable, then check filename length, and for long filenames to copy DBF file with some other temporary short name, and then import file into database and after all delete it if it was a temporary file.
At the end this worked well and everybody was happy :))
As there is no secret here with this utility, final source VBA code looks like as below:
2016 February (1)
2014 December (1)
2013 May (1)
2013 March (1)
2013 February (1)
2012 December (2)
2012 October (1)
2011 February (2)
2010 October (2)
2010 July (1)
2010 May (1)
2010 April (1)