I’m trying to step up from being a decent Access Programmer, to
including SQLServer in my repertoire and am running into a conceptual
design problem. Any help would be appreciated.

Setup - Access2003 Front End, SQLServer2008 back end

Two tables – Components and Events… components is a list of items,
Events is a list of actions taken to the components over time, e.g.,
placing in service, inspections, repairs, taking out of service

Problem – the user needs to be able to determine the status of the
components at a user selected date. The date may be after the last
event record, or there may have been events after the selected date.
Those events should be ignored.

For example, for Component 001,
• in service 1/1/11
• inspected and failed 2/1/11
• repaired 2/3/11
• inspected and passed 3/1/11
• taken out of service 4/1/11

If the user selects a status date of 1/7/12, the component would be
“out of service”. However, if the status date is 2/2/11, the
component would be “awaiting repair”

How do I pass this status date from the Access front end to the
SQLServer so I can make a view that includes the status of the
component on the desired status date? I know I can pull all the data
to the Access front end and do the processing there, but that seems
wildly inefficient!

Note I am dealing with tens of thousands of components and hundreds of
thousands of events if that makes any difference.

Thanks

One way to pass a query from your Access database to the server (in your
case, but not necessarily, Microsoft SQL Server) is to use a Passthrough
Query (which means to ODBC "pass this to the server, unchanged -- keep your
hands {electrons?} off".

However, what you describe can, I think, be handled with "normal queries"
that have a date criteria (depending, of course, on the design of your
tables -- which you haven't detailed).

By the way, despite claims of those ignorant of the workings of Access and
SQL Server, using "normal Access queries" will NOT (repeat NOT) "pull all
the data to the Access front end and do the processing there". It certainly
takes a good deal of work to do so, and the vast majority of Access users,
even experienced ones, don't "know how" to do so. (One way, of course, is to
include calls to VBA procedures, which will force the records to be
retrieved and processed in Access -- but simply retrieving the data via
ODBC, even with criteria, turns out to to be surprisingly fast and mostly
done at the server.)

If we assume that you can create a query to retrieve the component and event
data, with the date of the event. If the event date is named "EventDate" and
you want only the records between November 12, 2011 and December 30, 2011,
you could create an Access query with a WHERE clause reading 'WHERE
EventDate BETWEEN #11/12/2011# AND #12/30/2011#'. If you preferred to force
the data extraction to be done in the back end, no matter what, you could
make it into a Passthrough Query, using the SQL Server 'flavor' of SQL and
SQL Server operators. Although I show the WHERE Clause in Jet/ACE SQL, you
could also create it in the Access Query Builder.

There are a lot of server-specific approaches to performing operations
faster -- you'll require detailed knowledge of tuning and optimizing
performance in MS SQL Server (possibly even the specific version you are
using). And, this newsgroup is not the best source for SQL Server-specific
information; though we can, and do, address interface between Access and
servers. I suspect you'll find some USENET newsgroups and/or Microsoft
online Forums on the subject of SQL Server that would be helpful -- but they
are unlikely to be as "granular" as the Microsoft-sponsored newsgroups that
were so helpful for so long before Microsoft stopped supporting them.

One possible advantage of using Views would be if you were selecting only
specific types of events, and preloaded the Views into SQL Server ... they
would be pre-optimized as stored. I've done this for reports with complex
queries... but did not create Views "in real-time" (nor as a matter of fact,
for SQL Server, they were Informix views, back in the day when Informix was
an independent company before it was bought by IBM, and was the corporate
standard database for the company to which I was contracted).

If you are using Access Project (ADP) with ADO, then I'm not able to assist,
and my comments here are not necessarily applicable. However, if you've
been led down _that_ "garden path", be aware that the Access Team at
Microsoft now recommends Access <-> Jet <-> ODBC <-> SQL Server (or, with
Access 2007 or 2010, Access <-> ACE <-> ODBC <-> SQL Server) as the method
of choice for connecting an Access application to MS SQL Server.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Tom" <rtmnews (AT) swbell (DOT) net> wrote

I’m trying to step up from being a decent Access Programmer, to
including SQLServer in my repertoire and am running into a conceptual
design problem. Any help would be appreciated.

Setup - Access2003 Front End, SQLServer2008 back end

Two tables – Components and Events… components is a list of items,
Events is a list of actions taken to the components over time, e.g.,
placing in service, inspections, repairs, taking out of service

Problem – the user needs to be able to determine the status of the
components at a user selected date. The date may be after the last
event record, or there may have been events after the selected date.
Those events should be ignored.

For example, for Component 001,
• in service 1/1/11
• inspected and failed 2/1/11
• repaired 2/3/11
• inspected and passed 3/1/11
• taken out of service 4/1/11

If the user selects a status date of 1/7/12, the component would be
“out of service”. However, if the status date is 2/2/11, the
component would be “awaiting repair”

How do I pass this status date from the Access front end to the
SQLServer so I can make a view that includes the status of the
component on the desired status date? I know I can pull all the data
to the Access front end and do the processing there, but that seems
wildly inefficient!

Note I am dealing with tens of thousands of components and hundreds of
thousands of events if that makes any difference.

Thanks

"Tom" wrote in message
news:40c288ea-d0c9-477a-ad8a-d7875d909aec (AT) p13g2000yqd (DOT) googlegroups.com...

Actually, as Larry points out, the Access client does a good job of
filtering only information that you need.
If your query is bound to a report, and you open that report supplying a
typical "where" for that report, then Access will NOT pull down all the
records, but in most cases Access will only pull down the required records
and respect your filter.

So it not a "blanket all" statement that all records come down the network
pipe from SQL to Access.

In other words for the the most part, you can simply use the access client
query builder. You build your query as need. So SQL server will respect the
conditions and filtering placed into those queries, and therefore only pull
down those records you require.

However there are a number of types of queries that can benefit enormously
from pass-though or "forcing" the query to run server side.

A nice example is aggregate query with joins and group by. If you think
about this some of these groupings need a "count" or "sum" of values in each
row, and thus all rows for the sum have to be touched. And often Access
needs each row (even if only based on your filter). The suggestion in these
cases is to consider using a pass-through query since all of that summing is
done before the row comes down the network pipe (so many rows can be totaled
up, but the final output is one row and with pass-though only the one result
row comes down the pipe).

However for the most part I don't use a lot of pass-though query, and the
reason is one of syntax errors and when I have to edit/change the SQL.

And as a result of my amazing laziness, I found a better trick with less
effort:

I create a view using the SQL server tools. In fact I often cut+paste the
SQL from the Access query builder and drop it right into the SQL server
query builder. Often most of the SQL is quite close. Now at this point I
have a SQL server compatible query and syntax. I save this as a SQL server
view.

Then in the access client I link to that view (you use the linked table
manager which will show both tables and views from the SQL server side of
things). The result of this is that I get pass-through query performance,
but I don't have to build a pass through query in the Access client side.
Building + maintain that SQL on the Access client side is "more work" since
you do NOT have query tools and syntax checking based on SQL server sides
syntax.

I suppose one could use the SQL server tools to build the query and then cut
+ paste that SQL into the Access client. However, if you use a view then you
can can go BACK and modify and change the SQL (view) over time "in place" on
the SQL server side You not need to open query Access side, cut + paste to a
temp query server side, test + edit, and then cut + paste back to the Access
client. So from a maintenance point of view, it's simply easier to use and
keep the SQL on the SERVER side as a view. You can easy edit + open +
modify those views very much just like how we edit queries in the Access
client side.

So in some cases pass-through or views really do run much faster when I have
aggregate types of queries especially with a few table joins and a group by.
Thus each sum row is crunched servre side down to one row before that row is
sent down the pipe.

Building such queries is really very similar in Access client, or in the SQL
server client. The SQL query designers are in fact very similar once you
flip into "design" mode as opposed to SQL mode.
So this view idea is nice since I quite much get pass-though query
performance. And reports "where" clauses work very well since sql server
applies it to the rows only being sent down. So this setup is better and
more forgiving then building a complex query Access client side, and it less
work then going 100% pass-though.

And you are free to build local client queries on those linked "view" tables
with ease and success. Often since sorting etc. of the query is ignored by
a report then I just attached the report to the view and setup the sorting
in the report like we have to anyway.
So often little need to build a local query that is based on a link to view
to SQL server.

So in summary:
For the most part and for a good vast majority of the queries I build, I
simply build them in the access client as regular queries based on the
linked tables to SQL server.

As pointed out indexing and filtering usually works quite well and not all
of the table rows are brought down the network pipe in most cases.

For complex joins or sub queries + aggregates (sums) etc, then in fairness I
will admit these types of query be messed up by the Access client.

And thus in place of building a passthrough query, simply create a view
server side with the nice query builder SQL server side.
You save the view and then from Access table linker link to that view. My
relink routines even let me add "one" link at a time now so this is fast for
me.

Once you link to this view, then you are off to the races with what I found
to be less work and hassle then building a pass-through query.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

Thanks to both of you Larry and Albert... I had forgotten about pass-
through queries.

One thing I didn't mention in an attempt to simplify the question was
that there is a Facility table wherein one facility has many
Components and one Component has many Events. Only one facility is
dealt with at a time. So each query to the Events table necessarily
involves a where clause that is based on user defined variable (i.e.,
the status date) and the selected Facility. It sounds like the thing
to do is to just set up a pass-through query set up on the fly
incorporating those two variables.

The other part of the problem is that determining status is a very
complex task. Initially, when just using Access for both FE and BE, I
had it set up with a bunch of subqueries with UDFs. Very elegant, I
thought, but performance sucked. Ultimately to be usable, I had to
resort to temporary tables (boo, hiss, I know, but I couldn't find an
acceptable way around it). I was hoping to shift all that processing
to the server and get rid of the temp tables. But unless I'm missing
something, since I can't pass the facility and status date up to the
server, all the complex processing will still occur in the Access FE.
<sigh>

Any further thoughts based on this new information would be
appreciated.

Thanks, Tom

You certainly _can_ pass the Facity and the StatusDate up to the server...
as Criteria. What you can't do is pass the UDF to be executed on the
server, since the server does not process VBA (no servers that I know about,
in any case) -- at this point, you need to start becoming an "SQL Server
Developer" and learn how to write SQL Server stored procedures... depending
on version, there are some languages that you can use, the one that's been
around for a while, and usable with the SQL Server version you mentioned is
Transact SQL, and the latest versions can use DotNet languages, VB.NET or
C#... not being much more than a "novice SQL Server user", I wouldn't
venture to suggest what you should use.

But, what I will say is that it's difficult for me to imagine a logical
situation where you'd _need_ to do a lot of UDF calculation to choose the
records to select (not impossible, mind you, but seems unlikely). If the
actual Components (s) and Facilities in the records enter into these
calculations, you might be able to use Totals Queries to obtain/calculate
the information, then simply use the _result_ in the criteria of your final
query.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Tom" <rtmnews (AT) swbell (DOT) net> wrote

May I take the opportunity of this discussion for an opinion from other
"seasoned" access developers.

My name is Stefano, from Italy; I've been using Access since year 2001.
I read the NG on a daily basis, but ths is my very first post.

I've always used Access as a FE to SQL Server via ADO/ODBC, being firmly
convinced that ADPs are, in the long run, a serious obstacle to
performance and manageability. Thus, my only choice has aways been
Access<->ADO/ODBC<->SQL Server, with excellent results I'm not afraid to
say.

Now I read from you that "the Access Team at Microsoft now recommends
Access<->ACE<->ODBC<->SQL Server as the method of choice". What do you
think of ADO instead? I know it's a supported model that will continue
to be enhanced (see MSDN sept. 2011). I open a connection (e.g. reading
from a .udl file) and use it to fetch data into *disconnected* ADO
recordsets. I've always considered JET/ACE as excellent tools for Access
backends only...

Also (since Mr. Kallal has answered the post, and will possibly read
mine), I also use to write views server-side (almost exclusively I'd
say), but instead of linking them to the database via ODBC, I use to
manage passthrough query texts, appending custom WHERE clauses, to
filter server side. Just my own experience, but I've found cases where
Access would pull all records because his SQL syntax was not fully
server-compliant, or because of joins between tables. Also, this way, I
can alter the server view without having to tinker with the FE code.

This modus operandi has allowed me to write complex multiuser scalable
applications with minimal impact on server performance. But an opinion
from you would be much appreciated. Thank you.

Have a good day.


Il 08/01/2012 1.13, Access Developer ha scritto:

"Stefano C" wrote in message
news:4f0acffd$0$1390$4fafbaef (AT) reader2 (DOT) news.tin.it...

The problem is not ADO. They may continue to enhance it but
SQL not going to support it.

ADO is has quite much been much superseded by ADO.net.
I can't say there that's much activity occurring with ADO.

If you using new version of Access and SQL server cloud (Azure), then I do
not believe there's a set of oleDB
drivers at this point in time for ADO. However you can use ODBC and Access.

And there are a bunch of issues that come up with using linked tables to
SharePoint - again DAO is the better choice.
(in fact I don't even think a good number of features work with ADO at all).

So the issue is not only the Access team at Microsoft, but you can
read the announcement in the following article:

http://blogs.technet.com/b/dataplatf...with-odbc.aspx

http://blogs.msdn.com/b/sqlnativecli...ta-access.aspx

The above does not mean ADO is on its way out tomorrow. But it not an
ideal case here either.

To be honest and fair I 100% accept that using ADO is a better
object model and choice when dealing with SQL server then that of
DAO.

However the above announcements don't bid too well for ADO on sql server.

Access uses will continue to use ODBC.
The problem is all these new web technogloes (access with linked talbes to
sharepoint also do not work).
So things like multi-value support etc. continue to be enhanced in Access
and SharePoint.
And SharePoint and DAO now gives us local disconnected tables.

Sure, calling stored procs and using a pass though works fine. It is a time
honored approach.

Even call a SP from DAO is easy:
I use:

Dim qdfPass As DAO.QueryDef


Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc " & "p1"
qdfPass.Execute

Note how no connection string or object need be messed with. So a total of 3
lines of code here.

At the end of the day, I think it's EASY to make the case that ADO is a
better choice when using access and SQL server.
However, if the original application was not written using ADO, then I think
continuing to use DAO is a better choice.
(so what you started out using much tends to suggest that is what you
continue to use).

I don't think the problem is future support of ADO. You'll probably be able
to download and install those ADO libraries for the next 15 or 20 years.

The problem is with SharePoint, SQL Azure, and the new emerging server
technologies from Microsoft. They don't seem to be supporting ADO.

In other words if they tell you that they're going to enhance and supporting
ADO (very true).
However the future for use with new server products does not look too good.

At the end of the day, this is not a decision or change that's going to
occur overnight.

I don't think there is any hurry or some deadline either. However, it looks
like ODBC is much being supported for the long term future.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

Many thanks for your reply, especially for those links to MSDN blog. I
missed those topics.

Il 13/01/2012 17.57, Albert D. Kallal ha scritto:
http://msdn.microsoft.com/en-us/library/ms810810.aspx

While SQLODBC and SQLOLEDB are deprecated, ADO sits on top of ODBC.
Also, Microsoft has replied to the discussions you linked stating that
"... this announcement applies to SQL Server Native Access Client (SNAC)
OLE DB provider only and it does not apply to other OLE DB providers
either from Microsoft or 3rd party software vendors. SNAC provider is
used primarily with native applications (C, C++) connecting to Microsoft
SQL Server."

True, I'll have to consider working with ODBC directly (not through ADO,
even though it sits on its top) from now on. The fact is, I'm in true
love with ADO.Connection...

As regards ADO.net: my two cents here, but the whole .net will stay on
top until Windows 8 will enter the scene.

plague. While I'm aware the world is heading for web technologies only,
there are fields where they're totally useless.

I'm an industrial automation engineer. I've used to embed ocx controls
in my apps to manage industrial equipment, which mostly communicates
through serial ports still nowadays, or PLCs. Today, I can write a large
integrated application ranging from field data collection to report
generation for the management. Sharepoint is just out of business here.

to a mass hallucination.

Thanks again for sharing your thoughts.

All the best.

Il 07/01/2012 23.58, Tom ha scritto:
Server" by Chipman & Baron.

Don't be misleaded by its publication date: solid concepts and good
ideas do travel through time.