Categories
2007 2010 ado dao error 3343 microsoft access

Microsoft Access error 3343 and the not so obvious solution

Recently I was asked to assist on a small project which used Excel to reporting on data within Microsoft Access. Simple enough, said I, I’ve done that before.

Now this project required the looping through of sales divisions with a company database and pulling together several million rows into a quite substantial report – which was ten put into Excel and pivoted. No problem, said I.

Things were going just fine. I setup the master Excel workbook and a script with copied over the template sheets into the destination workbook. But then I ran into a problem. When I opened the database file, a Access 2007 file, I got this error.



Run-time error 3343 Unrecognized Database Format

Which is very weird. This was my code



dim oDBEngine as DAO.DBEngine

set oDBEngine = new DAO.DBEngine
oDBEngine.OpenDatabase "C:\tests\test.accdb"

I hunted around wondering, is my file corrupt? But everything checked out okay.

I short bit of searching and I found the solution, well solutions really.

  1. Use ADO – nope sorry I ain’t going there
  2. Use the Access Object Library ver 12 (14 for 2010)

I will only talk about the second option because that helps retain most of a user’s code when you have been using tried and tested DAO for your Access umm errr access. The above snipped will be replaced with:



Dim oAccess As Access.Application
Dim oDB As DAO.Database

Set oAccess = New Access.Application
oAccess.OpenCurrentDatabase "C:\tests\test.accdb"
Set oDB = oAccess.CurrentDb
oDB.Execute

This simple change does the trick.

I just wonder if there is some intermediary layer between DAO/Jet and the accdb format. It does beg the question why is DAO incapable of opening the file directly?

That’s a mystery for a future post. I hope this helps keep someone from pulling their hair out or (heaven forbid) throwing their laptop out a window.

Categories
Access csv dao delimited file import schema.ini

Loading in CSV files with variable columns

In certain specialised application in Access there will be features allowing users to specify, say, a custom hierarchy. Mainly these are used for reporting so amounts can be rolled up. Typically these are synchronized with the source data which will come from a external source. Now this can mean that the import/export specs in the Access front end will need to change.

What if you wanted to avoid that? How would you import in a CSV file with the new data layout into the database without having to change a import spec or a line of code!

The trick is, of course, to use the meta-data (data about data) to control and validate the input. And using the ability of DAO to open a recordset on a file along with schema data this can be done fairly easily. Here’s one way that I did it.

DAO has the ability to open a delimited file without using a import spec if there is a file named schema.ini in the same folder as the file being imported. We wouldn’t really want our users to manipulate this file so we’ll need to create it using code.

The file looks like this:


[<filename>]
Format=CSVDelimited | TabDelimited | Fixedwidth
ColNameHeader=True|False
Col<n>=<ColName> <datatype>

A sample file looks like so


[test.csv]
Format=CSVDelimited
ColNameHeader=True
Col1=LName  char width 20
Col2=FName char width 20
Col3=DOB date
Col4=Children integer
...

Detailed information on schema.ini file contents can be found at msdn.

Sample code to create this file


dim oFS as new FileSystemObject
dim oFileStream as TextStream
dim nColNum as long

set oTextStream = oFS.OpenTextFile("c:\test\schema.ini", ForWriting, True)
oTextStream.WriteLine "[test.csv]"
oTextStream.WriteLine "Format=CSVDelimited"
oTextStream.WriteLine "ColNameHeader=True"

dim oRS as dao.recordset
'need to read our meta-data to get the names of the columns and datatypes
set oRS = currentdb.openrecordset("select ent_name, ent_type from entities")

nColNum = 1
while not oRS.EOF
     oTextStream.Writeline "Col" & nColNum & "=" & oRS.Fields("ent_name") & " " & oRS.Fields("ent_type") & iff(oRS.Fields("ent_type")="width 50"
     oRS.movenext
    nColNum = nColNum +1
wend
oRS.Close
oTextStream.Close

You obviously wouldn’t use hard-coded names and such, but I was in a rush writing this. As well you’d probably want to store more information on text widths.

This can then be imported using the docmd.transfertext command. However, if you need to run through the file like a recordset then you can use DAO to open a recordset on the file.The SQL to open the file with DAO looks like this.


select <FIELDS> from [Text;FMT=Delimited;HDR=YES;DATABASE=<FILEPATH>;].[<FILE>#<EXT>];

<FIELDS> is typically set to *
<FILEPATH> is the folder the file resides in
<FILE> is the filename without extension, <EXT> being the extension.

To open such a recordset all you then do is


dim oRS as dao.recordset
set oRS = currentdb.OpenRecorset("select * from [Text;FMT=Delimited;HDR=YES;DATABASE=c:\test;].[test#csv]")

And then you can process the data in the file. I used this technique to great success when processing variable data that would end up in a master-detail relationship. The first few columns were known and fixed, the remained stored in meta-data. These remaining columns were then pivoted at load time and then the data merged into the existing data in the database table.

I hope you can all make use of this feature.