Jet Database Engine Questions

[eselk]

What is the lowest level I can use to access the Jet Database Engine?
I know I can use ADO or DAO. Are there any COM objects that
msjet40.dll exposes that I can use directly, or something else of that
nature? I've been doing some speed testing using Access 2000 and was
very impressed. I really like the data format, indexes, etc, that it
uses. I haven't been able to get similar speed when using ADO or DAO,
not the same speed I get when doing things via the Access GUI (like
doing an update query of 100,000 records, it is so much faster from
the Access GUI).

But that isn't the only issue, the main thing I want to be able to do
is use the Jet Database Engine *without* having to add stuff to the
system/common areas of the registry, like class IDs, program IDs,
etc. I'd like to see, mostly just as an experiment if nothing else,
what it would take to be able to just include a few DLLs (like
msjet40.dll) in my application folder, and then access these DLLs
directly in order to read/write MDB files.

My boss would never go for anything that could be broken by a user
installing some other application that updates the "system" copy of
the Jet Database Engine, or having to depend on a certain version of
Jet or MDAC being installed. Everything we do has to be fully
contained in the application folder and can't use any "shared
resources", seriously, pretty much nothing... although I do get away
with using kernel32.dll and user32.dll from the system folder -- thank
god =] You should have been there today when I approached him about
using .NET, you would have thought the world was coming to an end.

p.s.-I was kind of hoping to find a "Native Jet API" on MSDN, like
I've seen for other database libraries/engines... but maybe there is
no "native API" for it? Something like the Win32 API would be great,
but for Jet =] (am I the only one using Win32 API still?)

 

[eselk]

What is the lowest level I can use to access the Jet Database Engine?
I know I can use ADO or DAO. Are there any COM objects that
msjet40.dll exposes that I can use directly, or something else of that
nature?

I just found "ESE" on MSDN:

http://msdn2.microsoft.com/en-us/library/ms684493.aspx

I guess it is the new "JET Blue", and Access/DAO use "JET Red", but I
love the fact that it has a C-based API, like the Win32 API. This
could be exactly what I need.

Anyone here used it? Any comments on how it stacks up against Access/
DAO (JET Red) for performance? It looks like it uses a very similar
file format, if not identical, with the single file and the super-cool
indexing options.

 

[Tony Toews [MVP]]

I know I can use ADO or DAO. Are there any COM objects that
msjet40.dll exposes that I can use directly, or something else of that
nature?

To my knowledge no.

I haven't been able to get similar speed when using ADO or DAO,
not the same speed I get when doing things via the Access GUI (like
doing an update query of 100,000 records, it is so much faster from
the Access GUI).

Interesting. That's one timing test I've never done.

But that isn't the only issue, the main thing I want to be able to do
is use the Jet Database Engine *without* having to add stuff to the
system/common areas of the registry, like class IDs, program IDs,
etc. I'd like to see, mostly just as an experiment if nothing else,
what it would take to be able to just include a few DLLs (like
msjet40.dll) in my application folder, and then access these DLLs
directly in order to read/write MDB files.

Windows XP includes Jet 4.0 in the operating system.

My boss would never go for anything that could be broken by a user
installing some other application that updates the "system" copy of
the Jet Database Engine, or having to depend on a certain version of
Jet or MDAC being installed.

I can't think of a database system that doesn't update DLLs, have
different versions of DLLs and such. Thus I'd suggest writing your
own database system using CSV files.

Now that comment is somewhat tongue in cheek. However your boss has
some serious delusions if he thinks that is possible.

That said DAO and Jet are stable and mature. I just checked on a
WIndows XP Virtual PC system which has never had any software
installed on it other than the MS Updates and anti virus. msjet40.dll
is in the \windows\system32 directory and is at version 4.0.8905.0
dated 2004-08-04. Also Microsoft/Windows Update does update that
dll.

As far as I'm concerned that's stable enough for me.

Everything we do has to be fully
contained in the application folder and can't use any "shared
resources", seriously, pretty much nothing...

Write your own database system using csv files then.

although I do get away
with using kernel32.dll and user32.dll from the system folder -- thank
god =] You should have been there today when I approached him about
using .NET, you would have thought the world was coming to an end.

No, time to change jobs.

Something like the Win32 API would be great,
but for Jet =] (am I the only one using Win32 API still?)

Not at all. I use a lot of API calls with my apps. My Auto FE
Updater, in VB6.0, is nothing but a lot of API calls with some glue
code in between. <smile>

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/

 

[Albert D. Kallal]

As Tony mentioned, the jet database engine to ship with Windows XP, and also
vista .

What this means is that you can use the jet engine, and not have to use or
install any other software except you're own stuff. What this means is you
can even use windows scripting, to execute SQL and read mdb files. Thus jet
is available for your use without any install at all on a windows box.

