Access VBA SQL select via ADO with parameters

Hi,

Can anyone tell me if it's possible to use parameters within the ADO code.

For instance:


PARAM1 = "ClientName"
PARAM2 = "="
PARAM3 = "'Acme'"



SQL = _
    "SELECT [ClientName] FROM [dbo].[Clients] ORDER BY [ClientName] WHERE PARAM1 PARAM2 PARAM3"
 
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
 
With cn
    .ConnectionString = ConnString
    .Open
End With
 
With cmd
    .CommandText = SQL
    .ActiveConnection = cn

End With
 
Set rs = cmd.Execute
LVL 2
lee_jdAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Try this.
SQL = _
    "SELECT [ClientName] FROM [dbo].[Clients] ORDER BY [ClientName] WHERE [" & PARAM1 & "] " & PARAM2 & " " & PARAM3

Open in new window

0
 
lee_jdAuthor Commented:
I know how to do this by building the string.  I was hoping there was a built-in way to do it with parameters.  I imagine it's possible.
0
 
NorieVBA ExpertCommented:
I don't think it's possible.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
lee_jd,

If you believe this is possible, then what might this look like as a solution...?

I am curious as to how imnorie's syntax could be done via some simpler "built-in way"?

JeffCoachman
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Did you try imnorie approach.  The SQL evaluates to:

SELECT [ClientName] FROM [dbo].[Clients] ORDER BY [ClientName] WHERE [ClientName] = 'Acme'

mx
0
 
lee_jdAuthor Commented:
The reasoning behind my logic is that if I push a query to a SQL server proc via ADO I can add parameters easily.  I was hoping I could parameterise the predicate.  Not to worry if it can't be done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.