Re: Translation of SQL for VBA

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

> 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" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:5C13B9B1-F1FA-4928-B183-D6F1A7B0FD3D@xxxxxxxxxxxxxxxx
> > 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)" wrote:
> >
> >> 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" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:AAA8A202-0E7F-4CE1-864D-ACFAB3317B8F@xxxxxxxxxxxxxxxx
> >> >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]));
> >> >
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • RE: Counting of Records in a Before opening a Data Form
    ... First make sure that the query returns records. ... select the criteria. ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • RE: Counting of Records in a Before opening a Data Form
    ... the form "Criteria Building Form" MUST be open. ... Would you post the SQL of the query "Criteria"? ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
    (microsoft.public.access.formscoding)
  • RE: Complex query criteria - desperate appeal
    ... The following will search for query by form: ... > appropriate SQL string if it is not. ... > Dim qdf as DAO.QueryDef ... > criteria in text fields, you would have to modify these somewhat to give the ...
    (microsoft.public.access.queries)
  • Re: Translation of SQL for VBA
    ... Not knowing what the criteria are in the SQL string, ... > Append Query. ... >> Dim strSQL as String ...
    (microsoft.public.access.modulesdaovba)