ADP querying all rows in design view

[obviously.bogus]

I have an Access Project (.ADP) with a SQL Server 2000 database.

When creating a Form or Report in "Design View", Access 2007 is
querying the server with

"SELECT v_viewname.* FROM v_viewname"

and Access 2003 queries the server with

"SELECT * FROM v_viewname"

(I'm seeing these queries with SQL Server Profiler on the server
itself)

This happens every time the Form or Report is opened, every time a
field is added or removed, every time the sort order or grouping is
changed

Unfortunately, without any WHERE clause, the view can take a long time
to run, and sometimes even exceeds the timeout, and then bad things
start happening as Access then "forgets" the column list (even tho its
right there in the RecordSource property)

So is there anyway to prevent Access from doing "SELECT *" while in
Design View ?

 

[Arvin Meyer [MVP]]

Use a where clause or a join. Without it you will always get all the
records. You could also write a Stored Proc on the server to reduce the
number of records.

Bottom line: If you have lot's of records, don't ask for them all. Reduce
what the form wants to use as a recordsource. You could use unbound forms
and fill them with the results of a select statement as a recordsource,
using an event, such as the afterupdate of a combobox to supply the where
clause.

 

[Sylvain Lafontaine]

Strange. What's the exact record source that you are using for the form?

 

[Sylvain Lafontaine]

I would suggest that you create a blank ADP project and create a blank new
form based on this view to see if this behavior will repeat. If not, then
import everything into a blank new ADP project.

 

[jon.letham]

Thanks for all the replies ... I'll reply in this one message to all 3
replies.

Use a where clause or a join. Without it you will always get all the
records. You could also write a Stored Proc on the server to reduce the
number of records.

Bottom line: If you have lot's of records, don't ask for them all. Reduce
what the form wants to use as a recordsource. You could use unbound forms
and fill them with the results of a select statement as a recordsource,
using an event, such as the afterupdate of a combobox to supply the where
clause.

When in production, the forms and reports are being opened with a
WhereCondition, like this ...

DoCmd.OpenReport "ReportName", acViewPreview, , "ID = 1234"
DoCmdOpenForm "FormName", acNormal, , "ID = 1234"

.... so only the requied records are selected, and the query runs in a
fraction of a second.

Its in Design View that I'm having the problem, and since this is a
system that I inherited,
(and now have to maintain), where most of the Forms and Reports are
built this way, re-writing
to use Stored Procs or Unbound Forms is one of the last options I want
to consider.

Strange. What's the exact record source that you are using for the form?

Its the view name. I tried changing it to "SELECT * FROM v_ViewName",
but that does the same
thing, that is a full query to the SQL Server on the slightest
provocation.

I also tried changing the RecordSource to "SELECT Col1 FROM
v_ViewName". That changes the query
thats executed on the server to only select the one column, but its
not much help since without
the WHERE clause, the query still takes too long to run.

I would suggest that you create a blank ADP project and create a blank new
form based on this view to see if this behavior will repeat. If not, then
import everything into a blank new ADP project.

I created a new database on the SQL Server, with one simple table, and
a "SELECT *" view on
that table. Then I created a new blank ADP project to that database,
and created a new Form with
the RecordSource = "SELECT View_1.* FROM View_1".
Then when I add a column to the form, Access queries the server with
"SELECT View_1.* FROM View_1"
(as seen in SQL Server Profiler). Its not a problem in this test
scenario because the test table
is almost empty, but when the view or table is huge or takes a long
time to execute it is a
problem, and I don't see why access needs the execute the query just
to add a field to a Form
or Report in Design View.

 

[Sylvain Lafontaine]

And you're sure that this behavior occurs for both Access 2007 *and* 2003 as
well?

 

[jon.letham]

And you're sure that this behavior occurs for both Access 2007 *and* 2003as
well?

Yes.

 

[Sylvain Lafontaine]

Well, it's because I cannot reproduce this behavior with A2003. I will try
later with A2007 - which has some problems of its own - but I never heard of
such a problem with A2003. Have you set the compatibility mode of
SQL-Server to something earlier (aka, 6.5) and the exact text for the
created view (ie, any option like WITH SCHEMABINDING) ? Any trigger,
particularly Instead Of trigger?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

And you're sure that this behavior occurs for both Access 2007 *and* 2003
as
well?

Yes.

 

[jon.letham]

Well, it's because I cannot reproduce this behavior with A2003.  I willtry
later with A2007 - which has some problems of its own - but I never heardof
such a problem with A2003.  Have you set the compatibility mode of
SQL-Server to something earlier (aka, 6.5) and the exact text for the
created view (ie, any option like WITH SCHEMABINDING) ?  Any trigger,
particularly Instead Of trigger?

sp_dbcmptlevel gives "The current compatibility level is 80."

No options on the view, its a straight ...

