|
|
I split an Access 2003 mdb to access front end/sql server back end. I got
everything to work, but some forms are slow when remote users are trying to
access them. I want to optimize the Access database to make better use of Sql
Server. I don't fully understand the Stored Procedure concept and pass
through queries. I was able to create a pass through query, but don't
understand how can you pass parameters from the input forms to these. The
record source for these forms is coded in VBA (using DAO) based on selections
on the form. Can anyone direct me to where I can get some information on
this? Links or books or classes (Phila area).
I just read a whole book on upsizing from Access to SQL Server and it said
to turn the mdb into adp. I was ready to start this today, but after reading
more about mdb's and adp's in this site, I'm thinking that this isn't the
best way to go.
|
|
hi,
Crossh wrote:
[Quoted Text] > I split an Access 2003 mdb to access front end/sql server back end. I got
> everything to work, but some forms are slow when remote users are trying to
> access them. I want to optimize the Access database to make better use of Sql
> Server.
Just switching to SQL Server as backend doesn't speed up your application.
First of all:
You need a completly normalized data model.
After that:
Use views instead of queries (SELECT queries). You can link them as
tables. Use them as recordsource for your forms and reports.
Check if you have all necessary indices on the server.
> I don't fully understand the Stored Procedure concept and pass
> through queries.
You use them as a replacement for complex SELECT queries or for UPDATE
or INSERT queries. They normally implement your business logic.
> I just read a whole book on upsizing from Access to SQL Server and it said
> to turn the mdb into adp. I was ready to start this today, but after reading
> more about mdb's and adp's in this site, I'm thinking that this isn't the
> best way to go.
Stay with your .mdb. cause you know it. You may switch later to an .adp.
mfG
--> stefan <--
|
|
You will often hear about the superiority of using ODBC linked tables
against other types of clients/frontend. All these discussions are similar
to the concept of discussing the superiority of an automobile with an
automatic transmission against a big truck with its 18 manual speeds. Many
will tell you that they have a very big family with multiple complicated
paths to travel each days and they feel absolutely no need for any kind of
truck, much less one with 18 speeds but other will tell you that they have
to transport 40000 pounds of milk each day.
In your case, you are probably in the situation where you begin to feel that
it would be a good idea to add a trailer to your automobile. When you start
to be in such a situation, you'll see that any discussion that you will read
in newsgroup about MDB versus ADP and later versus .NET will become more and
more pointless each day.
First, when you are working against a SQL-Server back end - and whatever the
solution that you will choose to use in the short term - you must begin to
learn about SQL-Server; including but not limited to T-SQL, stored
procedures, User Defined Functions (UDF), views, triggers, transactions,
isolation levels and locking. Without such a basic knowledge about
SQL-Server, you will never get higher than primary school and until you get
this basic knowledge, discussion of anything beyond the use of ODBC linked
tables - even when it's simple as the use of a passthrough query - is
probably beyond your current grasp. (This beeing said without beeing rude
at all: before a learned a basic knowledge of SQL-Server, I was in exactly
the same situation as you and using ODBC linked tables is *not* having a
basic knowledge of SQL-Server.).
As to your problem, passthrough queries are read-only, so they might not be
your solution if you want to have updatable bound forms (but they could be
used with read-only forms, unbound forms or strict VBA code). To pass
parameters using a passthrough query, you must change the sql string each
time. As indicated in the message that you have multi-posted from A.
Kallal:
dim rst as DAO.recordset
dim qdf as DAO.QueryDef
Set qdf = currentdb.QueryDefs("qryExecuteMySP")
qdf.SQL = "exec MySP " & FirstParameter & ", " & SecondParameter .......
qdf.execute
If your parameter is a string or a date instead of a number, you must
enclose its value between single quotes. For any embedded single quotes
inside a string, replace them with two single quotes.
Finally, please use crossposting (single message with many nesgroups, in OE,
click on the word Newsgroups at the left in the header to add other
newsgroups) instead of multiposting (multiple copies of the same message,
each with one newsgroup)
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Crossh" <Crossh[ at ]discussions.microsoft.com> wrote in message
news:3D7C4050-A1AF-41F5-A913-8966BF783187[ at ]microsoft.com...
[Quoted Text] >I split an Access 2003 mdb to access front end/sql server back end. I got
> everything to work, but some forms are slow when remote users are trying
> to
> access them. I want to optimize the Access database to make better use of
> Sql
> Server. I don't fully understand the Stored Procedure concept and pass
> through queries. I was able to create a pass through query, but don't
> understand how can you pass parameters from the input forms to these. The
> record source for these forms is coded in VBA (using DAO) based on
> selections
> on the form. Can anyone direct me to where I can get some information on
> this? Links or books or classes (Phila area).
>
> I just read a whole book on upsizing from Access to SQL Server and it said
> to turn the mdb into adp. I was ready to start this today, but after
> reading
> more about mdb's and adp's in this site, I'm thinking that this isn't the
> best way to go.
>
|
|
"Sylvain Lafontaine" wrote:
[Quoted Text] >
> dim rst as DAO.recordset
> dim qdf as DAO.QueryDef
> Set qdf = currentdb.QueryDefs("qryExecuteMySP")
> qdf.SQL = "exec MySP " & FirstParameter & ", " & SecondParameter .......
> qdf.execute
>
Are you saying that converting all read-only Queries & SQL Select statements
to stored Pass-Through Queries will improve performance? I tried this on a
few forms and did not notice any difference.
How do you pass parameters to Views that are linked as ODBC tables?
|
|
« Are you saying that converting all read-only Queries & SQL Select
statements to stored Pass-Through Queries will improve performance? I tried
this on a few forms and did not notice any difference. »
Not at all. For most (but not all) simple Select queries that you write
against ODBC linked tables; JET will be able to translate and pass them
directly to SQL-Server, so you won't see any difference of speed at all.
However, if things become a little more complicated - for example, you begin
to add VBA functions in the FROM or the WHERE statements or JET is not able
to translate the query then the performance will drop like a ball because
JET will have to retrieve many more rows than necessary from the SQL-Server.
To see what's really happening, you should use the SQL Profiler in order to
see what JET is doing against the SQL-Server.
You cannot pass parameters to Views; you use them in your select queries as
if they were standard tables but you must take some precaution if you want
them to be updatable; see:
http://support.microsoft.com/kb/q209123/
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Crossh" <Crossh[ at ]discussions.microsoft.com> wrote in message
news:86ABB16F-21E0-4601-A0B7-D93157580163[ at ]microsoft.com...
[Quoted Text] > "Sylvain Lafontaine" wrote:
>>
>> dim rst as DAO.recordset
>> dim qdf as DAO.QueryDef
>> Set qdf = currentdb.QueryDefs("qryExecuteMySP")
>> qdf.SQL = "exec MySP " & FirstParameter & ", " & SecondParameter .......
>> qdf.execute
>>
>
> Are you saying that converting all read-only Queries & SQL Select
> statements
> to stored Pass-Through Queries will improve performance? I tried this on a
> few forms and did not notice any difference.
>
> How do you pass parameters to Views that are linked as ODBC tables?
|
|
|