Translation of SQL for VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have not been able to find a translation of 2 conditions of a query to be
properly coded in VBA. I know this is pretty basic, but I'm struggling. How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
 
What are you doing with the SQL string when it's built? That can affect how
you would handle this.

In general, you would concatenate the actual value from the parameter into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).
 
I'm trying to do it in VBA, so I can change the target table in my append
query, and not have to make 4 or 5 different queries. Instead, I just want
one query, while modifying the criteria and the target table of the Append
Query. Someone gave me a start with:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't know
what it is next time we need it. The Execute method above is the same as
running the query any other way and is about 5 times faster than then RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

So I was just trying to put my SQL string together and was having problems.

Ken Snell (MVP) said:
What are you doing with the SQL string when it's built? That can affect how
you would handle this.

In general, you would concatenate the actual value from the parameter into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).

--

Ken Snell
<MS ACCESS MVP>

Kou Vang said:
I have not been able to find a translation of 2 conditions of a query to be
properly coded in VBA. I know this is pretty basic, but I'm struggling.
How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
Click to expand...
Click to expand...
 
Ahhhhhh... ok, what I gave you isn't going to work directly. Instead, you'll
need to create a new query, evaluate the parameters in the "new" query, and
then execute it.

Let's change your code to this (I've also corrected the code, as TableDefs
do not contain queries):


Dim strSQL as String
Dim dbf as Database
Dim par as DAO.Parameter
Dim qdf As DAO.QueryDef

'Set up object variables
Set dbf = CurrentDb

'Get the SQL string for the query
strSQL = dbf.QueryDefs("MyQueryName").SQL

'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Create temporary query
Set qdf = dbf.CreateQueryDef("", strSQL)

'Evaluate the parameters in the new query
For Each par in qdf.Parameters
par.Value = Eval(par.Name)
Next par

'Runs the append query with the selected table name
qdf.Execute

'Clear object variables
qdf.Close
Set qdf = Nothing
dbf.Close
Set dbf = Nothing

--

Ken Snell
<MS ACCESS MVP>


Kou Vang said:
I'm trying to do it in VBA, so I can change the target table in my append
query, and not have to make 4 or 5 different queries. Instead, I just
want
one query, while modifying the criteria and the target table of the Append
Query. Someone gave me a start with:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't
know
what it is next time we need it. The Execute method above is the same as
running the query any other way and is about 5 times faster than then
RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

So I was just trying to put my SQL string together and was having
problems.

Ken Snell (MVP) said:
What are you doing with the SQL string when it's built? That can affect
how
you would handle this.

In general, you would concatenate the actual value from the parameter
into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).

--

Ken Snell
<MS ACCESS MVP>

Kou Vang said:
I have not been able to find a translation of 2 conditions of a query to
be
properly coded in VBA. I know this is pretty basic, but I'm
struggling.
How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help Ken! I think I'm almost there, this is getting closer to
what I wanted. If I wanted to get into modifying the criteria of the query,
would I then have to write out my SQL line and modify it in my string? I'm
using a form with text and combo boxes as the values in the criteria of the
Append Query. The code you have given me only changes the target table
correct? How do I access the criteria of the query now?

Ken Snell (MVP) said:
Ahhhhhh... ok, what I gave you isn't going to work directly. Instead, you'll
need to create a new query, evaluate the parameters in the "new" query, and
then execute it.

Let's change your code to this (I've also corrected the code, as TableDefs
do not contain queries):


Dim strSQL as String
Dim dbf as Database
Dim par as DAO.Parameter
Dim qdf As DAO.QueryDef

'Set up object variables
Set dbf = CurrentDb

'Get the SQL string for the query
strSQL = dbf.QueryDefs("MyQueryName").SQL

'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Create temporary query
Set qdf = dbf.CreateQueryDef("", strSQL)

'Evaluate the parameters in the new query
For Each par in qdf.Parameters
par.Value = Eval(par.Name)
Next par

'Runs the append query with the selected table name
qdf.Execute

'Clear object variables
qdf.Close
Set qdf = Nothing
dbf.Close
Set dbf = Nothing

--

Ken Snell
<MS ACCESS MVP>


Kou Vang said:
I'm trying to do it in VBA, so I can change the target table in my append
query, and not have to make 4 or 5 different queries. Instead, I just
want
one query, while modifying the criteria and the target table of the Append
Query. Someone gave me a start with:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't
know
what it is next time we need it. The Execute method above is the same as
running the query any other way and is about 5 times faster than then
RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

So I was just trying to put my SQL string together and was having
problems.

Ken Snell (MVP) said:
What are you doing with the SQL string when it's built? That can affect
how
you would handle this.

In general, you would concatenate the actual value from the parameter
into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).

--

Ken Snell
<MS ACCESS MVP>

I have not been able to find a translation of 2 conditions of a query to
be
properly coded in VBA. I know this is pretty basic, but I'm
struggling.
How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
Click to expand...
Click to expand...
Click to expand...
 
Not knowing what the criteria are in the SQL string, my first thought is
that you would modify the criteria similarly to how your posted code
modifies the tablename in the string.
--

Ken Snell
<MS ACCESS MVP>