By the way, I've not seen any performance differences when using dao
directly, or from within MS access.

Do be aware there is also a standalone inbeeded version of SQL server that
is available for use with your applications. It's also a free download. This
is not the desktop edition of SQL server, it is a step below that, and does
NOT run as a service, and it is NOT multiuser. I've not tested the speed of
this new data engine from Microsoft, but it should perform well.

 

[eselk]

As Tony mentioned, the jet database engine to ship with Windows XP, and also
vista .

Sweet! That's the best news I've heard all night.. as long as I
understand correctly. I've just now learned that the lowest level API
to JET (Red) is DAO. So I assume DAO is also shipped with XP and
Vista? If not, I guess JET by itself really isn't usefull at all,
since it needs an API of course =]

What this means is that you can use the jet engine, and not have to use or
install any other software except you're own stuff. What this means is you
can even use windows scripting, to execute SQL and read mdb files. Thus jet
is available for your use without any install at all on a windows box.

Since scripting probably means COM objects, I'm getting pretty
excited, since that means DAO is also included.

By the way, I've not seen any performance differences when using dao
directly, or from within MS access.

Actually I probably miss-spoke on the DAO part. I have done testing
with ADO, but now that I think about it, the only DAO testing I've
done had a couple extra layers on top (3rd party libraries) so that
can't really be a fair test.

Do be aware there is also a standalone inbeeded version of SQL server that
is available for use with your applications. It's also a free download. This
is not the desktop edition of SQL server, it is a step below that, and does
NOT run as a service, and it is NOT multiuser. I've not tested the speed of
this new data engine from Microsoft, but it should perform well.

Yah, at first I was really excited when I learned about that one,
"MSCE"... until I found out it doesn't support multiples PCs accessing
the database file at once. Of course that totally makes since, since
you can't have that type of access and still get certain features that
only true client/server can give you.

My target market used to include Win98, but I think I'm not so worried
about them anymore. You mentioned JET is included on XP and Vista,
how about 2000, 2003, and by any chance ME (for all 10 copies that
were sold)?

Also, I'd still be interested to know if anyone has successfully
installed a "private" copy of JET in their application folder. It
seems like the JET/ADO combo only needs 3 DLLS, dao360.dll,
msjet40.dll, and mswstr10.dll. I wonder if I could ship those 3 DLLs
with my application, that way I always know exactly what code is
running (what version is being used)... I think I'd rather be in
charge of doing the updates myself, that way I know if I really need
an update or not (since only I know what functions I use) and I know
every once and a while and update will actually break something (or
flush out an otherwise hidden "bug" in my code). Also, I wouldn't
need any of the numerous COM registry entries, because I could just
load my private copy of dao360.dll directly and call DllGetClassObject
to get the COM interface(s) from it. As long as I could get
dao360.dll to load my private copy of msjet40.dll, I think all would
be good. Probably if I load msjet40.dll BEFORE I load dao360.dll,
that would probably ensure that dao360.dll uses the already loaded
copy instead of getting the one from the system folder.

Thoughts on this? Plus, and I'm totally being a law breaker and would
Microsoft totally crush me because I can't redistribute these DLLs in
this manor? I know they are "free", but of course that doesn't always
mean they don't have restrictions on them.

I know your thinking this is crazy at this point... but this would
only be a "back-up" plan, just incase we do ever run in to issues with
certain versions. I'd need to at least be able to tell my boss that
we have this option -- something like, "worst case, if we get burned,
we ship these 3 DLLs (only 2.5MBs) and load them this way". I guess
one way we could "get burned", which no one can tell me 100% for sure
that it will not happen, is that Microsoft could not include JET with
the next version of Windows, and then also not include a way to get
them installed (like no "install MDAC" option). At least with just 3
DLLs that I ship, and I know at the lowest level they just do basic
file i/o (which I don't think MS will change anytime soon), then that
has to be as future-proof as you can get (short of having the source
code for those 3 DLLs -- I wish!).

 

[eselk]

I don't think I'll actually go this route, but I was able to copy
these DLLs to my application folder:

dao360.dll
msjet40.dll
mswstr10.dll
vbajet32.dll
expsrv.dll

As long as I load all of the DLLs from my app folder first, then call
DllGetClassObject directly on dao360.dll... after that I'm able to use
all of the DAO objects like DBEngine and OpenDatabase, and no DLLs
from the system/shared folders are used.

However, I bet there could be a lot of registry settings and things
that are still be used that I'm not aware of, and those settings may
not be compatible with a version of these DLLs that I ship with my
app.

It was a fun learning experience anyway =]

I'll be checking with the boss today, but as long as DAO and JET [Red]
are included on at least XP, 2003 and Vista, then I think he may be
okay going this route -- finally, I might get to use a real database
engine! Of course he still uses 98, so I'll have to sneak in to his
office and make sure JET is installed and working correctly so he can
run my proto-type app and get a good first impression.

 

