|
Help Connecting Access 2007 To SQL Server 2005 Express
|
On Friday, May 9th 2008 at 04:17 PM
By BobA |
|
I am looking to figure out several things:
1) When I need a query to act as the source for an updatable form,
should I construct the sql statement in VBA code, adding the selection
criteria and then enter it as the form source?
2) Use a standard query without criteria as the form source and set
the filter in the form def
3) same as above for the report source?
4) If I am wanting to use a nonupdatable query with parameters,
a)What is the syntax of the stored procedure?
b)What is the syntax of the query that calls the stored procedure?
Is this an Access query or a pass through query?
5) I have some multi-layer queries to do summarizations and joins and
so forth to produce data for reports. Are these best replaced as
stored procedures or functions? Need an example.
6) How can I see what is being passed when I execute a form, query or
report so I can evaluate if I have it written correctly for
performance?
Thanks
bob |
Re: Help connecting Access 2007 to SQL Server 2005 Express by Alex Dybenko on Saturday, May 10th 2008 at 12:20 AM
|
> 1) When I need a query to act as the source for an updatable form,
> should I construct the sql statement in VBA code, adding the selection
> criteria and then enter it as the form source?
yes
> 2) Use a standard query without criteria as the form source and set
> the filter in the form def
yes, this is a second option
> 3) same as above for the report source?
for report you need a read-only query, so better to use pass-through queries
> 4) If I am wanting to use a nonupdatable query with parameters,
> a)What is the syntax of the stored procedure?
> b)What is the syntax of the query that calls the stored procedure?
> Is this an Access query or a pass through query?
pass-through queries
> 5) I have some multi-layer queries to do summarizations and joins and
> so forth to produce data for reports. Are these best replaced as
> stored procedures or functions? Need an example.
yes, could be SP or tsql functions, called by pass-through queries. see
books online for samples
> 6) How can I see what is being passed when I execute a form, query or
> report so I can evaluate if I have it written correctly for
> performance?
perhaps SQL profiler can help you here
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com |
|
|
Tutorials
Code
Knowledge Base
News
Forums
Pictures
Videos
Bargains
Icons
Newsgroups
PageRank™ Checker
Alexa Rank Checker
Domain-Name Lookup
Bargains RSS Feed
View More Magazines
View More Downloads
