The Old Joel on Software Forum: Part 3 (of 5) - Guide to building JET powered windows apps?

Guide to building JET powered windows apps?

Hey guys,

I'm currently playing around with windows application development for the first time, you see I'm one of those UNIX programmers. Having picked up the trial edition of Visual Studio.net I'm pretty impressed with what I've got, however while the IDE is pretty sweet, there don't seem to be any good documents available on building, bundling and installing an actual desktop application.

I'm really impressed by the CityDesk idea of using Jet as a file format. It strikes me that I'll be able to apply all of my transactional experience to a desktop application, plus I won't have to develop file formats with nasty parsers, encoding bugs etc. etc.

However, all the example applications & documents talk about working with a pre-existing database, configuring visual studio to access it and dragging and dropping tables & adapters onto the canvas. That doesn't really apply in this situation as I want to create the database when required, change the connection to alternative files when they choose 'open', etc. etc.

Does anyone know of a decent resource for this type of information? Specifically about building, bundling and installing desktop-jet-powered applications with visual studio? I'm after a book or a set of articles online?

Cheers

Koz

Michael Koziarski
Monday, December 15, 2003

Try this link:

http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate_topic6.asp?FRAME=true#daotoadoupdate_topic6a

You will want to read up on DAO or ADO depening on which you intend to use. It is also common practice to create a database using MS-Access and include it in the application instead of dynamically creating one. Searching on MSDN will yield a wealth of information on the subject.

Or if you have the MSDN CD (2001) you can check out:

/Platform SDK Documentation/Data Services/Microsoft Data Access Components (MDAC) SDK/Microsoft ActiveX Data Objects (ADO)

"Bundling" these components with an application is also fairly straightforward especially when using a RAD tool like VB. Your installation program could do a full MDAC install or you can simply install the DLL's you need. If i'm not mistaken the Jet engine is shipped with new OS's.

Monday, December 15, 2003

"JET powered windows apps," makes me think of Wile E. Coyote.

http://www.alexross.com/CJ063.jpg

SG
Monday, December 15, 2003

http://peach.ease.lsoft.com/scripts/wa.exe?A0=access-l

Monday, December 15, 2003

That is an excellent Access listserv

Mike
Monday, December 15, 2003

The common practice for apps using Access databases as their file format is to embed an "empty" database in their resources, or keep it hanging around on the file system, and then use that as a starting point. Your definition of empty could include fully hydrated tables and initial data, or it could be a truly empty file that you apply a "genesis" step to, to create all the tables and data needed.

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, December 16, 2003

DAO is defunkt. ADO is the way to go now.

Teller Coates
Tuesday, December 16, 2003

DAO may be "defunkt" [sic], but since in the COM universe you need either it or JRO to compact your database anyway (not to mention changing the schema), I've actually been engaged for the past week in switching all data access code in a VB6 app to DAO instead of ADO.

(Actually, that's not the only reason I'm dumping ADO; it's also to make my install lighter by ~5MB and not require a reboot, since ease of installation was the biggest complaint about the prior version. But I digress. And isn't it all Fire And Motion anyway?)

Also, doesn't using either DAO or ADO from inside .NET impose a performance penalty due to COM interop? (I haven't worked with .NET much, so if this is wrong, please let me know.) ADO.NET should be capable of retrieving data from a .MDB natively, as long as you're not manipulating structure.

And yes, Koz, the typical practice is to design the .MDB itself using the full version of Access. You could theoretically build it with DAO code, but it would be really annoying.

Sam Livingston-Gray
Tuesday, December 16, 2003

There are lots of db work that you can only do through ado and not through the access GUI.

E.g. check constraints on tables/table invariants can only be set through oledb.

So you may want to create your access db with a script rather than the GUI. It is more repeatbable anyway.

Karel
Tuesday, December 16, 2003

Koz,

If you're getting stymied with JET, there are a couple of other good formats available suitable for this kind of application. SQLite is nice and gives you the illusion of SQL access. Berkeley DB is also available in its many incarnations for Windows. Both of these libraries make file creation easy. There are, of course, certain tradeoffs.

Clay Dowling
Tuesday, December 16, 2003

If you want more than JET, you should check out MSDE which is a slightly crippled version of MS SQL Server. It's also freely distributed and uses the same interface as MS Access/JET making the switch easy. It has some drawbacks since there is a real server running, but it may be the way to go if you find that Access is no longer cutting it.

pdq
Tuesday, December 16, 2003

I can recommend ADOX for creating the database at runtime; it works very well. In addition to the MSDN documentation, this FAQ has some useful tips:

http://www.able-consulting.com/ADOX_Faq.htm

The tips are VB-oriented but easily applicable to C++, too (which is what I'm using).

A big advantage to using ADOX over pregenerating the database with Access is that you can use it to modify an existing database's schema. I'm using this capability to auto-upgrade users' databases when I distribute a new version of my application, since newer versions typically update the database schema.

Every version of Windows since Windows 98 SE supports Jet out-of-the-box. If you feel you must support the original Windows 98 as well (which I do) then you'll need to bundle MDAC with your application. You can find it at:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

As previously mentioned, you need to use JRO to compact the database. This is quite independent from your choice of data access library (ADO or DAO), and takes just three lines. Well, that and 100 lines for error checking...

And finally, regarding MSDE: if you can afford the huge installation hit then it's certainly desirable. The only advantage of Jet over MSDE is that installation is simpler and smaller. Microsoft have been trying to get people to stop using Jet for quite a while now, but obviously they aren't having much luck.

Oren
Wednesday, December 17, 2003