Access 2007 file format detection through DAO?

N

Nando

Hi all! Not long ago I created a tiny EXE that scans files in a folder and
detects the format of the files for some inventory and data consolidation.
It works fine for mdb files (2000, 2002, 2003). But it cannot detect Access
2007 format.

The EXE is written in Visual Basic 6 using DAO3.6. Below is the code I use,
but whenever I encounter an Access 2007 (accdb) it fails at the first line
("3343: Unrecognized database format"). Is there any updates for DAO or any
other way I can gather some basic database information from the file?
Thanks!

Set db = DAO.OpenDatabase(MyFilePathname, False, True)
sver = db.Properties("AccessVersion")
 
6

'69 Camaro

Hi.
It works fine for mdb files (2000, 2002, 2003). But it cannot detect
Access 2007 format.

The EXE is written in Visual Basic 6 using DAO3.6.
Click to expand...

DAO 3.6 cannot read ACCDB files. OLEDB is required to read the ACE database
file format. If you don't have Office 2007 installed, then you can download
the free Data Connectivity Components for the 2007 Office System Driver.
Please see the following Web page for the download and sample connection
string to connect to the ACCDB files:

http://www.microsoft.com/downloads/...36-8C28-4598-9B72-EF94E038C891&displaylang=en

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
N

Nando

Thanks 69 Camaro, any sample code I can use to implement this connectivity
pack? How do I open database and check the AccessVersion Property?
 
6

'69 Camaro

Hi.
any sample code I can use to implement this connectivity pack?
Click to expand...

Sorry. I don't post code I haven't tested, and I don't have a way to test
Access 2007 ACCDB files at the moment. Perhaps someone else can offer some
assistance.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
T

Tony Toews [MVP]

Nando said:
The EXE is written in Visual Basic 6 using DAO3.6. Below is the code I use,
but whenever I encounter an Access 2007 (accdb) it fails at the first line
("3343: Unrecognized database format"). Is there any updates for DAO or any
other way I can gather some basic database information from the file?
Thanks!

Set db = DAO.OpenDatabase(MyFilePathname, False, True)
sver = db.Properties("AccessVersion")
Click to expand...

The problem is what if Access 2007 isn't installed on the system
running your exe?

I used DAO as a late binding object starting with DAO version 12.0,
then DAO version 3.6 A2000-A2003, 3.5 - A97, 3.0 - A95 and 2.0 - A2.0.

(I didn't bother with Access 1.0 and 1.1 and that hasn't been a
problem. Now you laugh but I've now had one request for a bug in the
AutoFEUpdater regarding A2.0 and one or two email discussions
regarding A2.0.)

If the open request hit an error 429 then I dropped down a version
until either it worked or it dropped off the end. At which point my
error message stated that they likely need to register DAO.

The key VB 6 code is as follows.

Set objDAO = CreateObject("DAO.DBengine." & strDAOVersion)

Set wrk = objDAO.CreateWorkspace("AutoFEUpdater", "Admin",
vbNullString, dbUseJet)
On Error Resume Next
Set db = wrk.OpenDatabase(strPathAndFileofMDB, True, True)
If Err.Number <> 0 Then
lngErrNumber = Err.Number
strErrorDesc = Err.Description
End If

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
N

Nando

Thanks Tony, I'm currently using something similar. But I don't have a
problem with that. The problem I have is that I cannot do any kind of
analysis with an Access 2007 file. As you I'm using DAO, but it comes up
with "unrecognized database format" error.

I wish I have either a way I can analyze them all (2007 and older) or a way
I can test if the file is 2007. Given the last case I would have to check
for that condition first, and if it is false, then I'll have to check the
file for an older format using my old code I guess.
 
N

Nando

Thanks Allen, everything works fine for anything but Access 2007 files. I'm
using VB6 for my EXE (and DAO36). It seems like DAO36 is fully incompatible.
I need a way to read Access files 2007 and older, or a way to just read 2007
(in that case I'll have to check for this condition first, and if it is
false then I'll have to use the old code to see if is older than 2003). But
there must be a way I can read (even with limited functionality) a file in
Access 2007 format.
 
A

Allen Browne

Correct: DAO 3.6 is not compatible with the ACCDB file format.

You will need the link that '69 Camaro gave you.
 
N

Nando

Allen Browne said:
Correct: DAO 3.6 is not compatible with the ACCDB file format.

You will need the link that '69 Camaro gave you.
Click to expand...

Yes, but that's what I'm saying. Even after I downloaded and installed it,
my code still doesn't work.
 
A

Allen Browne

Did you open your VB project, and include the new reference?

I'm not using VB, so I haven't actually done this, but you will need to
reference the new library, not DAO 3.6.

The file Access 2007 uses is typically:
C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE12\ACEDAO.DLL
 
N

Nando

Thanks Allen! Good! I'm following you. I had to browse for the reference.
The file is:
"C:\Program Files\Common Files\Microsoft Shared\OFFICE12\ACEDAO.DLL"
and the library name is:
"Microsoft Office 12.0 Access Database Engine Object Library"

The code now runs, but just one problem: I downloaded an Access 2007
database sample (.accdb) from the Web to test my code (function). However,
after using the following statement, I get returned "09.50" which is Access
XP. But that's wrong!
Below is what I'm using (it has always worked for 2003 and below). Any ideas
on what I'm doing wrong:

Dim db As DAO.Database
Dim sver As String
Set db = DAO.OpenDatabase(accessfile, False, True)

sver = db.Properties("AccessVersion")
Select Case Left(sver, 2)
Case "07": GetAccessFormatVer = 97
Case "08": GetAccessFormatVer = 2000
Case "09": GetAccessFormatVer = 2002
Case "10": GetAccessFormatVer = 2003
Case "11": GetAccessFormatVer = 2007
Case Else: GetAccessFormatVer = -2
End Select
 
T

Tony Toews [MVP]

Allen Browne said:
Correct: DAO 3.6 is not compatible with the ACCDB file format.
Agreed.

You will need the link that '69 Camaro gave you.
Click to expand...

Not if you use late binding on the DAO reference. Although granted if
you don't have Access 2007 installed on your system it won't be able
to determine the version number of A2007 accdbs.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

Nando said:
Thanks Tony, I'm currently using something similar. But I don't have a
problem with that. The problem I have is that I cannot do any kind of
analysis with an Access 2007 file. As you I'm using DAO, but it comes up
with "unrecognized database format" error.

I wish I have either a way I can analyze them all (2007 and older) or a way
I can test if the file is 2007. Given the last case I would have to check
for that condition first, and if it is false, then I'll have to check the
file for an older format using my old code I guess.
Click to expand...

What I hadn't made clear in my posting is that you do need to use the
ACEDAO.DLL as Allen had mentioned so you've got things solved anyhow.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help - Access 2007 8
Looking for C++DAO Direct/ Acedao samples to work with Access 2007 0
mdb format and Access 2007 3
Export from excel to Access 3
unrecognized database format 1
Convert to Access 2007 file format? 1
Image control in Access 2003 adp under Access 2007... 2
DAO references / Access 2007 2

Top