1) Play around A LOT with the QUERY BUILDER
2) I always use the QUERY BUILDER to actually craft my SQL statements just
to save time. Its just a matter of displaying the SQL Statement and then copy
and past. *BUT* if you go the route of imbeding your SQL Statement in code,
it will not run as efficiency as a SQL statement saved as a QUERY. When
Access executes a QUERY, the JET Engine optimizes it figuring out how best to
execute it. When the SQL statement is in code, JET has to optimize it each
and every time you execute it. Depending on the circumstances, you may want
to have one SQL Statement saved as a query and another in code. If its saved
as a QUERY, then you get efficiency but at the risk that a user might tamper
with it (if you don't have security turned on). If its a SQL Statement, you
sacrifice efficiency for increasing the probability that it won't be tampered
with (especially if you create a .MDE file for the front end). Keep in mind
though that most of the time the efficiency factor won't be an issue given
the typical speed of laptops/desktops, the probability that you're NOT
executing a complicated query, and the probability that you're working with a
relatively small database.
"Del" wrote:
> Thank you.
>
> I am self-taught in VBA and I have many gaps in my knowledge. One of them
> was just filled in by your code. I was not aware of 'AS Expr' before. As
> you can imagine, it was limiting.
>
> Thanks again,
> --
> Thank you,
> Del
>
>
> "dch3" wrote:
>
> > DoCmd.RunSQL command can be used to execute a SQL Statement. Since the SQL
> > Statement is comprised of text you can concateonate(sp) the statement as
> > neccessary to insert whatever values you need.
> >
> > Such as
> >
> > strSQL = "INSERT INTO tblTrailerActivityHeaders (
> > lngTrailerLoadLocation, txtTrailerLoadStatus, txtTrailerDispatchStatus,
> > dteTrailerLoadDate, txtTrailerDOTNumber, dblControlId ) SELECT " &
> > Me.pg2_cboTrailerLoadLocation & " AS Expr6, 'Loading' AS Expr5, 'Not Ready'
> > AS Expr4, #" & Date & "# AS Expr3, " & "'" & Me.pg1_lstSelectTrailer & "' AS
> > Expr2, " & dblControlValue & " AS Expr1;"
> >
> > Or you can use DAO to insert the records directly as in...
> >
> > Set db = dbEngine(0)(0)
> > Set rs = db.OpenRecordset("tblTrailerActivityDetailShows")
> > rs.AddNew
> > rs("lngTrailerActivityHeaderId") = lngExistingHeader
> > rs("txtShowNumber") = Me.pg2_cboSelectShow
> > varNewRecordId = rs("lngTrailerActivityDetailId")
> > rs.Update
> > rs.Close
> > Set rs = Nothing
> > Set db = Nothing
> >
> > (The code samples are working with two completely different tables and are
> > not examples of how to do the same task with the same table. But the
> > principle is the same.)
> >
> > "Del" wrote:
> >
> > > I need to record several variables into a new record of a table. The table
> > > is in the same database but not the Record Source for the form that the code
> > > is associated with.
> > >
> > > The problem I need help with is creating the new record. Once the record
> > > exists I can put the data into it.
> > > --
> > > Thank you,
> > > Del
> > >
> > >
> > > "Del" wrote:
> > >
> > > >
> > > > --
> > > > Thank you,
> > > > Del
|
Similar Threads
