Re: access 2007 really slow

I tried adding "dbo.StoredProcedureName" After this change the access
application errors out with Run-time error - Procedure or function
"StoreProcedureName" expects parameter '@showID', which was not supplied. In
debug mode, I verified the parameter had a value. The showiID is set to a
public variable that is defined as
a variant and is a GUID from the shows table. This value always gets set.

I am not sure what you mean that the problem could be a name resolution?
(Sorry, I am getting into new territory) I Googled Name resolution and one
source mentions Name resoultion is used to find a lower level address (such
as an IP address)
that corresponds to a given higher level address such as hostname. Is this
what you are talking about?

Sorry about the vagueness comment about the SQL Profiler (again new to SQL
Profiler - first time I used it was last Friday) I looked at it again this
morning and they match for the first 61 lines. After that they are slightly
different.
The end user has an extra exec sp_Prepare and exec sp_unprepare commands
compared to the administrator profile. this happens a couple of times. I
started looking into what these stored procedures do (I looked at BOL) and
they were not listed, I googled searched and did not find an explaination of
what they do.

Any help would be greatly appreciated.
Toni




"Paul Shapiro" wrote:

For your VBA code executing the stored proc, use:
objCmd.CommandText = "dbo.StoredProcedureName"

The fact that SQL profiler shows no difference and it works fine for a db
owner suggests a problem with name resolution. You also weren't 100% clear
that there was NO difference in SQL Profiler. You need to be more certain
than "I did not really notice anything different between the two traces".
Take the time to identify the smallest test that shows a performance
difference, and then compare the SQL profiler traces completely to see if
there is ANY difference. If the traces are too long to compare manually, you
can copy them into separate Word documents and let Word compare the two
documents. Eliminate unnecessary columns from the comparison (like start/end
time, username, etc. that will necessarily be different), maybe by pasting
into Word tables or going through Excel if you can't remove the fields in
profiler.

"Toni" <Toni@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4854414-3ACB-465E-A2D3-BFDFE42DB330@xxxxxxxxxxxxxxxx
We are using dbo, not mixed. I made sure the startup form and the form
that
I am dealing with in this application has the record source qualifier to
be
dbo
I also made sure all references to the tables have a prefix of dbo (w/i
the
VBA code that was written, there were only a few select statements) No
views
on this form, but there is one stored procedure and I was not sure how to
specifiy dbo for it. Below is what I have.

' set CommandText to Stored procedure name
objCmd.CommandText = "StoredProcedureName"
objCmd.CommandType = adCmdStoredProc

' Connect to the data source
Set objConn = CurrentProject.Connection
objCmd.ActiveConnection = objConn

' set stroed procedurer parmaters
objCmd.Parameters("@SID") = pubSID
objCmd.Parameters("@MemID") = pubMemID
objCmd.Parameters("@FirstName") =
Form_subfrm_DealerDealerPersons.TxtFirstName.Value
objCmd.Parameters("@LastName") =
Form_subfrm_DealerDealerPersons.TxtLastName.Value
objCmd.Execute

I also ran SQL Server Profileer while I did the test as administrator and
again as the end user. I did not really notice anything different between
the two traces.

Thank you for the suggestions... I am afraid that we may have to install
access 2003 for the end users until this issue gets resolved....

Toni


"Sylvain Lafontaine" wrote:

Hum, the fact that you don't have any problem while logged as an
administrator make me unsure that your situation is related to the ADP
2007
current problem of excessive number of requests about the structure of
the
database made by ADP 2007.

You should take a look with the SQL-Server Profiler to see what's really
going on with your ADP.

Are you using a mix of owners (dbo + some other users) or if all the
objects
belongs to dbo and also, in the second case, did you took the precaution
of
specifying dbo. for all references to views, tables and SP in your
queries
and SP? Finally, did you also took the precaution of setting dbo as the
Record Source Qualifier under the Data tabl of all your forms and
reports?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"Toni" <Toni@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7142A28E-1CA7-4997-B5AC-3706D15AAFBD@xxxxxxxxxxxxxxxx

Yes I also have read several complaints about the slowness of .adp
What
puzzles me is why I do not experience this problem while logged on as
administrator.

After I posted this question we installed SP2 (12.06423.1000 SP2 - MSO
12.0.6425.1000) on one of our machines and I ran the same tests - log
on
as
end user problem (slow connecting slow when typing in data), log on as
administrator - ok. The access application is connected to is fairly
small
DB, just over 1 gigabyte.
The form that I am testing has a sub form that prompt for first name
and
last name only. When I type in 3 records -Test One, Test Two, Test
three,
it can not keep up (I am not a fast typist). The tables that this
form
is
dealing with has around 2850 records. Let me know if you need
additional
information.

"Sylvain Lafontaine" wrote:

They have been many complaints in the past regarding the lack of speed
of
ADP 2007 and I don't use it myself personally exactly because of that.
Some
people have reported an improvement with SP1 but I never tested it
personally. It's interesting to see that you don't have any problem
when
you are logged as an administrator. Could you give us some more
details?

SP2 for Office 2007 is out, so you should give it a try:

http://support.microsoft.com/kb/953195

http://www.microsoft.com:80/downloads/details.aspx?FamilyID=b444bf18-79ea-46c6-8a81-9db49b4ab6e5&displaylang=en

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and
SQL-Server
(French)


"Toni" <Toni@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF0CA67D-6E0B-4B34-8999-2E6B9DACEDA0@xxxxxxxxxxxxxxxx
We recently upgraded from access 2003 to access 2007 and SQL Server
2000
to
SQL Server 2008 Standard edition. We use ODBC to connect to the
backend,
a
system DSN was created with a SQL Server driver.

I noticed our adp access apps are really slow, can not keep up with
the
end
user typing a name into a text box. I have done the following tests
on
2
different machines. One machine has access 2003 and the other
mahcine
has
access 2007. The machine with access 2003 is fine (I logged on and
tested
the application as the end user and as adminisstrator) The machine
with
access 2007 I logged on as administrator and the application was
fine,
I
then
logged on as the end user and the application was real slow. I
then
gave
the end user full control on the C: program files - Microsoft office
folder
to see if that would make a difference, that did not. Does anyone
have
any
suggestions on what else I can try?


.

Relevant Pages

  • Re: access 2007 really slow
    ... The fact that SQL profiler shows no difference and it works fine for a db owner suggests a problem with name resolution. ... I also made sure all references to the tables have a prefix of dbo (w/i the ... objCmd.CommandText = "StoredProcedureName" ... administrator make me unsure that your situation is related to the ADP 2007 ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Tracking Data Changes
    ... > inserts, deletes, and updates. ... Using SQL Profiler doesn't really do what ... > I thought about using triggers and the inserted/deleted tables to move the ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures Data view webparts
    ... and diagnose with SQL profiler. ... Got another issue though i cannot filter the contents? ... have these problems with stored procedures and filtering the data. ...
    (microsoft.public.sharepoint.portalserver)
  • Tracking Data Changes
    ... inserts, deletes, and updates. ... Using SQL Profiler doesn't really do what I ... I thought about using triggers and the inserted/deleted tables to move the ...
    (microsoft.public.sqlserver.programming)
  • RE: Unable toconnect to database
    ... Hi, I ran profiler, did 2 things, in VS I clicked on "Shpow project portal", ... Now, if you open with tfsservice account, this account ... SQL Profiler doesn't capture any error. ...
    (microsoft.public.vsnet.general)