Senin, 21 Mei 2012

RE: [MS_AccessPros] Re: Indexes

 

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]

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar