[MS_AccessPros] Re: Indexes

 

John-

Ah! that makes perfect sense. Seems like I used to know that. [smile]

Bill

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Bill & Bill-
>
>
>
> When you use .Execute, you're invoking the JET (ACE) engine directly. The call
> could be coming from anywhere - a VB application or Word or Excel. The engine
> has no visibility to the interface from which it was called, so you get an
> unresolved parameter. It works running queries in the UI in Access because
> Access does the parameter substitution for you before calling the database
> engine.
>
>
>
> John Viescas, author
>
> Microsoft Office Access 2010 Inside Out
>
> Microsoft Office Access 2007 Inside Out
>
> Building Microsoft Access Applications
>
> Microsoft Office Access 2003 Inside Out
>
> SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Monday, May 21, 2012 5:10 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Indexes
>
>
>
>
>
> Bill
>
> The big rule when using parameters in VBA SQL strings is to use them as
> variables. Parameters have to be written into the compiled query. VBA strings
> are interpretted differently than JET statements so you have to concatenate
> their values.
>
> It's something that I've never understood because I don't know the way JET
> works. Maybe John can shed some light on that.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
> <Bill.Singer@> wrote:
> >
> > Duane,
> >
> > Thanks, My remaining hair follicles thank you. I woke up this morning at
> > 4:30 thinking if it worked in a new query and not in my code it must be the
> > formatting marks. I would have never found that on my own. But I did
> > learn a lot. It is usually how I learn best. Jump in with both feet and
> > then try to figure out how to swim.
> >
> >
> >
> > My last question and hopefully this thread will be closed. Why do I have to
> > keep that reference to a form outside the quotations? What are the
> > quotations doing? I was wondering why they are in this code and not in the
> > SQL view of a query. Access must see things inside the quotes and outside
> > the quotes differently.
> >
> >
> >
> > Bill Singer
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane
> > Sent: Sunday, May 20, 2012 11:31 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Re: Indexes
> >
> >
> >
> >
> >
> > Bill,
> > Here is your code:
> > strSQL = "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID, fscSchDate,
> > fscSchStartTime, fscEveID )" & _
> > "SELECT hlpLocDay1, hlpSpoID, hlpDay1Date, hlpGame1Time, 1 " & _
> > "FROM t_ScheduleHelper " & _
> > "WHERE hlpHlpID=[Forms]![f_8TeamSeasonSchedule]![hlpHlpID]"
> >
> > and here is my earlier suggestion:
> > 'assuming hlpHlpID is numeric
> > strSQL = "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID, fscSchDate,
> > fscSchStartTime, fscEveID ) " & _
> > "SELECT hlpLocDay1, hlpSpoID, hlpDay1Date, hlpGame1Time, 1 " & _
> > "FROM t_ScheduleHelper " & _
> > "WHERE hlpHlpID=" & [Forms]![f_8TeamSeasonSchedule]![hlpHlpID]
> >
> > Find the difference to resolve your parameter issue.
> >
> > Duane Hookom
> > MS Access MVP
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "agent1of6"
> > <Bill.Singer@> wrote:
> > >
> > > Duane/John
> > >
> > > An exercise in Frustration. I have learned some great tricks but it still
> > is very frustrating.
> > >
> > > I believe I found the missing spaces. I followed the procedure that both
> > of you suggested but the SQL view does not have quotes and spaces. The
> > spaces fixed my error 3067, must contain one table.
> > >
> > > Once I got the spaces fixed and one more error fixed I am now back to the
> > error 3061, too few parameters, Expected1. As a way to further trouble shoot
> > as suggeste by an Allen Board I copied everything from the results in the
> > immediate window and pasted it in the sql view of a new query. I selected
> > datasheet view and it worked perfectly. I do not get that. The exact
> > language in the VB window will not run. I would pull my hair but my
> > follicles are leaving me too fast the way it is. I have tried a few things
> > but that lead to other errors. I have checked the spelling and find no
> > errors. I check my books but no help.
> > >
> > > Any idea what error 3061, too few parameters, Expected1 means?
> > >
> > > Here is the code.
> > >
> > > Private Sub Command155_Click()
> > > Dim intTrans As Integer, db As DAO.Database
> > > Dim strSQL As String
> > >
> > > On Error GoTo MyErrorTrap
> > >
> > > Set db = CurrentDb
> > >
> > > ' Tell error trap we're starting a transaction
> > > intTrans = True
> > > ' Start the transaction
> > > BeginTrans
> > >
> > > ' Run the queries
> > >
> > > strSQL = "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID, fscSchDate,
> > fscSchStartTime, fscEveID )" & _
> > > "SELECT hlpLocDay1, hlpSpoID, hlpDay1Date, hlpGame1Time, 1 " & _
> > > "FROM t_ScheduleHelper " & _
> > > "WHERE hlpHlpID=[Forms]![f_8TeamSeasonSchedule]![hlpHlpID]"
> > >
> > > Debug.Print strSQL
> > >
> > > db.Execute strSQL, dbFailOnError
> > >
> > > ' Commit the transaction
> > > CommitTrans
> > > ' Turn off the flag
> > > intTrans = False
> > >
> > > MyExit:
> > > Exit Sub
> > >
> > > MyErrorTrap:
> > > ' Display the error (You can also test the error # and display a custom
> > msg)
> > > MsgBox "Unexpected error: " & Err & ", " & Error
> > > ' If transaction in progress, roll it back
> > > If intTrans Then Rollback
> > > ' Turn off the flag so we don't try to rollback again
> > > intTrans = False
> > > ' Bail
> > > Resume MyExit
> > >
> > > End Sub
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Duane" <duanehookom@>
> > wrote:
> > > >
> > > > Viewing the SQL statement in the debug/immediate window might be enough
> > to determine the issue with your syntax. If not, copy the statement to your
> > clipboard, open a new query design, view its SQL, and paste the SQL
> > statement. This will likely provide a better view of your issue.
> > > >
> > > > Duane Hookom
> > > > MS Access MVP
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , Bill.Singer@ wrote:
> > > > >
> > > > > Duane,
> > > > > Now the Immediate window is active. That is nice. Thank you for the
> > tip. I was reading up on the things you can do in the Immediate window. It
> > now shows my SQL in one long line. I have tried to follow the models I have
> > found but nothing is working yet. I will continue my learning (efforts)
> > later today.
> > > > >
> > > > > Thank you for the help.
> > > > > Bill
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: Duane [mailto:duanehookom@]
> > > > > Sent: Sunday, May 20, 2012 12:05 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > Subject: [MS_AccessPros] Re: Indexes
> > > > >
> > > > > Bill,
> > > > >
> > > > > The lightening seems to have let up on this side of town.
> > > > >
> > > > > This is one of the main reasons while us veterans typically create a
> > strSQL to store the SQL statement and then use DeBug.Print strSQL to find
> > any syntax issues. We always do this when we get an error like this.
> > > > >
> > > > > When you follow this advice, you will find you are missing at the end
> > of each line:
> > > > >
> > > > > Dim strSQL as String
> > > > >
> > > > > 'assuming hlpHlpID is numeric
> > > > > strSQL = "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID,
> > fscSchDate, fscSchStartTime, fscEveID ) " & _
> > > > > "SELECT hlpLocDay1, hlpSpoID, hlpDay1Date, hlpGame1Time, 1 " & _
> > > > > "FROM t_ScheduleHelper " & _
> > > > > "WHERE hlpHlpID=" & [Forms]![f_8TeamSeasonSchedule]![hlpHlpID]
> > > > >
> > > > > debug.Print strSQL
> > > > >
> > > > > db.Execute strSQL, dbFailOnError
> > > > >
> > > > > Duane Hookom
> > > > > MS Access MVP
> > > > >
> > > > > --- In mailto:MS_Access_Professionals%40yahoogroups.com, "agent1of6"
> > <Bill.Singer@> wrote:
> > > > > >
> > > > > > John,
> > > > > > Well I have moved from one error to another. I think that is
> > progress. I am now getting Error 3067, Query input must contain at least one
> > Table. I have researched it on the web but had not found any clear solutions
> > and now I have a lightening storm coming so I am shutting down for a while.
> > Maybe I am missing some [] or a Dim.
> > > > > >
> > > > > > I am attempting to get one of the three append queries to work in
> > the code before I put all 3 in.
> > > > > >
> > > > > > Here is the code, you will recogonize most of it.
> > > > > >
> > > > > >
> > > > > > Private Sub Command155_Click()
> > > > > > Dim intTrans As Integer, db As DAO.Database
> > > > > >
> > > > > >
> > > > > > On Error GoTo MyErrorTrap
> > > > > >
> > > > > > Set db = CurrentDb
> > > > > >
> > > > > > ' Tell error trap we're starting a transaction
> > > > > > intTrans = True
> > > > > > ' Start the transaction
> > > > > > BeginTrans
> > > > > >
> > > > > > ' Run the queries
> > > > > >
> > > > > > db.Execute "INSERT INTO t_FacilitySchedule ( fscFacID, fscSpoID,
> > fscSchDate, fscSchStartTime, fscEveID )" & _
> > > > > > "SELECT t_ScheduleHelper.hlpLocDay1, t_ScheduleHelper.hlpSpoID,
> > t_ScheduleHelper.hlpDay1Date, t_ScheduleHelper.hlpGame1Time, 1 AS Event" & _
> > > > > > "FROM t_ScheduleHelper" & _
> > > > > > "WHERE
> > (((t_ScheduleHelper.hlpHlpID)=[Forms]![f_8TeamSeasonSchedule]![hlpHlpID]));"
> > , dbFailOnError
> > > > > >
> > > > > >
> > > > > > ' db.Execute "aq_EventPosting2Visitor", dbFailOnError
> > > > > >
> > > > > > ' Commit the transaction
> > > > > > CommitTrans
> > > > > > ' Turn off the flag
> > > > > > intTrans = False
> > > > > >
> > > > > > MyExit:
> > > > > > Exit Sub
> > > > > >
> > > > > > MyErrorTrap:
> > > > > > ' Display the error (You can also test the error # and display a
> > custom msg)
> > > > > > MsgBox "Unexpected error: " & Err & ", " & Error
> > > > > > ' If transaction in progress, roll it back
> > > > > > If intTrans Then Rollback
> > > > > > ' Turn off the flag so we don't try to rollback again
> > > > > > intTrans = False
> > > > > > ' Bail
> > > > > > Resume MyExit
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > >
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (29)

Recent Activity:

• New Members 4
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___