Replacing Text In VBProject Module

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hi is it possible to use vba code to change text in a
module at run time I am using Excel XP, it will be in the
same file. if any body can guide in the right direction it
will much appreciated.

I am trying to write a sql statment to query a table in
MSAccess but the user can select from three Comboboxes, I
wanted to add the Where string at run time to the code.

TIA
Charles
 
Hi Charles
why don't you just build the SQL string (using text concatenation) as
pass this string to the module / SQL Querey?
 
Charles,

Ignore this advice and follow Frank's suggestion. It is more direct,
simpler, and more appropriate.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Rather than create dynamic SQL (which is a bit evil), create a 'stored
procedure' in your database and pass the values from your combo boxes
to the procedure as input parameters. Here's an example which takes
two dates as parameters:

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

If you are using ADO, use a command object to create the parameters
and invoke the stored proc. If you are using MS Query, here's what
should appear in the SQL window to run the above procedure with
parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}

--
 
O.K. I agree this is the way in respect to performance. But if the OP
does not want to struggle with Stored Procedures dynamic SQL would do
:-)
 
What you suggested is not dynamic SQL, but dynamically constructing an SQL
string. Dynamic SQL is something completely different and is evil.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Frank, Funny, I though the OP *is* struggling with dynamic SQL ;-)

Actually, I was trying to simply things for the OP. Creating a stored
procedure can be as simple as executing a CREATE PROCEDURE statement,
and hopefully mine is a useful template. Using a stored procedure to
me seems analogous with using a VBA function with arguments i.e.
encapsulates functionality and hides complexity from the user; they
pass in the parameters and get back some values.

--
 
Hi Bob,
I admit I may be incorrectly lumping 'constructing a sql string on the
fly' in with dynamic SQL (I was hedging by say 'a bit evil'!) Could
clarify your understanding of the difference?

A quick google search turned up this definition, which seems typical:
'Dynamic SQL means SQL statements are not prewritten into your
programs; instead, they are constructed at run time as character
strings and then passed to the SQL engine for execution.'

Jamie.

--
 
Hi Jamie,

I agree with the definition of Dynamic SQL as you state, and that to my mind
is a maintenance nightmare, and thus is evil (and I don't care if the
performance is better, it is evil).

What Frank was suggesting, as I understand it, was basically to have a
skeletal SQL statement, something like 'Select Name,
Addline1,Addline2,PostCode From Customer Where custId = <variable>', and
within the presentational or application layer (in my parlance, VBA is the
probably both here, although Excel itself may be the presentation layer) the
statement would be dynamically constructed in the application layer (VBA)
before passing to the DB server. Thus, the DB server only acts upon the SQL
passed to it, there is no run time construction within the DB Server. To my
mind, this is perfectly appropriate way to issue an SQL statement, and is
akin to calling an SP with parameters (although I think you will agree, this
is the best way to go).

I think that in my response. I should have said, (new words in upper case)
'...What you suggested is not dynamic SQL, but dynamically constructing an
SQL
string WITHIN THE APPLICATION LAYER (or WITHIN VBA). Dynamic SQL is
something completely different and is evil... ' and my comment might have
been clearer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Jamie,

Just seen this after replying to your other post. I think we are violently
agreeing here<vbg>

Bob
 
Back
Top