The Microsoft Access (Jet) Database Provider

Team-Fly team-fly    

ADO.NET Programming in Visual Basic .NET
By Steve  Holzner, Bob  Howell

Table of Contents
Chapter 5.   Connecting to Databases

The Microsoft OLEDB provider for Jet Databases (4.0) has another complex Connection string. Just look at this sample from the Northwind sample database.

 Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data  Source=C:\Program Files\Microsoft  Office\Office\Samples\Northwind.mdb;Mode=Share Deny None;Extended  Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry  Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet  OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet  OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet  OLEDB:Create System Database=false;Jet OLEDB:Encrypt Database=false;Jet  OLEDB:Don't Copy Locale on Compact=false;Jet OLEDB:Compact Without  Replica Repair=false;Jet OLEDB:SFP=false 

If you are at all familiar with Microsoft Access, then you will see a parallel with the Access command-line startup options. There are just too many properties to go over completely. I will list them and give a brief meaning but it will be up to you to use the Access documentation for the full explanation.

  1. Provider The name of the OLE DB providerMicrosoft.Jet .OLEDB.4.0. This provider will open all databases up to Access XP. It was written for the Access 2000 format, but works with the XP format as well. (I believe they are the same.) There is also a provider for the Access 97 database format named Microsoft.Jet.OLEDB.3.51. It is similar but has fewer properties. I'm sure they will open Access 95 format (3.0) as well. I haven't tested if any of these can be used with the old 16-bit versions of Jet (1.0 and 2.0) databases that were written for Windows 3.1.

  2. User ID and password These mean what they are. Jet databases can be configured to be secure or open. By default they are open. Even when they are open, they still use a default user ID of Admin with a blank password. In order for a Jet database to be secure, you must also specify a workgroup database name.

  3. Data source For Jet databases this is the path to the .mdb file.

  4. Jet.OLEDB:System database This is the name of the Workgroup database. Normally this defaults to system.mda. If you are opening a secure Jet database you will have to supply the path to the system database (.mdw file) that you created.

  5. Jet OLEDB:Engine type The version of the database engine you are using.

  6. Jet OLEDB:Locking mode Setting for how to control concurrency. The choices are page locking or row locking. Row locking is the default.

  7. Jet OLEDB:Registry path An option alternate registry path for Jet to get its settings from. When writing Access applications, often it is desirable to have your secured application use one set of settings, while adhoc Access use by the user is not affected. This setting will point Jet to an alternate location than the default for getting its start up settings.

The other settings are either not implemented or esoteric. In most cases, all you need are the four main ones (provider, Data Source, user ID, and password). When opening a secured database you also need the path to the system database (.mdw file).

Connection Pooling

Connection pooling is the process by which the Connection classes conserve resources by maintaining a pool of open connections from the workstation to the server. As an example, suppose we have an application that opens a connection, retrieves data, and closes the connection. Then, a second later it wants to open the connection again. The Connection class will reuse the first connection. This is true as long as the Connection strings are identical. It will do this even from different applications as long as the Connection strings are identical. You can force the Connection class to release any pooled connections by called the ReleaseObjectPool method of the OleDbConnection class. All current connections must first be closed and then you must wait for the garbage collector to come around, but at least you have some control over when to release the connection. The SqlConnection class manages Connection pooling for you and there is no need to programmatically release connections.

Determining the State of a Connection

One of the new features of the Connection classes is the implicit open. This means that the Connection class will open a database connection when it is needed. It is not necessary in most cases to call the Open method before attempting a database access. When the database operation is complete, the Connection class will close the connection. At any point in your program, you have no way of knowing whether or not the connection is open. The Connection classes provide a property for this purpose, called the State property. You can test the State property to see if the connection is open. The State property can have value corresponding to the System.Data.ConnectionState enumeration:

  • Closed The connection is currently closed.

  • Open The connection is currently open.

  • Connecting The Connection object has sent the Connection string to the server, but the server has not yet responded.

  • Executing A Command object is executing a command using this connection.

  • Fetching A Data Reader is using this connection to get a stream of data from the server.

  • Broken The connection was severed unexpectedly, either by the server or due to a system exception.

Team-Fly team-fly    

ADO. NET Programming in Visual Basic. NET
ADO.NET Programming in Visual Basic .NET (2nd Edition)
ISBN: 0131018817
EAN: 2147483647
Year: 2005
Pages: 123

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: