How to run 32-bit UDL file on a 64-bit Operating System


We’ll need to  execute the command below from a command line or Start/Run :


C:\Windows\syswow64\rundll32.exe “C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll”,OpenDSLFile C:\test.udl


Check the paths of rundll32.exe and oledb32.dll while running this command!


Here you have your 32 bit OLE DB Providers.

MS Access 2010 Store Password DSNs


Hidden Tables in Your Microsoft Access Database

Any sophisticated piece of software like Microsoft Access will have many hidden and internal files which are archived to maintain the application’s environment and user customisations. In Access, we are talking about System Objects; in particular theMSysObjects Table which is a hidden table that can be used to extract information about your Access database.

Microsoft Access System Objects consist of several tables that gives you important and useful information about your database application. In this post, I’m going to focus on MSysObjects Table. It contains a list of all object types, when it was created, last updated and other related ID’s which link to other system tables.

A health warning should be included here. DO NOT attempt to delete or modify this table, its records (if you can)! It will potentially kill the system and corrupt a lot more than you may think.

First of all, you need to show the hidden system tables but ticking the ‘Show System Objects‘ option checkbox control which is found in different locations based on the version of Microsoft Access you use.

In Access 2003 (and earlier), you need to go to the ‘Options…‘ command via the ‘Tools‘ menu and click on the ‘View‘ tab.

In later versions, this feature is found from the ‘Navigation Options…‘ of the Navigation Pane (by right mouse clicking the header).

Microsoft Access 2010 Navigation Pane - System ObjectsOne of the key fields in this table is the ‘Type‘ field which has a unique value for each type of object and can be used to interrogate and extract recordsets. An example may well be to list tables in the current database to populate a combo-box control on a form or to list queries in a list-box control for a form.

MSysObjects.Type field has the following values representing the following objects:

Tables 1
Linked Tables 6
Forms -32768
Queries 5
Reports -32764
Modules -32761
Macros -32766
Relationships 8

Don’t ask me why the values assigned are not obvious – just accept it!

You use system tables to create additionally functionality for your Access database and help manage user controlled environments that otherwise could be exposed to all users and make your database un-user-friendly.

For example, I want to view all forms in a combo-box so that the chosen item can be used as a parameter setting for the default loading of a form (very much like setting the ‘Start-Up’ of a selected form in Microsoft Access).

SELECT MSysObjects.Name FROM MSysObjects
WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=-32768
ORDER BY MSysObjects.Name;

Note the MSysObjects.Name is also set in the WHERE clause to exclude the ~ (tilde) as this refers to internal settings namely indexes and other non essential references for our purpose.

Copy the above SQL statement to either the combo-box control (or save it first as a new query) and then you can start to add functionality to your form.

There are other system objects which will also be visible. Don’t forget to hide these tables when done though in a polished database application you will probably have hidden the ‘backstage‘ view inclusing the Navigation Pane.

I welcome your comments in the form below. Keep up to date with Access Database Tutorial free weekly tips loated at the top (right) of this page.


Power Tip: Improve the security of database connections

This post about making ODBC connections more secure is provided by Access MVP Ben Clothier.

Now and then, we come to a point where we decide to upsize an Access database to a server-based RDBMS. The reasons for upsizing can vary, but a common theme is security. With a server-based RDBMS, we have more options for security than when we are working with a file-based data source. Even if security wasn’t the reason for upsizing, we do have an obligation to ensure that we develop a “good citizen” application and protect the company’s assets contained in the RDBMS. When our RDBMS supports Windows authentication (e.g., SQL Server, Oracle, FireBird), we have it easy – no username or password needs to be stored. But unfortunately, we don’t always have Windows authentication available, and even when it is supported, it may not be practical. This article focuses on the case where we have to pass in a username and password as a part of the connection string and we want to do so in a secure manner. The article also assumes we are using ODBC and DAO.

Holes in Data Source Names

