SQL from VB
 

Hi,
I need to do function or sub, which is able to edit a SELECT in MS QUERY.
For example:

MS QUERY SQL:
select * from dba.company where name='ABC'

EXCEL VB:
function do_sql(X)
select * from dba.company where name=X
end function

What is the right way to do this. Is it possible to use SQL in VB?

Thanx, kamil

SQL from VB
 

Yes it is. The best way to get started is to turn on the macro recorder, run
your MS Query, then edit the macro to your needs. You could post back if you
have any specific problems then.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kamil b." wrote in message
...
Hi,
I need to do function or sub, which is able to edit a SELECT in MS QUERY.
For example:

MS QUERY SQL:
select * from dba.company where name='ABC'

EXCEL VB:
function do_sql(X)
select * from dba.company where name=X
end function

What is the right way to do this. Is it possible to use SQL in VB?

Thanx, kamil

SQL from VB
 

First, you could do this by setting up a parameter in MSQuery linked to your
workbook; if you do not know how to do that then check the Help in MSQuery on
Parameters.

But if you need to do this in code, it is really quite easy. If you have
the query set up already for a particular set of data, the SQL used will be
in the QueryTable.CommandText property. You can modify it easily with code
then refresh your query:

With Worksheets("SheetName").QueryTables(1)
.CommandText = Replace(.CommandText, "'ABC'", "'X'")
.Refresh
End With
--
- K Dales


"kamil b." wrote:

Hi,
I need to do function or sub, which is able to edit a SELECT in MS QUERY.
For example:

MS QUERY SQL:
select * from dba.company where name='ABC'

EXCEL VB:
function do_sql(X)
select * from dba.company where name=X
end function

What is the right way to do this. Is it possible to use SQL in VB?

Thanx, kamil