Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficientl
y
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Project
s
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in bot
h
MDB and ACCDB file formats, but only a subset of those features are availabl
e
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...|||ADP is still supported in Access 2007 which means that it will be supported
to at least end of 2016 (5 years mainstream support + 5 years extended
support).
I am sure there are lots of ADPs in the real-world but the majority of
Access databases still use MDB format.
In my experience, there is no problems using ODBC provided you follow a few
simple techniques on reducing the amount of data being pulled through the
network (this applies to all Client / Server database applications, anyway).
There are a number of articles in the Microsoft KB, e.g:
http://support.microsoft.com/kb/208858
and
http://support.microsoft.com/kb/209551
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>I am a forum newbie - appreciate your patience.
> We are embarking on a new adventure to scale up an Access DB up to SQL
> Server. We are not going to use the upsizing wizardry, but have chosen to
> build native SQL tables instead.
> The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?)
> We were prepared to move ahead with creating an Access Data Project using
> Access 2003, but then I read the following on microsoft.com ...
> " Access creates front-end applications that leverage SQL Server as a
> backend data source. Access forms and reports can be optimized as
> efficiently
> as Visual Basic front-end for SQL Server. Office Access 2007 offers two
> ways
> to connect to SQL Server data: linking to SQL Server and Access Data
> Projects
> (ADPs).
> The preferred way to connect to SQL Server is MDB file format or ACCDB
> file
> format. This enables you to use the full flexibility of local tables and
> local queries, while leveraging the full power of SQL Server. In addition,
> MDB and ACCDB files link to multiple SQL Servers and a wide variety of
> other
> data sources. Office Access 2007 contains many new features available in
> both
> MDB and ACCDB file formats, but only a subset of those features are
> available
> in ADPs. "
> This seems to state that best practice is to use linked tables. Is this
> true
> in the "real world"? Isn't using the layers of ODBC going to make for a
> slower app? Is ADP a technology that may not be supported by MS in the
> near
> future?
> Where would I find info to learn how to optimize Access/ODBC to work as
> well
> as a VB front-end as stated in the quote above?
> Your thoughts would be appreciated!|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supporte
d
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a fe
w
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway
).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>|||* Definitely Linked Tables from what I have heard for some time now. When
Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
definitely not the case with Access 2007. In fact, I think the new ACCDB
format utilises the new JET engine (and JET engine was supposed to be in
maintenance mode only!)
See http://www.tinyurl.com/y2yjzn
and have a look at the first paragraph of page 10 from the Microsoft
document above and I am sure you can infer what Microsoft impplies.
* I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
you need A2007 to use ACCDB. If you have a uniform enviroment where
everyone has A2007, ACCDB will probably be better (since the Microsoft
Access / JET engineers have exclusive control of the engine which enable
them to tune it to suit Access while the JET 4 engine is shared in the OS so
they can't change it easily). If you have a mixed environment, I think it
is safer to stick to the MDB / MDE format for the moment.
* I am confused about "VB" nowadays since it could mean either the old VB6
or VB.Net or VB2005 but don't you have to use some sort of interface such as
OLEDB to access data regardles of the BE engine? In my databases, I used
ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
ADO / OLEDB for SQL Server to access and manipulate data and the speed is
quite fast.
I think the major bottleneck in most database application is network
transmission bandwidth, not the database engine or the interface to the
database (I once added a million simple records one at a time to a JET Table
using DAO Recordset code and it took only about 3-4 seconds). I concentrate
on reducing the need to transfer data across the network to a minimum, e.g.
getting the SQL Server to do most processing and only pass back the
processed result to Access FE.
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
> Cindy & Van T.
> Thanks for taking time to respond to my question. I still have three
> lindering wunderabouts:
> - Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE
> app
> does MS consider best practice with the current revs of their Access & SQL
> products?
> - If, linked tables - how to choose between MDB and ACCDB?
> - What did they really mean in the quote above about making an Access
> FE as effiecient as a VB FE?
> If you have any additional info or know of a resource who might be of
> assistance please respond further.
> Your help is so incredibly appreciated.
> Best Regards,
> David Hogan|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAI
K,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS
so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such
as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostl
y
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Tab
le
> using DAO Recordset code and it took only about 3-4 seconds). I concentra
te
> on reducing the need to transfer data across the network to a minimum, e.g
.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>|||You're welcome ... Glad to help ...
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:7444F6AD-25F7-4DB5-B9E2-FD249F9C4C40@.microsoft.com...
> Van
> Thanks again for the wisdom. Access "Vision" document was very helpful. I
> will begin to start looking for material on how to code an application
> optimzed for a SQL server BE. I think your thoughts on minimizing network
> traffic are right on.
> I appreciate your generous assistance!
> David
>