By default, Access offers to set up a Data Source Name (DSN) when we want to create a new linked table. As a matter of development, the DSN is quite convenient, giving us a graphic method to quickly build and specify an ODBC connection string without having to remember all of the syntax and parameters. Unfortunately, when the time comes for deployment, using DSNs has a host of problems, especially security. Let’s quickly review a few of the security holes associated with using DSNs.

Save password check box in Link Tables dialog box
Figure 1

When we link a new ODBC table, Access defaults to not saving the password when we select a DSN. We have to select Save Password to do so, as seen in Figure 1. Recent versions of Access warn that the password will be saved as plaintext. If we ignore the warning and save the password anyway, the password is saved in the Access system table, MSysObjects, as plaintext. You can see an example of this in Figure 2.

Passwords saved as plain text in the MSysObjects table
Figure 2

Not saving passwords is not good enough

So, let’s elect not to save the password. Does that resolve the problem? Unfortunately, not necessarily. When we create a DSN, the data used to build connection strings is stored in a registry entry. Let’s see how the DSN for a MySQL database is structured. You can find it in the registry node shown in Figure 3.

Password saved as plain text in the system registry
Figure 3

Here, the password is stored in the registry as plaintext, so even though we didn’t check the Save Password option, and our Connect wouldn’t list Password anywhere in the database itself, it’s still there for the taking. Yikes. Even worse, storing passwords is a vendor-specific implementation. The PostgreSQL ODBC driver also stores passwords as plaintext, but the Firebird ODBC driver always encrypts the password. SQL Server ODBC drivers will never try to store a SQL Server authentication password, no matter what. Considering the large number of different ODBC drivers, including third-party drivers for the same RDBMS, I’d rather not have to track the specific oddities of each ODBC driver and accommodate each one of them as I move from RDBMS to RDBMS.

We can say, “Let’s not define the password in the DSN, requiring the user to complete the connection at runtime.” Indeed, we can do that, but we now run into a new problem that also plagues the SQL Server ODBC driver. Every time a user opens an Access object that either is, or depends on, an ODBC object, the user will be prompted to enter their password for that ODBC connection. This does not make for a great user experience, not to mention several additional issues raised by showing users the dialog box for configuring the ODBC driver, which exposes options you may not want users to tinker with.

Using DSN-less connection instead

So in short, using DSNs may be convenient, but it can be problematic to implement in a secure manner. Moreover, because the specifics of implementation vary by ODBC driver, there is no good general solution for designing a secure DSN. Therefore, we should consider DSN-less connection strings. Though DSN-less connections per se are not necessarily more secure, the additional security comes from the fact that they can be disposable, which becomes very important, as we’ll see shortly.

Access MVP Douglas J. Steele has posted a sample of building a DSN-less connection in VBA for a SQL Server backend. Doug recently updated the material, incorporating modifications suggested by George Hepworth, another Access MVP, to support both trusted connections and SQL Server authentication. This document provides us an excellent starting point to learn how we can build DSN-less connections. We’ll also learn how we can ensure that all linked tables will be updatable by adding a unique index local to Access.

Cached connection

There is an interesting behavior in Access we want to take advantage of. When Access opens an ODBC connection, it caches that connection. Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection. This means we don’t have to specify the full connection string for all ODBC objects each time. We only need to supply the complete connection string once at startup and store only the incomplete connection string. We can then leave it up to Access to match subsequent ODBC objects to that cached connection string. This helps immensely in simplifying the security setup.

During application startup, we want to call a routine that will create a temporary query that contains the complete connection. Then we can discard that query at end of the routine. Procedure InitConnect demonstrates this crucial step.

Public Function InitConnect(UserName As String, Password As String) As Boolean
‘ Description:  Should be called in the application’s startup
‘               to ensure that Access has a cached connection
‘               for all other ODBC objects’ use.
On Error GoTo ErrHandler

Dim dbCurrent As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