CREATE VIEW v_viewname
AS
SELECT ...
FROM ...
ORDER BY ...

GO

(I would give you the full SELECT and FROM, but its fairly complex
and would distract from the task at hand. As I said, it happens even
with
the most trivial view based on the most trivial table)

There are INSERT, UPDATE and DELETE triggers on a couple of the
tables in the view, but no "INSTEAD OF" triggers. Obviously, none of
them
should fire on a SELECT, and I know they are not because they would
create
logfile entries in the database, and they are not.

As a further test, I created a new database on SQL Server 2005,
created a
trivial table (Col1 char(10), Col2 char(10), etc) and a new blank
A2007 ADP
connected to it, created a Form, set the RecordSource = "SELECT
dbo.Table_1.*
FROM dbo.Table_1"

It then does a "SELECT dbo.Table_1.* FROM dbo.Table_1" every time I
add
a column to the form, and every time I change the column that the Text
Box
is bound to.

With the full "SELECT" statement in the RecordSource, it does not do a
full
requery of the table/view if you do something as inconsequential as
resizing
or moving the text box, but if the RecordSource is just the Table/View
name
(No SELECT/FROM), then every time the bound control is moved or
resized
it does a full query on the table/view.

 

[jon.letham]

Well, it's because I cannot reproduce this behavior with A2003.  I willtry
later with A2007 - which has some problems of its own - but I never heardof
such a problem with A2003.  

I think I have found a solution/workaround.

1) A2007 was always bad ... I'm not going to develop with A2007 any
more.

2) Using A2003 with a windows account that had only basic permissions
in the SQL Server database it was still bad, but using an account that
has "Server Role" "System Administrators" things were much better.

3) Changing the RecordSource to "SELECT dbo.v_viewname.* FROM
dbo.v_viewname" instead of just v_viewname also appears to help.

I don't exactly recall what happens with other combinations, but doing
all three seems to have done the trick!

Thanks again for your help in looking at this.

 

[Sylvain Lafontaine]

Personally, I had never bound an ADP form or report directly to a table or a
view and I never used something like DoCmd.OpenReport "ReportName",
acViewPreview, , "ID = 1234" to filter them.

I always use either a SP with parameters - BTW, the only way of having
sub-report if I remember correctly - or I set up a full SQL query string
such as "Select * from dbo.Table_1.* FROM dbo.Table_1 where ID=1234"

This later format is quite useful for subforms and controls on subform
because it's diminush the number of queries that ADP is doing against
SQL-Server when you move from record to record on the main form.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Well, it's because I cannot reproduce this behavior with A2003. I will try
later with A2007 - which has some problems of its own - but I never heard
of
such a problem with A2003.

I think I have found a solution/workaround.

1) A2007 was always bad ... I'm not going to develop with A2007 any
more.

2) Using A2003 with a windows account that had only basic permissions
in the SQL Server database it was still bad, but using an account that
has "Server Role" "System Administrators" things were much better.

3) Changing the RecordSource to "SELECT dbo.v_viewname.* FROM
dbo.v_viewname" instead of just v_viewname also appears to help.

I don't exactly recall what happens with other combinations, but doing
all three seems to have done the trick!

Thanks again for your help in looking at this.

 

[Sylvain Lafontaine]

Also, the official newsgroup for ADP is m.p.a.adp.sqlserver.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

I never used something like DoCmd.OpenReport "ReportName",
acViewPreview, , "ID = 1234" to filter them.

I always use either a SP with parameters

Why would you bother with that? Haven't you ever run a SQL trace to
see what actually happens when you do the report filter? Now, I
don't use ADPs, but what I see with ODBC linked tables is that
whatever Jet sends to SQL Server, a generic SP is used to filter the
recordset returned.

Now, maybe ADPs don't do that. If so, that would seem to me to be a
good reason not to use them, since it requires that you do a lot
more work than should be required.

 

[Sylvain Lafontaine]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

Why would you bother with that? Haven't you ever run a SQL trace to
see what actually happens when you do the report filter? Now, I
don't use ADPs, but what I see with ODBC linked tables is that
whatever Jet sends to SQL Server, a generic SP is used to filter the
recordset returned.

Now, maybe ADPs don't do that. If so, that would seem to me to be a
good reason not to use them, since it requires that you do a lot
more work than should be required.

Most of the SP that I'm writing are far more too complex for the limited
capabilities of the JET engine. Your answer is typical of someone who
doesn't really know about the capabilities of the T-SQL language of
SQL-Server and sees it more or less as some other version or color of the
JET engine.

I know a lot of persons who don't know much about SQL-Server and are saying
that the JET engine is sufficient for them. However, I have yet to know a
single person who, after having learn to work with SQL-Server for real and
not only just through the JET engine with its ODBC linked tables and
passthrough queries, has ever go back to the JET engine.

