How to use Jet and ODBCDirect with Microsoft DAO 2.5/3.51 Compatibility Library to access data sources
What is Microsoft DAO 2.5 3.5 Compatibility Library and Why Do You Need It?
- If you are working with Microsoft Access or Visual Basic for Applications (VBA), you may have encountered the term "Microsoft DAO 2.5 3.5 Compatibility Library" or simply "DAO". But what is it exactly, and why do you need it?
microsoft dao 2.5 3.5 compatibility library download
Download Zip: https://www.google.com/url?q=https%3A%2F%2Furluso.com%2F2ukWyG&sa=D&sntz=1&usg=AOvVaw0_RTUzK8HZ497BkDfrqC-C
DAO stands for Data Access Objects, which is an object-based interface that allows you to access data sources through VBA. With DAO, you can create, modify, query, and manipulate databases using VBA code.Microsoft DAO 2.5 3.5 Compatibility Library is a type library that provides backward compatibility for applications that were created in earlier versions of Access or VBA that use DAO. For example, if you have an old Access database that uses DAO 2.5 or 3.0, you can still open it in Access 2002 or later by using the compatibility library.However, using the compatibility library may cause some errors or issues due to the changes in the newer versions of DAO. Therefore, it is recommended that you update your DAO code to use the latest version of Microsoft DAO, which is 3.6. In this article, I will show you how to install Microsoft DAO 2.5 3.5 Compatibility Library on Windows 10, how to set a reference to it in VBA, how to update your DAO code to use the latest version of Microsoft DAO, and how to use Microsoft DAO 2.5 3.5 Compatibility Library to access SQL data sources.
How to Install Microsoft DAO 2.5 3.5 Compatibility Library on Windows 10?
Before you can use Microsoft DAO 2.5 3.5 Compatibility Library in your VBA projects, you need to install it on your Windows 10 computer. Here are the steps to do so:
Download the Microsoft Data Access Components (MDAC) 2.8 SP1 from this link. This is a package that contains various data access technologies, including Microsoft DAO 2.5 3.5 Compatibility Library.
Run the downloaded file (MDAC_TYP.EXE) and follow the instructions to install MDAC 2.8 SP1 on your computer.
Restart your computer after the installation is complete.
Verify that Microsoft DAO 2.5 3.5 Compatibility Library is installed by opening the Windows Explorer and navigating to C:\Program Files (x86)\Common Files\Microsoft Shared\DAO folder. You should see a file named DAO2535.TLB, which is the type library for Microsoft DAO 2.5 3.5 Compatibility Library.
If you have successfully installed Microsoft DAO 2.5 3.5 Compatibility Library, you can now use it in your VBA projects.
How to Set a Reference to Microsoft DAO 2.5 3.5 Compatibility Library in VBA?
To use Microsoft DAO 2.5 3.5 Compatibility Library in your VBA code, you need to set a reference to it in the code window. Here are the steps to do so:
Open the VBA editor by pressing Alt + F11 in your Access or Excel application.
Select the project that you want to use Microsoft DAO 2.5 3.5 Compatibility Library in from the Project Explorer window.
Click on Tools > References from the menu bar.
In the References dialog box, scroll down and find Microsoft DAO 2.5/3.51 Compatibility Library from the list of available references.
Check the box next to it and click OK.
You should see a reference to Microsoft DAO 2.5/3.51 Compatibility Library in the References window below the code window.
How to Update the DAO Code to Use the Latest Version of Microsoft DAO?
If you have existing VBA code that uses Microsoft DAO 2.5 or 3.0, you may encounter some errors or issues when you run it with Microsoft DAO 2.5 3.5 Compatibility Library. This is because some of the methods, properties, and constants of DAO have changed or been deprecated in the newer versions of DAO. Therefore, it is recommended that you update your DAO code to use the latest version of Microsoft DAO, which is 3.6. Here are some tips on how to do so:
Replace any references to DAO with references to DAO36 in your code. For example, change Dim db As Database to Dim db As DAO36.Database.
Replace any references to Jet with references to JetEngine or JetEngine36 in your code. For example, change Set db = DBEngine.OpenDatabase("C:\MyDB.mdb") to Set db = JetEngine36.OpenDatabase("C:\MyDB.mdb").
Replace any references to ODBCDirect with references to ODBCDirect36 in your code. For example, change Set db = DBEngine.OpenConnection("DSN=MyDSN") to Set db = ODBCDirect36.OpenConnection("DSN=MyDSN").
Replace any references to Recordset with references to Recordset36 in your code. For example, change Dim rs As Recordset to Dim rs As Recordset36.
Replace any references to QueryDef with references to QueryDef36 in your code. For example, change Dim qd As QueryDef to Dim qd As QueryDef36.
Replace any references to Field with references to Field36 in your code. For example, change Dim fld As Field to Dim fld As Field36.
Replace any references to Index with references to Index36 in your code. For example, change Dim idx As Index to Dim idx As Index36.
Replace any references to Relation with references to Relation36 in your code. For example, change Dim rel As Relation to Dim rel As Relation36.
Replace any references to TableDef with references to TableDef36 in your code. For example, change Dim td As TableDef to Dim td As TableDef36.
Replace any references to Workspace with references to Workspace36 in your code. For example, change Dim wk As Workspace to Dim wk As Workspace36.
Replace any deprecated methods, properties, or constants with their equivalent or alternative ones in the newer versions of DAO. For example, change db.CreateProperty("Jet OLEDB:Database Password", dbText, "mypassword") to db.Properties("Jet OLEDB:Database Password") = "mypassword".
By updating your DAO code to use the latest version of Microsoft DAO, you can avoid potential errors or issues and take advantage of the new features and improvements of DAO.
How to Use Microsoft DAO 2.5 3.5 Compatibility Library to Access SQL Data Sources?
One of the main benefits of using Microsoft DAO 2.5 3.5 Compatibility Library is that it allows you to access SQL data sources using VBA code. SQL data sources are databases that use the Structured Query Language (SQL) as their standard language for defining and manipulating data. Some examples of SQL data sources are Microsoft SQL Server, Oracle, MySQL, and PostgreSQL.
Microsoft DAO 2.5 3.5 Compatibility Library supports two technologies for accessing SQL data sources: Jet and ODBCDirect. Jet is a database engine that can connect to various data sources using ODBC drivers or OLE DB providers. ODBCDirect is a direct interface that bypasses Jet and connects to ODBC data sources using ODBC drivers.
Both Jet and ODBCDirect have their advantages and disadvantages, depending on your needs and preferences. Here is a comparison table of the two technologies:
Jet ODBCDirect --- --- Pros - Supports both local and remote data sources- Supports both ODBC and OLE DB connections- Supports transactions and record locking- Supports creating and modifying database objects- Supports security and encryption features - Faster and more efficient than Jet- Supports remote data sources only- Supports ODBC connections only- Supports stored procedures and pass-through queries- Supports batch updates and asynchronous execution Cons - Slower and less efficient than ODBCDirect- Requires more memory and disk space- Does not support stored procedures and pass-through queries- Does not support batch updates and asynchronous execution - Does not support local data sources- Does not support OLE DB connections- Does not support transactions and record locking- Does not support creating and modifying database objects- Does not support security and encryption features In the following sections, I will show you how to use Jet and ODBCDirect with Microsoft DAO 2.5 3.5 Compatibility Library in more detail.
How to Use Jet with Microsoft DAO 2.5 3.5 Compatibility Library?
Jet is a database engine that can connect to various data sources using ODBC drivers or OLE DB providers. Jet can access both local and remote data sources, such as Access, Excel, Text, SQL Server, Oracle, etc. Jet can also create and modify database objects, such as tables, queries, indexes, relations, etc., using VBA code.
To use Jet with Microsoft DAO 2.5 3.5 Compatibility Library, you need to do the following:
Create a connection string that specifies the data source name (DSN), the provider name (if using OLE DB), the database name (if applicable), the user name and password (if required), and any other connection parameters.
Create a Database object that represents the connection to the data source using the OpenDatabase method of the JetEngine object.
Create a Recordset object that represents the result set of a query or a table using the OpenRecordset method of the Database object.
Use the methods and properties of the Recordset object to retrieve, add, change, or delete data in the data source.
Use the methods and properties of the Database object to create or modify database objects in the data source.
Use the methods and properties of the JetEngine object to implement security and encryption features in the data source.
Close the Recordset and Database objects when you are done with them.
Here are some code examples and best practices for using Jet with Microsoft DAO 2.5 3.5 Compatibility Library:
How to Create a New Database or Change the Design of an Existing Database with Jet?
If you want to create a new database or change the design of an existing database using Jet, you can use the methods and properties of the Database object and its related objects, such as TableDef, QueryDef, Index, Relation, etc. Here are some code examples and best practices for doing so:
To create a new database, use the CreateDatabase method of the JetEngine object. For example, the following code creates a new Access database named MyDB.mdb in the C drive:
Dim jet As JetEngine36 Dim db As Database36 Set jet = New JetEngine36 Set db = jet.CreateDatabase("C:\MyDB.mdb", "Jet OLEDB:Engine Type=5")
To change the design of an existing database, use the OpenDatabase method of the JetEngine object with the option dbOpenExclusive. For example, the following code opens an existing Access database named MyDB.mdb in exclusive mode:
Dim jet As JetEngine36 Dim db As Database36 Set jet = New JetEngine36 Set db = jet.OpenDatabase("C:\MyDB.mdb", dbOpenExclusive)
To create a new table in a database, use the CreateTableDef method of the Database object. For example, the following code creates a new table named Customers with three fields: CustomerID, CustomerName, and CustomerEmail:
Dim td As TableDef36 Dim fld As Field36 Set td = db.CreateTableDef("Customers") Set fld = td.CreateField("CustomerID", dbLong) td.Fields.Append fld Set fld = td.CreateField("CustomerName", dbText, 50) td.Fields.Append fld Set fld = td.CreateField("CustomerEmail", dbText, 100) td.Fields.Append fld db.TableDefs.Append td
To modify an existing table in a database, use the TableDefs collection of the Database object. For example, the following code adds a new field named CustomerPhone to the Customers table:
Dim td As TableDef36 Dim fld As Field36 Set td = db.TableDefs("Customers") Set fld = td.CreateField("CustomerPhone", dbText, 20) td.Fields.Append fld
To create a new query in a database, use the CreateQueryDef method of the Database object. For example, the following code creates a new query named CustomerList that selects all fields from the Customers table:
Dim qd As QueryDef36 Set qd = db.CreateQueryDef("CustomerList", "SELECT * FROM Customers") db.QueryDefs.Append qd
To modify an existing query in a database, use the QueryDefs collection of the Database object. For example, the following code changes the SQL statement of the CustomerList query to select only CustomerName and CustomerEmail from the Customers table:
Dim qd As QueryDef36 Set qd = db.QueryDefs("CustomerList") qd.SQL = "SELECT CustomerName, CustomerEmail FROM Customers"
To create a new index on a table in a database, use the CreateIndex method of the TableDef object. For example, the following code creates a new index named CustomerIDIndex on the CustomerID field of the Customers table:
Dim td As TableDef36 Dim idx As Index36 Set td = db.TableDefs("Customers") Set idx = td.CreateIndex("CustomerIDIndex") idx.Fields.Append idx.CreateField("CustomerID") idx.Primary = True 'make it a primary key index td.Indexes.Append idx
To modify an existing index on a table in a database, use the Indexes collection of the TableDef object. For example, the following code changes the name of the CustomerIDIndex index to CustIDIndex:
Dim td As TableDef36 Dim idx As Index36 Set td = db.TableDefs("Customers") Set idx = td.Indexes("CustomerIDIndex") idx.Name = "CustIDIndex"
To create a new relation between two tables in a database, use the CreateRelation method of the Database object. For example, the following code creates a new relation named OrdersCustomers between the Orders table and the Customers table, based on the CustomerID field:
Dim rel As Relation36 Set rel = db.CreateRelation("OrdersCustomers", "Orders", "Customers", dbRelationUpdateCascade) rel.Fields.Append rel.CreateField("CustomerID") rel.Fields("CustomerID").ForeignName = "CustomerID" db.Relations.Append rel
To modify an existing relation between two tables in a database, use the Relations collection of the Database object. For example, the following code changes the name of the OrdersCustomers relation to CustOrders:
Dim rel As Relation36 Set rel = db.Relations("OrdersCustomers") rel.Name = "CustOrders"
By using Jet with Microsoft DAO 2.5 3.5 Compatibility Library, you can create and modify databases using VBA code.
How to Retrieve, Add, Change, or Delete Data in a Database with Jet?
If you want to retrieve, add, change, or delete data in a database using Jet, you can use the methods and properties of the Recordset object and its related objects, such as Field, Bookmark, Filter, etc. Here are some code examples and best practices for doing so:
To retrieve data from a database, use the OpenRecordset method of the Database object with the appropriate options. For example, the following code opens a recordset that contains all records from the Customers table:
Dim rs As Recordset36 Set rs = db.OpenRecordset("Customers", dbOpenTable)
To add data to a database, use the AddNew method of the Recordset object. For example, the following code adds a new record to the Customers table with the values "C001", "John Smith", and "john@example.com" for the CustomerID, CustomerName, and CustomerEmail fields respectively:
rs.AddNew rs!CustomerID = "C001" rs!CustomerName = "John Smith" rs!CustomerEmail = "john@example.com" rs.Update
To change data in a database, use the Edit method of the Recordset object. For example, the following code changes the CustomerName field of the current record to "Jane Smith":
rs.Edit rs!CustomerName = "Jane Smith" rs.Update
To delete data from a database, use the Delete method of the Recordset object. For example, the following code deletes the current record from the Customers table:
rs.Delete
To navigate through the records in a recordset, use the methods and properties of the Recordset object, such as MoveFirst, MoveLast, MoveNext, MovePrevious, EOF, BOF, etc. For example, the following code loops through all records in the Customers table and prints their CustomerName and CustomerEmail fields:
rs.MoveFirst Do While Not rs.EOF Debug.Print rs!CustomerName & " - " & rs!CustomerEmail rs.MoveNext Loop
To search for a specific record in a recordset, use the FindFirst, FindLast, FindNext, or FindPrevious method of the Recordset object. For example, the following code searches for a record that has "C001" as its CustomerID field value:
rs.FindFirst "CustomerID = 'C001'" If Not rs.NoMatch Then Debug.Print rs!CustomerName & " - " & rs!CustomerEmail Else Debug.Print "No match found" End If
To sort or filter a recordset, use the Sort or Filter property of the Recordset object. For example, the following code sorts the recordset by CustomerName in ascending order and filters it by CustomerEmail that contains "@example.com":
rs.Sort = "CustomerName ASC" rs.Filter = "CustomerEmail LIKE '*@example.com*'"
To close a recordset when you are done with it, use the Close method of the Recordset object. For example:
rs.Close
By using Jet with Microsoft DAO 2.5 3.5 Compatibility Library, you can retrieve, add, change, or delete data in a database using VBA code.
How to Implement Security to Protect Your Data with Jet?
If you want to implement security to protect your data using Jet, you can use the methods and properties of the JetEngine object and its related objects, such as User, Group, Permission, etc. Here are some code examples and best practices for doing so:
To create a new user in a database, use the CreateUser method of the JetEngine object. For example, the following code creates a new user named Alice with the password "1234" in the MyDB.mdb database:
Dim jet As JetEngine36 Dim usr As User36 Set jet = New JetEngine36 Set db = jet.OpenDatabase("C:\MyDB.mdb", dbOpenExclusive) Set usr = jet.CreateUser("Alice", "1234") db.Users.Append usr
To create a new group in a database, use the CreateGroup method of the JetEngine object. For example, the following code creates a new group named Admins in the MyDB.mdb database:
Dim jet As JetEngine36 Dim grp As Group36 Set jet = New JetEngine36 Set db = jet.OpenDatabase("C:\MyDB.mdb", dbOpenExclusive) Set grp = jet.CreateGroup("Admins") db.Groups.Append grp
To add a user to a group in a database, use the Append method of the Users collection of the Group object. For example, the following code adds Alice to the Admins group in the MyDB.mdb database:
Dim jet As JetEngine36 Dim grp As Group36 Set jet = New JetEngine36 Set db = jet.OpenDatabase("C:\MyDB.mdb", dbOpenExclusive) Set grp = db.Groups("Admins") grp.Users.Append db.Users("Alice")
To set permissions for a user or a group on a database object, use the Permissions property of the User or Group object. For example, the following code grants read and write permissions to Alice on the Customers table in the MyDB.mdb database:
Dim jet As JetEngine36 Dim usr As User36 Set jet = New JetEngine36 Set db = jet.OpenDatabase("C:\MyDB.mdb", dbOpenExclusive) Set usr = db.Users("Alice") usr.Permissions("Customers") = dbSecReadDef + dbSecWriteDef
To encrypt a database with a password, use the EncryptDatabase method of the JetEngine object. For example, the following code encrypts the MyDB.mdb database with the password "mypassword":
Dim jet As JetEngine36 Set jet = New JetEngine36 jet.EncryptDatabase "C:\MyDB.mdb", "mypasswo