‘<configuration specific to MySQL ODBC driver>

strConnection = “ODBC;DRIVER={MySQL ODBC 5.1 Driver};” & _
“Server=” & ServerAddress & “;” & _
“Port=” & PortNum & “;” & _
“Option=” & Opt & “;” & _  ‘MySql-specific configuration
“Stmt=;” & _
“Database=” & DbName & “;”

Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef(“”)

With qdf
.Connect = strConnection & _
“Uid=” & UserName & “;” & _
“Pwd=” & Password
Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True

On Error Resume Next
Set rst = Nothing
Set qdf = Nothing
Set dbCurrent = Nothing
Exit Function
InitConnect = False
MsgBox Err.Description & ” (” & Err.Number & “) encountered”, _
vbOKOnly + vbCritical, “InitConnect”
Resume ExitProcedure
End Function

Even though we discard the query at the end of the procedure, Access holds onto the connection it created behind the scenes. As far as I know, there is no programmatic access to this cached connection.  However, once it exists, you can open any other ODBC objects that match on driver, server, and database parameters and interact with it as if it did have the complete connection string. Figure 4 shows a table opened without any additional prompt after we run the procedure at startup. Even though it does not have the complete connection string, Access used the cached connection created by InitConnect so there’s no need to prompt the user for missing information.

Linked table with connect string displayed as a tooltip
Figure 4

With this technique, you can now implement a custom login form to have users enter their username and credentials at runtime and call the InitConnect procedure. That information is passed to the code snippet illustrated above to create the connection. You now have an application that does not store the password anywhere, inside or outside the file. Although that won’t stop your users from penciling their password on a note taped to their monitor, you can be confident that, if the file was copied and taken outside the building, it would be useless; more so if the connection requires being within a certain network. (Certain IP addresses such as 192.168.*.* are not world-accessible and require a physical connection to a particular DHCP server or at least VPN or similar tunneling software.)

Passthrough queries have a connection string, too

Property Sheet for a query, displaying the ODBC Connect Str property

Figure 5

As Figure 5 shows, we should remember that linked tables aren’t the only objects to have a Connect property –passthrough queries can have a Connect property that also needs to be maintained. Fortunately, those queries can also share the cached connection so there should be no reason for us to store a complete connection string for them. We only need to ensure that all passthrough queries have the minimum of three required parameters and we’re done.

Closing one more hole

There is a hole we need to close – the cached connection does not close when we close the database but do not quit Access. In other words, if a user closed your application and opened their own databases, they wouldn’t be prevented from accessing the same ODBC objects using the cached connection. Fortunately, that is fairly easy to remedy – when the last form closes, forcing Access to quit will usually ensure that the cached connection is properly disposed of at the end of your application’s session. This guarantees that users will be always required to explicitly log in when they open Access and attempt to access the ODBC sources.

When you’ve implemented an incomplete connection string, the file no longer can be simply copied around. If someone attempts to bypass the login routine, the tables and queries will not be able to connect. Double-clicking on those objects would simply give them the ODBC driver’s dialog box asking them to complete the connection. Because the password isn’t stored anywhere, they can’t just look under the doormat. Though encrypting the password is an option, it’s much more preferable not to store any password and require the user to supply it just in time. Encrypting the password and storing it in the file is analogous to putting your confidential information in a small safe and leaving it out in your front yard. Anybody can just take your small safe somewhere else and take as much time as they want to crack the password. True security comes in not having anything available for taking and blocking access rather than making access harder.

Extra reading

Note: If we create a File DSN instead of a System or User DSN, the data will be stored in a file rather than in a registry entry. However, File DSNs still store the data in plaintext, similar to what we saw in the registry. So the concerns for security apply equally to File DSNs and System/User DSNs.

–Ben Clothier

Ben Clothier has been an Access MVP since 2009. He is an independent contractor working with J Street Technology and Advisicon and was Technical Editor for the Access 2010 Programmer’s Reference.