Thank you Andrew...
Give me times to try all the technique that you have described, and thank you for all you reply...
soon i'll come again with other problem still about this,,,
Regards
Hendra Agestha
--------------------------------------------
Pada Jum, 21/2/14, yahoo@craven.de <yahoo@craven.de> menulis:
Judul: [MS_AccessPros] RE: Processing Recordset retrieved from Stored Procedure
Kepada: MS_Access_Professionals@yahoogroups.com
Tanggal: Jumat, 21 Februari, 2014, 4:01 AM
Your Welcome Hendra,
1.
qd.SQL =
"EXEC [spGetSalesNotCompleted] " &
Forms!MyForm!txtInputCustomerNr
this works for integers. For Strings:
qd.SQL =
"EXEC [spGetSalesNotCompleted] '" &
Forms!MyForm!txtInputCustomerNr &
"'"
Although here I would recommend using a function to quote
the string, the function will need to make sure that if the
user types an apostrophe that it gets quoted but that can
come later, once you are comfortable with how it should
work. (The function to control what the user enters is,
ultimately a *must* but the subject "SQL
Injection" can be best left to a later date.)
For dates, you will have to take more care, especially so if
any part of the systems are not US English. There are
however many solutions to these problems and which solution
is best for you is dependant on how many languages/Locales
you have to accomodate. If you have to pass dates as
parameters I can give you a few more tips.
2. AS soon as you have a query that gives you the recordset,
you can use this just as you would any other query or table
and design a create-table query around it, completely in
Access without having to care about the fact that the data
is *actually* coming from a SQL Server. I normally create
the Passthrough query by hand at first and then create the
create-table, or table-append query, based on it. The second
query you never need to look at any more, unless you change
the number or type of fields returned by the Stored
Procedure, and even then you just have to open the query and
save the (automatically) changed schema.
I might, however, be misunderstanding your second question
and your stored procedure might just be *doing* something
rather than returning records, if so you can change the SQL
to execute the stored procedure and *then* do a select:
EXECUTE MyStoredProc @lParam1 = 100
SELECT Field1, Field2 FROM MySQLTable WHERE KeyField = 100
One thing that you, or your SQL programmer should, sorry
*has* to, take care of in this scenario is that only one set
of records gets returned and only one count of records
occurs. Generally this implies that the Stored Procedure
does a "SET NOCOUNT ON" at the beginning and a
"SET NOCOUNT OFF" just before doing the SELECT to
return records.
Sorry if I've made things soound complicated, they
aren't. Even if you go for the ADODB solution you might
get unexplicable problems if the routines you are calling
don't follow "best practices".
What *is* you native language? From your name I would tip,
but not bet, on a Scandinavian origin.
Yours,
Andrew "I'm an Englishman in Germ-ny" Craven
(with apologies to Sting)
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar