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
ado appendchunk images MS Access

Getting images and icons into a Access column

Last post I showed how to extract icons out of a table column. Sorting data in this way saves a lot of room as compared to storing a OLE object wrapping the image. But how do you get the image into the column in the first place?

That’s what I’m gonna show today.

First make sure you have Edwardo Morcillio’s essential olelib.tlb file. His site contains many useful tools and code. You might also find the ImageLoad with GDI+ useful.

First to load an image from the file you’ll need to do something like this:


Dim oPicture As New StdPicture

Set oPicture = LoadPicture(sfilePath) 'the path to the image file here (.ico, .bmp)

Now you have the image loaded you need to convert it into a stream of bytes to put into your column.


Dim bytedata() As Byte

Picture2Array oPicture, bytedata

Where Picture2Array is defined as


Const GMEM_MOVEABLE = &H2
Const GMEM_ZEROINIT = &H40
Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)

Const PictureID = &H746C&

Private Type PictureHeader
Magic As Long
Size As Long
End Type

Public Sub Picture2Array(ByVal oObj As StdPicture, aBytes() As Byte)
Dim oIPS As IPersistStream
Dim oStream As IStream
Dim hGlobal As Long
Dim lPtr As Long
Dim lSize As Long
Dim Hdr As PictureHeader

Set oIPS = oObj ' Get the IPersistStream interface
Set oStream = CreateStreamOnHGlobal(0, True) ' Create a IStream object on global memory
oIPS.Save oStream, True ' Save the picture in the stream
hGlobal = GetHGlobalFromStream(oStream) ' Get the global memory handle from the stream
lSize = GlobalSize(hGlobal) ' Get the memory size
lPtr = GlobalLock(hGlobal) ' Get a pointer to the memory
If lPtr Then
lSize = lSize - Len(Hdr)
ReDim aBytes(0 To lSize - 1)
CopyMemory aBytes(0), ByVal lPtr + Len(Hdr), lSize ' Copy the data to the array
End If
GlobalUnlock hGlobal ' Release the pointer
Set oStream = Nothing ' Release the IStream object
End Sub

That’s got the image in memory in a byte array. Now we need to put this into a OLEObject column in the database. Note that we are NOT putting a OLE object into that column. But Access won’t care, as long as we don’t put it on a form and try to access it that way.

The only way to put the byte array into the column is via the ADO recordset AppendChunk method. Because I am dealing with small images here I just put it in using a single AppendChunk call. If you’re dealing with larger images, say anything over 64K, you may want to break it up into 64K chunks. Look at How To Read and Write BLOBs Using GetChunk and AppendChunk at the Microsoft support site for more information on that.

For me it was a simple case of;


Dim oadoRS as New ADODB.Recordset

oadoRS.Open "select * from ImagesTable", CurrentProject.Connection
oadoRS.AddNew
oadoRS(0) = 100 'a sample image ID
oadoRS(1).AppendChunk bytedata
oadoRS.Update
oadoRS.Close

There you have it very simple. This proves much easier to handle than if you had to supply the icon and image files and install them to the user’s machine. And its much faster!