Kou Vang said:
Thanks for the help Ken! I think I'm almost there, this is getting closer
to
what I wanted. If I wanted to get into modifying the criteria of the
query,
would I then have to write out my SQL line and modify it in my string?
I'm
using a form with text and combo boxes as the values in the criteria of
the
Append Query. The code you have given me only changes the target table
correct? How do I access the criteria of the query now?

Ken Snell (MVP) said:
Ahhhhhh... ok, what I gave you isn't going to work directly. Instead,
you'll
need to create a new query, evaluate the parameters in the "new" query,
and
then execute it.

Let's change your code to this (I've also corrected the code, as
TableDefs
do not contain queries):


Dim strSQL as String
Dim dbf as Database
Dim par as DAO.Parameter
Dim qdf As DAO.QueryDef

'Set up object variables
Set dbf = CurrentDb

'Get the SQL string for the query
strSQL = dbf.QueryDefs("MyQueryName").SQL

'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Create temporary query
Set qdf = dbf.CreateQueryDef("", strSQL)

'Evaluate the parameters in the new query
For Each par in qdf.Parameters
par.Value = Eval(par.Name)
Next par

'Runs the append query with the selected table name
qdf.Execute

'Clear object variables
qdf.Close
Set qdf = Nothing
dbf.Close
Set dbf = Nothing

--

Ken Snell
<MS ACCESS MVP>


Kou Vang said:
I'm trying to do it in VBA, so I can change the target table in my
append
query, and not have to make 4 or 5 different queries. Instead, I just
want
one query, while modifying the criteria and the target table of the
Append
Query. Someone gave me a start with:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't
know
what it is next time we need it. The Execute method above is the same
as
running the query any other way and is about 5 times faster than then
RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

So I was just trying to put my SQL string together and was having
problems.

:

What are you doing with the SQL string when it's built? That can
affect
how
you would handle this.

In general, you would concatenate the actual value from the parameter
into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).

--

Ken Snell
<MS ACCESS MVP>

I have not been able to find a translation of 2 conditions of a query
to
be
properly coded in VBA. I know this is pretty basic, but I'm
struggling.
How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
Click to expand...
Click to expand...
Click to expand...
 
Aaahh...So just add another Replace string.

strSQL=Replace(strSQL, "OldCriteria","NewCriteria")

I can put this below the Replace TableName string. I'll try that, thanks!

Ken Snell (MVP) said:
Not knowing what the criteria are in the SQL string, my first thought is
that you would modify the criteria similarly to how your posted code
modifies the tablename in the string.
--

Ken Snell
<MS ACCESS MVP>



Kou Vang said:
Thanks for the help Ken! I think I'm almost there, this is getting closer
to
what I wanted. If I wanted to get into modifying the criteria of the
query,
would I then have to write out my SQL line and modify it in my string?
I'm
using a form with text and combo boxes as the values in the criteria of
the
Append Query. The code you have given me only changes the target table
correct? How do I access the criteria of the query now?

Ken Snell (MVP) said:
Ahhhhhh... ok, what I gave you isn't going to work directly. Instead,
you'll
need to create a new query, evaluate the parameters in the "new" query,
and
then execute it.

Let's change your code to this (I've also corrected the code, as
TableDefs
do not contain queries):


Dim strSQL as String
Dim dbf as Database
Dim par as DAO.Parameter
Dim qdf As DAO.QueryDef

'Set up object variables
Set dbf = CurrentDb

'Get the SQL string for the query
strSQL = dbf.QueryDefs("MyQueryName").SQL

'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Create temporary query
Set qdf = dbf.CreateQueryDef("", strSQL)

'Evaluate the parameters in the new query
For Each par in qdf.Parameters
par.Value = Eval(par.Name)
Next par

'Runs the append query with the selected table name
qdf.Execute

'Clear object variables
qdf.Close
Set qdf = Nothing
dbf.Close
Set dbf = Nothing

--

Ken Snell
<MS ACCESS MVP>


I'm trying to do it in VBA, so I can change the target table in my
append
query, and not have to make 4 or 5 different queries. Instead, I just
want
one query, while modifying the criteria and the target table of the
Append
Query. Someone gave me a start with:

Dim strSQL as String
Dim dbf as Database
Dim tdfs as TableDefs
Dim tdf as TableDef

'Set up object variables
Set dbf = CurrentDb
Set tdfs = dbf.TableDefs
Set tdf = tdfs("MyQueryName")

'Get the SQL string for the query
strSQL = tdf.SQL
'Replace the old name with the new name in the SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")

'Runs the append query with the selected table name
dbf.Execute(strSQL)

'Note, we don't change the name in the original saved query so we don't
know
what it is next time we need it. The Execute method above is the same
as
running the query any other way and is about 5 times faster than then
RunSql
method.

'Destroy object variables
Set tdf = Nothing
Set tdfs = Nothing
Set dbf = Nothing

So I was just trying to put my SQL string together and was having
problems.

:

What are you doing with the SQL string when it's built? That can
affect
how
you would handle this.

In general, you would concatenate the actual value from the parameter
into
the string:

MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
" AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]

(above assumes that both fields are numeric).

--

Ken Snell
<MS ACCESS MVP>

I have not been able to find a translation of 2 conditions of a query
to
be
properly coded in VBA. I know this is pretty basic, but I'm
struggling.
How
do I say this in my strSQL string in VBA? Thanks.

WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top