System Tables, Mixing Versions of Jet

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Dec-2000 >

System Tables, Mixing Versions of Jet

Peter Vogel        

Peter Vogel answers questions about retrieving table names with queries, using two versions of Jet on the same computer, giving users a File Open dialog box, and taking advantage of Access's version of SQL.

 

I'm trying to retrieve the names of tables from an Access 97 database, but I need to do this in a query and not with ADO. Can this be done?

There is, indeed, a way to do this. If you haven't already, select the Tools | Options menu and click the System Tables option in the Show box on the View tab. When you return to your database window, you should see a bunch of tables whose names are prefixed with "MSys" (Microsoft System) . The MSysObjects table lists all of the tables in the database, along with every other object in the database. You'll want to retrieve the Name field in the table for records where the Type property is equal to 1:

Select Name
From MSysObjects
Where Type = 1
 

I should warn you that this list will include the MSysObjects tables also, which you'll probably want to skip when you process the list.

Can you have both the 3.5 and the 4.0 Jet engines installed on the same computer?
I've installed both versions on several computers and they work fine. I'm typing this on my laptop, for instance, which has both Access 97 and Access 2000 installed on it, and I switch between the two of them frequently. When writing programs that use DAO, you can check off either of the two versions in your References list, depending on which one you want to use. In my case, thanks to having had older versions of Access installed, I have four different versions of DAO/Jet installed. When using ADO code, the version of Jet/DAO that you use is controlled by whether you select the Jet 4.0 or the Jet 3.5 provider. For Jet 4.0, use the following:

Provider=Microsoft.Jet.OLEDB.4.0
 

For Jet 3.5, use this:
 

Provider=Microsoft.Jet.OLEDB.3.51
 

The only real limitation in selecting between different versions of Jet/DAO is that when running Access forms, reports, or queries, the version that's used is controlled by the version of Access that you're using.

I'm trying to create a File Open dialog box in Access. I know Visual Basic has the CommonDialog control and Excel has application.dialogs(). Is there such a thing in Access, or do I have to create a form from scratch?

Before I'd create a custom form, I'd use the CommonDialog control, especially if you're already familiar with it. To get to the CommonDialog control in Access, click on the builder button at the bottom of the Toolbox when working in design mode on your form. That will give you a list of every ActiveX control on your system. Be patient, as it can sometimes take a few seconds to build this list. Scroll down the list to the Microsoft Common Dialog Control, click on it to select it, and then draw it on your form. Access creates an OLE container and stashes your control in it. The container will have the name ActiveXCtln (where n is some number).

Don't be alarmed when you write code with this control and don't see the familiar methods and properties in the IntelliSense dropdown lists. The methods and properties that appear in IntelliSense and the Property List are the ones for the OLE container that's holding your CommonDialog control. You can get at the CommonDialog control through the container's Object property. Visual Basic actually does very much the same thing, but it wraps the control in something called the Extender object. In Visual Basic, the control's methods and properties are merged with the Extender's methods and properties on the Property List and in IntelliSense. Access, unfortunately, doesn't perform the same service.

In order to get the benefits of IntelliSense with Access, use the following code, which also gives you early binding so everything should run faster, too:

Dim cmd As CommonDialog
Set cmd = ActiveXCtln.Object
cmd.ShowOpen
 

Here is the link for the SQL for a Survey part of this Access Answers article