Hi everyone
I was asked to create an access database and connect it to SQL Server.
My boss mentions something about choosing the right engine?
Of course (from my point of view) I have no idea what is all about can someone explain me what is all about (basic info) or send me some articles about it?
Many thanks in advance
Emil
In short (and there are lots of references on the web that can go into much more detail than I need to provide here so this is an overview basically) you generally have two choices with Access interaction with SQL Server.
Microsoft Access has two file extension formats namely the .MDB file and the .ADP Project file.
The MDB format uses the client based JET database engine and a has a query 'passthrough' capability that is to say, using ODBC you can communicate with SQL server and write transact SQL script language to do what you require ie create delete, retrieve table data either via scripting or by linking tables directly to the database window of the mdb format (where they are represented by a globe icon when so linked).
The MDB file format allows for the creation of local tables on the client within the MDB file itself. The MDB format has been the standard access format since the inception of the access program and uses DAO and/or ADO object libraries for controlling objects and recordsets.
The ADP project file came
'after' the mdb format and was heavily promoted (prior to Acc2007) for interaction with SQL server. It exposes stored procedures and views directly within the Access interface and connects to SQL server using UDL (Universal DataLink). You do not create local tables within this format the main purpose of it is to communicate directly with the server and as such, you do not need to use the client machine based JET engine in this scenario given the workflow is to process your information
server side as opposed to client side thereby reducing network traffic. You can handle datasets using ADO on the client machine.
Your Access screen forms can bind directly to 'views' (query equivalents) or parametised 'stored procedures' which are the most efficient method of transacting SQL server data because they are precompiled on the server and when written optimally return only the data you need to the application side.
Your choice of which format you use is a matter for you and largely depends on your requirements. In either case you can raise reports and to all intents and purposes functionally achieve most of what you require from either format. (
I personally prefer the ADP format for interacting with SQL server data because of the prebuilt dedicated easies within that format but don't be tainted by that.)
Hopefully this gives you a little something to get started with. Googling can find much what you need to know ie typically searches like : 'DAO versus ADO' or 'Stored procedures versus views' or 'MDB versus ADP' and so on.
Jim :)
93