[Tony Toews [MVP]]

I don't think I'll actually go this route,
Don't.

However, I bet there could be a lot of registry settings and things
that are still be used that I'm not aware of, and those settings may
not be compatible with a version of these DLLs that I ship with my
app.

Oh yes.

I'll be checking with the boss today, but as long as DAO and JET [Red]
are included on at least XP, 2003 and Vista, then I think he may be
okay going this route -- finally, I might get to use a real database
engine! Of course he still uses 98, so I'll have to sneak in to his
office and make sure JET is installed and working correctly so he can
run my proto-type app and get a good first impression.

msjet40.dll is present on a Windows 2003 Server system which has never
had Access installed on it.

The boss still uses Windows 98 a totally unsecure operating system?
Time to get a new job.

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/

 

[Tony Toews [MVP]]

My target market used to include Win98, but I think I'm not so worried
about them anymore. You mentioned JET is included on XP and Vista,
how about 2000, 2003, and by any chance ME (for all 10 copies that
were sold)?

Win 2003 Server yes Jet 4.0 is included. However you shouldn't have
any clients running that.

As far as ME goes well tough. Supporting a seven year old unsecure
operating system? Nah.

Win 2000. Just checking. Interesting. I didn't realize this but yes,
it's included in Win 2000 as well.

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/

 

[David W. Fenton]

(e-mail address removed) wrote in
:

Anyone here used it? Any comments on how it stacks up against
Access/ DAO (JET Red) for performance? It looks like it uses a
very similar file format, if not identical, with the single file
and the super-cool indexing options.

That's the Outlook PST format, and it's horridly inefficient.

 

[David W. Fenton]

(e-mail address removed) wrote in
:

You mentioned JET is included on XP and Vista,
how about 2000, 2003, and by any chance ME (for all 10 copies that
were sold)?

All NT-based versions of Windows since Windows 2000 have included
Jet 4 because Jet 4 is used as the data store for Active Directory.
Thus, all versions of Windows supporting Active Directory include
Jet 4. This means Win2K (Workstation and Server), WinXP and Win2K3
Server (Win2K3 Server could have been called WinXP Server, just like
with Win2K, as it was built on top of that codebase, though it was a
new kernel, whereas both versions of Win2K are kernel-identical).

Until MS implements its fabled database-based file system, Jet will
continue to be included. It's likely to be Jet 4, as Jet 5 (AKA
"ACE", the new data engine in Access 2007) is not maintained by the
Windows development team, whereas Jet 4 *is* maintained by them.

However, DAO is *not* included.

 

[David W. Fenton]

(e-mail address removed) wrote in
:

Also, I'd still be interested to know if anyone has successfully
installed a "private" copy of JET in their application folder. It
seems like the JET/ADO combo only needs 3 DLLS, dao360.dll,
msjet40.dll, and mswstr10.dll. I wonder if I could ship those 3
DLLs with my application

Buy MS development tools and you'll be able to *legally* include DAO
with your app.

 

[David W. Fenton]

Win 2000. Just checking. Interesting. I didn't realize this but
yes, it's included in Win 2000 as well.

The reason is because Jet 4 is the data store for Active Directory,
which was introduced in Windows 2000, and is included in all
subsequent versions of Windows. Jet 4 will remain in every version
of Windows that supports Active Directory until MS implements its
fabled database-based file system, at which time it's likely (if it
ever happens) Jet will be replaced by that SQL Server-based database
engine. *IF* it ever happens.

 

[eselk]

(e-mail address removed) wrote in:


Buy MS development tools and you'll be able to *legally* include DAO
with your app.

I've got MSVC, as well as an action-pack subscription and used to have
MSDN subscription, so obviously I don't have a problem paying for
stuff.

Thanks, and thanks to everyone else for your comments.

Also, just to clarify a bit our thinking on using system/shared DLLs.
We like to think our apps will be able to run on any PC that can run
Windows (the versions we support at least). One major way we can make
that happen is to only use components that fall in to 2 categories:

1. We have source code and full control over.

2. Windows can't run without them -- and if Windows doesn't work, they
can't install our software, so we don't have to worry about it.

So kernel32, user32, even oleaut32, are DLLs that are covered under
#2. I'm glad that JET [Red] and DAO are getting pretty close. I
don't know enough about Active Directory though, but I'm pretty sure
Windows in general (especially a non-server version) could run fine
even if AD wasn't working... so the first application that a user
installs that uses JET/DAO could very well be ours, so if any of those
DLLs were not the correct versions or any of the 100s of registry
settings were messed up --- our app doesn't run.