Believe me, you can switch from the use of ODBC linked tables and
passthrough queries to the ADP format or to the .NET platform but after
that, you never travel back.

 

[Larry Linson]

. . . However, I have yet to know a single person
who, after having learn to work with SQL-Server
for real and not only just through the JET engine
with its ODBC linked tables and passthrough
queries, has ever go back to the JET engine.

Well, now you do. I've worked with various server databases, of which MS SQL
Server is only one, and continued to use Jet for those applications where it
was appropriate.

I've worked with MDBs and ODBC and I've worked (albeit not extensively) with
ADPs... the ADPs aren't as burdensome as their strongest detractors lead you
to believe, but neither are they the panacea that their strongest proponents
would lead you to believe. My experience was that the same tasks took
somewhat longer in ADP, but that might have improved had I used them
excusively for a longer period. The biggest problem was that the only server
DB supported was Microsoft SQL Server, and most of those companies wanted to
preserve their option to switch if they decided it would be advantageous
(see below).

In most of the client shops where I worked, only the DBAs wrote stored
procedures, by company rule, and for a "mere developer" to be permitted to
"dink around" with their server database required jumping through some
flaming hoops.

I was permitted to do so, in some cases, but the clients found it easy to
obtain, less expensive, and simpler to contract Access developers than SQL
Server developers and mandated that SPs be used only when absolutely
_necessary_. All those clients with server databases had already chosen one
as their corporate standard and were not about to change that at the behest
of contract developers.

In cases where departments were allowed to hire contractors to create their
applications, some IT departments allowed them to use a different database
than the corporate standard, but at the cost of giving up IT support for
installations, updates, error fixes, etc.. Most departments, faced with that
option, decided to stick with the corporate standard and get support from
the corporate IT department.

Too, they wanted to preserve their option to change server databases, which
meant that "standard SQL" had to be the order of the day... they didn't want
to have to translate T-SQL to someone else's flavor of SQL if they got
ticked-off at Microsoft and decided to move to Informix or DB2 or Oracle (or
vice-versa). Now, I am not aware of any large company who _did_ change their
corporate standard server "just on a whim" but some have done so when they
merged with another company which had a different corporate standard server.
That's when the IT departments learned just which was the 800 pound gorilla
in their corporate "marriage of equals".

So, the "server elitist" view that poor beings who don't spend their time
writing T-SQL stored procedures are some kind of "lesser mortal" just
doesn't ring true to me.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

Most of the SP that I'm writing are far more too complex for the
limited capabilities of the JET engine.

But I wasn't referring to "most of your SPs", only the issue you
mention about not using OpenReport with a filter.

Your answer is typical of someone who
doesn't really know about the capabilities of the T-SQL language
of SQL-Server and sees it more or less as some other version or
color of the JET engine.

Your response is typical of someone who I believe should not use
Access at all, because you're hostile to the capabilities that it
offers and won't make the effort to use Access the way it was
designed to be used.

I know a lot of persons who don't know much about SQL-Server and
are saying that the JET engine is sufficient for them. However, I
have yet to know a single person who, after having learn to work
with SQL-Server for real and not only just through the JET engine
with its ODBC linked tables and passthrough queries, has ever go
back to the JET engine.

Well, meet one -- I have applications using SQL Server as the back
end with ODBC that were ported from Jet back ends. They work very
well and the conversions were quite easy. I didn't need to write a
single SP, though I did have to get creative with a few views.

Believe me, you can switch from the use of ODBC linked tables and
passthrough queries to the ADP format or to the .NET platform but
after that, you never travel back.

I think you should stop using Access entirely.

 

[a a r o n . k e m p f]

I agree-- once you learn a real database-- Jet is pointless

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote innews:[email protected]:

Why would you bother with that? Haven't you ever run a SQL trace to
see what actually happens when you do the report filter? Now, I
don't use ADPs, but what I see with ODBC linked tables is that
whatever Jet sends to SQL Server, a generic SP is used to filter the
recordset returned.

Now, maybe ADPs don't do that. If so, that would seem to me to be a
good reason not to use them, since it requires that you do a lot
more work than should be required.

Most of the SP that I'm writing are far more too complex for the limited
capabilities of the JET engine.  Your answer is typical of someone who
doesn't really know about the capabilities of the T-SQL language of
SQL-Server and sees it more or less as some other version or color of the
JET engine.

I know a lot of persons who don't know much about SQL-Server and are saying
that the JET engine is sufficient for them.  However, I have yet to know a
single person who, after having learn to work with SQL-Server for real and
not only just through the JET engine with its ODBC linked tables and
passthrough queries, has ever go back to the JET engine.

Believe me, you can switch from the use of ODBC linked tables and
passthrough queries to theADPformat or to the .NET platform but after
that, you never travel back.