Client's Speak |  FAQ
Call us: 248.681.1283
Follow Us:

Articles

Microsoft Access Database (Mdb) Vs Access Data Project (Adp)

Posted On: Sep 13, 2014

Microsoft Access Database (Mdb) Vs Access Data Project (Adp)

Both Microsoft Access Database and Access Database Project can be used to develop database management applications. Although their business objective is same, an access data project (.adp file) differs from a regular access database (.mdb file) in many ways. It also eliminates lot of problems you are likely to encounter when running a database that requires several users to be logged in at the same time. The main difference between the two is that an MDB file contains all the code, data, queries etc. in one neat little container (an MDB file), the ADP separates the data from the application by using tables and queries residing on the back-end database such as SQL server.

 

 The main advantage of the ADP over the MDB is that the data could be housed in a central and high-powered RDBMS such as SQL Server. The biggest disadvantage with the MDB is that any end user can make a copy and put new data in that copy, thus creating two of the same databases with different data in it, which creates a greater deal of confusion in the whole user group. Then we have multiple databases out there with each user adding different data to each one, they make copies of their new ones and distribute them to everybody, now we have many different database files, none of them have all the data we would like to have at one place. With an ADP, users can make copies of the project all day long if they want, and as developers, without causing any confusion because there is only one central location to change data irrespective of number of ADP files.

 

 Access data projects (.ADP) are designed to connect to a Microsoft SQL Server or Microsoft SQL Server Desktop Engine (MSDE) back-end database. Structured Query Language (SQL) is a standard interactive and programming language for working with relational databases. The Transact-SQL language is the native SQL dialect used by both SQL Server and the Desktop Engine. Transact-SQL supports ANSI SQL-92, the latest SQL standard. In Access 2002 or later that support the ANSI SQL-92, projects running queries or filter expressions against SQL Server data must do so in ANSI SQL-92 syntax. This will affect the way that you develop filter expressions and queries in Access Data Projects (.ADP).

 

 Microsoft Access databases (.MDB) use the Jet Database Engine to query against the native data. Jet supports the ANSI SQL-89 standard (Jet SQL syntax). Jet SQL syntax differs from the ANSI SQL-92 standard in several ways. For example, Jet SQL syntax uses an asterisk (*) for a multiple character wildcard, while ANSI SQL-92 syntax uses a percent sign (%). For example, if you had a macro in MDB file that is searching for CustomerName starting with “A” would have an expression as CustomerName = A*. It no longer finds all customer names beginning with the letter “A” in ADP file because the asterisk (*) is not an ANSI SQL-92 wildcard character. To resolve this, revise the macro expression to use the percent sign (%) wildcard character instead making it to CustomerName = A%


 The Jet Database Engine is used to work with the data in an Access (.MDB) database and supports the ANSI SQL-89 standard. However, access data project (.ADP) connects to Jet by using ActiveX Data Objects (ADO) and the Jet OLEDB provider. ADO and OLEDB always use ANSI SQL-92 syntax. Thus, data access pages must use ANSI SQL-92 syntax. So, a form and a page bound to the same query can return different results, depends on weather it is in MDB or ADP.