Keep in mind, so far the main sub-system component I've worked with is
MAPI.... and I guess that may be one of the worst when it comes to
other apps messing up the "shared" system resources (all those guys
out there replacing mapi32.dll with their own, instead of going
through the normal stub system, hence the "fixmapi.exe" utility that
Microsoft made). My boss mainly worked with the BDE (Borland) which
had all kinds of similar problems, so I see where he is coming from
also.

 

[James A. Fortune]

Thanks, and thanks to everyone else for your comments.

Also, just to clarify a bit our thinking on using system/shared DLLs.
We like to think our apps will be able to run on any PC that can run
Windows (the versions we support at least). One major way we can make
that happen is to only use components that fall in to 2 categories:

1. We have source code and full control over.

2. Windows can't run without them -- and if Windows doesn't work, they
can't install our software, so we don't have to worry about it.

So kernel32, user32, even oleaut32, are DLLs that are covered under
#2. I'm glad that JET [Red] and DAO are getting pretty close. I
don't know enough about Active Directory though, but I'm pretty sure
Windows in general (especially a non-server version) could run fine
even if AD wasn't working... so the first application that a user
installs that uses JET/DAO could very well be ours, so if any of those
DLLs were not the correct versions or any of the 100s of registry
settings were messed up --- our app doesn't run.

Here are some links that may be helpful (or maybe not):

How To: Redistributing DAO with Your Visual C++ 5.0 Application (Note:
This includes descriptions of some of the DAO and Jet core files.)

http://support.microsoft.com/kb/167523


Q&A: Microsoft ADT and Microsoft Jet Database Engine Licensing and
Solution Distribution

http://msdn.microsoft.com/archive/d...aseEngineLicensingandSolutionDistribution.asp


How To: Redistribute DAO 3.6

http://support.microsoft.com/kb/233002


Release strategy for Microsoft Data Access Components

http://support.microsoft.com/kb/892854


INFO: Deploy database applications with the Package and Deployment
Wizard (PDW)

http://support.microsoft.com/kb/213846


OLE DB/ADO: Making Universal Data Access a Reality

http://msdn2.microsoft.com/en-us/library/ms811450.aspx

James A. Fortune
(e-mail address removed)

 

[Tony Toews [MVP]]

So kernel32, user32, even oleaut32, are DLLs that are covered under
#2. I'm glad that JET [Red] and DAO are getting pretty close. I
don't know enough about Active Directory though, but I'm pretty sure
Windows in general (especially a non-server version) could run fine
even if AD wasn't working... so the first application that a user
installs that uses JET/DAO could very well be ours, so if any of those
DLLs were not the correct versions or any of the 100s of registry
settings were messed up --- our app doesn't run.

Sure, however, those Jet dlls are part of the OS and are updated by
Microsoft/Windows Update. I checked on clean systems running in
Virtual PC which have had no software installed on them other than an
antivirus program.

Those dlls via the Microsoft/Windows Update are the latest version.
So if anyone tries to install an older version it just won't install.

Keep in mind, so far the main sub-system component I've worked with is
MAPI.... and I guess that may be one of the worst when it comes to
other apps messing up the "shared" system resources (all those guys
out there replacing mapi32.dll with their own, instead of going
through the normal stub system, hence the "fixmapi.exe" utility that
Microsoft made). My boss mainly worked with the BDE (Borland) which
had all kinds of similar problems, so I see where he is coming from
also.

I've seen the same kind of cr*p elsewhere too. Just exactly what
escapes me though.

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/

 

[Tony Toews [MVP]]

Here are some links that may be helpful (or maybe not):

How To: Redistributing DAO with Your Visual C++ 5.0 Application (Note:
This includes descriptions of some of the DAO and Jet core files.)
http://support.microsoft.com/kb/167523

Thanks. I thought the above one was interesting enough I decided to
create a blog entry on it.
http://msmvps.com/blogs/access/archive/2007/11/30/interesting-kb-article-on-dao-and-jet-dlls.aspx
I assume you don't mind my putting your name on there.

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/

 

[Guest]

Normally the Access GUI is slow compared to a DAO connection.

However, the conditions are often quite different: The
Access GUI will default to using a transaction, where
a DAO connection will not: The Access GUI will default
to using an Asynchronous Select where a DAO connection
will default to a Synchronous Select.

And the first thing that people sometimes miss is that the
Access GUI has an open connection. Anything that requires
making and closing connections will be slower.

(david)

 

[David W. Fenton]

(e-mail address removed) wrote in
m:

I
don't know enough about Active Directory though, but I'm pretty
sure Windows in general (especially a non-server version) could
run fine even if AD wasn't working

What part of "Jet is part of the OS because it's used for AD" is
unclear? It's *always* installed in all versions of Windows that
support AD, which means Windows 2000 on.

You can't install Windows without AD support.