Hi-

I've been doing a lot of research on the best way to integrate SQL Server
2005 and Access. Many of Mary's suggestions to others lead me to these
decisions, so maybe she will comment? What I've learned has lead me to the
following conclusions:

Use linked tables to SQL 2005 for general data activities & bound forms
Use pass through queries to retrieve and store infrequently changed data
locally (drop down list items etc)
Use pass through queries for reports.
Use pass through queries for large batch updates.
Utilize views, functions and stored procedures wherever possible.
Use SQL Profiler to examine and optimize the system after its designed.

Does anyone see anything flawed with this approach, have any additional
suggestions, or is there anything I've missed?

Thanks,

-David

Looks to me like you nailed it :-) The golden rule is to fetch only
needed data, so creating a query-by-form interface to use with your
linked tables is a good idea when editing or inserting data. That way
you'll only be fetching the single record being edited. Also, take
advantage of caching static data in Jet local tables to minimize round
trips to the server. For security reasons, don't save links, but
dynamically re-link on app startup and delete links on app exit.

--Mary

On Fri, 10 Mar 2006 06:30:27 -0800, DBG
<DBG@discussions.microsoft.com> wrote:

>Hi-
>
>I've been doing a lot of research on the best way to integrate SQL Server
>2005 and Access. Many of Mary's suggestions to others lead me to these
>decisions, so maybe she will comment? What I've learned has lead me to the
>following conclusions:
>
>Use linked tables to SQL 2005 for general data activities & bound forms
>Use pass through queries to retrieve and store infrequently changed data
>locally (drop down list items etc)
>Use pass through queries for reports.
>Use pass through queries for large batch updates.
>Utilize views, functions and stored procedures wherever possible.
>Use SQL Profiler to examine and optimize the system after its designed.
>
>Does anyone see anything flawed with this approach, have any additional
>suggestions, or is there anything I've missed?
>
>Thanks,
>
>-David

Mary-

Thanks very much for your response. I literally spent weeks trying to find
a good solution to this (among DAO, ADO, ODBCDirect, Pass-Through, linked
tables, and whatever flavor programmer X likes best), and its nice to have my
research confirmed by someone both well regarded and well published on the
subject.

> Looks to me like you nailed it :-) The golden rule is to fetch only
> needed data, so creating a query-by-form interface to use with your
> linked tables is a good idea when editing or inserting data. That way
> you'll only be fetching the single record being edited.

Can you describe a bit more your definition of how the design of a
query-by-form interface works. I'm pretty sure I understand, but want to be
absolutely sure

> Also, take
> advantage of caching static data in Jet local tables to minimize round
> trips to the server.

Absolutely, I've been working on a module to handle this.

> For security reasons, don't save links, but
> dynamically re-link on app startup and delete links on app exit.

Can you point me to a reference source or explain more so that I create this
functionality? I have a good idea of some ways I could accomplish this, so
I'm only asking because security is so important to get right.

Thanks again,

-David

For query-by-form, the form could have a series of combo boxes: select
customer, select date range of orders, select order for that date,
then load the order to edit. For startup, you'd delete any linked
tables, then relink with user information. Create a hidden form that
calls the code to delete links when app closes. You delete old links
on app open in case it shut down abnormally the previous time and the
links are still cached. Only use integrated security and realize that
nothing in Access is securable in any meaningful sense of the word.
Use DAO code for the linking, it's the easiest and fastest. You create
TableDef objects and set their properties such as name,
sourcetablename, and connection string.

--Mary

On Thu, 16 Mar 2006 10:56:02 -0800, DBG
<DBG@discussions.microsoft.com> wrote:

>Mary-
>
>Thanks very much for your response. I literally spent weeks trying to find
>a good solution to this (among DAO, ADO, ODBCDirect, Pass-Through, linked
>tables, and whatever flavor programmer X likes best), and its nice to have my
>research confirmed by someone both well regarded and well published on the
>subject.
>
>> Looks to me like you nailed it :-) The golden rule is to fetch only
>> needed data, so creating a query-by-form interface to use with your
>> linked tables is a good idea when editing or inserting data. That way
>> you'll only be fetching the single record being edited.
>
>Can you describe a bit more your definition of how the design of a
>query-by-form interface works. I'm pretty sure I understand, but want to be
>absolutely sure
>
>> Also, take
>> advantage of caching static data in Jet local tables to minimize round
>> trips to the server.
>
>Absolutely, I've been working on a module to handle this.
>
>> For security reasons, don't save links, but
>> dynamically re-link on app startup and delete links on app exit.
>
>Can you point me to a reference source or explain more so that I create this
>functionality? I have a good idea of some ways I could accomplish this, so
>I'm only asking because security is so important to get right.
>
>Thanks again,
>
>-David