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?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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

It's possible. Visit this very handy site...

http://j-walk.com/ss/excel/tips/tip96.htm


-----Original Message-----
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
.

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)

"Serkan" wrote in message
...
It's possible. Visit this very handy site...

http://j-walk.com/ss/excel/tips/tip96.htm


-----Original Message-----
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
.

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')}

--

"Frank Kabel" wrote in message ...
Hi Charles
why don't you just build the SQL string (using text concatenation) as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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

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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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

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 Kabel" wrote in message
...
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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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

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.

--

"Frank Kabel" wrote in message ...
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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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 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.

--

"Bob Phillips" wrote in message ...
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 Kabel" wrote in message
...
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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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 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)

"onedaywhen" wrote in message
m...
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.

--

"Bob Phillips" wrote in message
...
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 Kabel" wrote in message
...
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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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

Jamie,

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

Bob

"onedaywhen" wrote in message
om...
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.

--

"Frank Kabel" wrote in message
...
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
:-)

--
Regards
Frank Kabel
Frankfurt, Germany

onedaywhen wrote:
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')}


"Frank Kabel" wrote in message
...
Hi Charles
why don't you just build the SQL string (using text concatenation)
as
pass this string to the module / SQL Querey?

--
Regards
Frank Kabel
Frankfurt, Germany

Charles wrote:
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