CurrentDb.Execute can't run SELECT Statement

[edisonl via AccessMonster.com]

Edison: Help! Help!

1. I had a leave applicantion where Manager able to view their Department
Leave status (Eg: Summay, History Transactions etc)

2. I had achieve it via Queries.

3. Now another issue is I need to PROGRAMATICALLY insert SQL statement to
allow HR View All,
I copied the statement from previous Queries made some modifications,
then do a

strsql = "SELECT AnnualLeave_Table.FromDate...... WHERE ((
(AnnualLeave_Table.FromDate)& _
"Between [Forms]![ViewLeave_Form]![From_Text] And [Forms]![ViewLeave_Form]!
[To_Text])) " & _
"Order By annualLeave_Table.Name;"
a = msgbox (strsql)
currentdb.execute strsql



'OUTPUT >> though I did a display of the strsql output prior to execute
sql

Run time Error'3065'
Cannot execute Select statement

Help! Help!
Edison

 

[Jeanette Cunningham]

Hi edisonl
you need to do a mock up of the sql in a new query to find what is wrong
with it.
Put this line after your sql, before the execute statement.
Debug.Print strSQL

Now open your form normally and run the code that executes the query.
Close the form and open the debug window - aka the immediate window by
pressing Ctl + G.
You will see the sql string there.
Copy and paste it into the sql view of a new query.
Switch the query to datasheet view and access will tell you about the errors
with your sql.

Here is a tip for selecting a time period using dates.
Using 'between' can miss out on some records - here is a way that won't miss
any records.

SELECT yadda, yadda
FROM SomeTable
WHERE AnnualLeave_Table.FromDate >= [Forms]!. . etc
AND AnnualLeave_Table.FromDate < DateAdd("d", 1, [Forms]!. . etc)

Replace [Forms]!. . . etc with the obvious.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Chris O'C via AccessMonster.com]

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the difference?
A select query shows the selected columns. An action query makes changes to
the data, and a data definition query makes changes to the db table
structures.

If you want to show the rows in a query you could use the openquery method,
but it's probably better to create a form where the record source is the name
of the query and open the form for the users when they click a button.

DoCmd.OpenQuery "queryname"

Second, you have a typo because there's no space between the parenthesis
after AnnualLeave_Table.FromDate and the ampersand.

Third, why are you returning a msgbox function value (a VbMsgBoxResult enum
type) to a variable? Most professional programmers use a debug.print
statement to show a value in the immediate window during debugging because it
can be read later after the proc executes and can be copied/pasted/edited.

debug.print strsql

Chris

Edison: Help! Help!

1. I had a leave applicantion where Manager able to view their Department
Leave status (Eg: Summay, History Transactions etc)

2. I had achieve it via Queries.

3. Now another issue is I need to PROGRAMATICALLY insert SQL statement to
allow HR View All,
I copied the statement from previous Queries made some modifications,
then do a

strsql = "SELECT AnnualLeave_Table.FromDate...... WHERE ((
(AnnualLeave_Table.FromDate)& _
"Between [Forms]![ViewLeave_Form]![From_Text] And [Forms]![ViewLeave_Form]!
[To_Text])) " & _
"Order By annualLeave_Table.Name;"
a = msgbox (strsql)
currentdb.execute strsql



'OUTPUT >> though I did a display of the strsql output prior to execute
sql

Run time Error'3065'
Cannot execute Select statement

Help! Help!
Edison

 

[Chris O'C via AccessMonster.com]

If the query doesn't already exist you can write one on the fly. Create a
temp query object first. Doesn't matter what the sql is as long as the query
exists before you run the proc.

Public Function showQry()
On Error GoTo ProcErr

Dim qry As QueryDef

Set qry = CurrentDb.QueryDefs("qrytemp")
qry.SQL = "SELECT invoicedate " _
& "FROM tablename " _
& "WHERE invoicedate BETWEEN #" & Forms!Form1!txtStart _
& "# AND #" _
& Forms!Form1!txtEnd & "#"
DoCmd.OpenQuery qry.name

ProcExit:
Set qry = Nothing
Exit Function

ProcErr:
MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
Resume ProcExit
End Function


Chris

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the difference?
A select query shows the selected columns. An action query makes changes to
the data, and a data definition query makes changes to the db table
structures.

If you want to show the rows in a query you could use the openquery method,
but it's probably better to create a form where the record source is the name
of the query and open the form for the users when they click a button.

DoCmd.OpenQuery "queryname"

Second, you have a typo because there's no space between the parenthesis
after AnnualLeave_Table.FromDate and the ampersand.

Third, why are you returning a msgbox function value (a VbMsgBoxResult enum
type) to a variable? Most professional programmers use a debug.print
statement to show a value in the immediate window during debugging because it
can be read later after the proc executes and can be copied/pasted/edited.

debug.print strsql

Chris

Edison: Help! Help!

[quoted text clipped - 26 lines]

Help! Help!
Edison

 

[David W. Fenton]

First off, you *can't* use the execute method with a select query.

If you're using DAO, correct. If you're using ADO, not true.

 

[Chris O'C via AccessMonster.com]

As per this thread's subject line, he's using the currentdb.execute method,
not ADO's connection.execute or command.execute methods.

Chris

 

[Jeanette Cunningham]

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the
difference?
A select query shows the selected columns. An action query makes changes
to
the data, and a data definition query makes changes to the db table
structures.

If you want to show the rows in a query you could use the openquery
method,
but it's probably better to create a form where the record source is the
name
of the query and open the form for the users when they click a button.

DoCmd.OpenQuery "queryname"

Second, you have a typo because there's no space between the parenthesis
after AnnualLeave_Table.FromDate and the ampersand.

Third, why are you returning a msgbox function value (a VbMsgBoxResult
enum
type) to a variable? Most professional programmers use a debug.print
statement to show a value in the immediate window during debugging because
it
can be read later after the proc executes and can be copied/pasted/edited.

debug.print strsql

Chris

Edison: Help! Help!

1. I had a leave applicantion where Manager able to view their Department
Leave status (Eg: Summay, History Transactions etc)

2. I had achieve it via Queries.

3. Now another issue is I need to PROGRAMATICALLY insert SQL statement to
allow HR View All,
I copied the statement from previous Queries made some modifications,
then do a

strsql = "SELECT AnnualLeave_Table.FromDate...... WHERE ((
(AnnualLeave_Table.FromDate)& _
"Between [Forms]![ViewLeave_Form]![From_Text] And
[Forms]![ViewLeave_Form]!
[To_Text])) " & _
"Order By annualLeave_Table.Name;"
a = msgbox (strsql)
currentdb.execute strsql



'OUTPUT >> though I did a display of the strsql output prior to execute
sql

Run time Error'3065'
Cannot execute Select statement

Help! Help!
Edison

 

[Jeanette Cunningham]

Good pickup Chris - using db.execute with a select query.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the
difference?
A select query shows the selected columns. An action query makes changes
to
the data, and a data definition query makes changes to the db table
structures.

If you want to show the rows in a query you could use the openquery
method,
but it's probably better to create a form where the record source is the
name
of the query and open the form for the users when they click a button.

DoCmd.OpenQuery "queryname"

Second, you have a typo because there's no space between the parenthesis
after AnnualLeave_Table.FromDate and the ampersand.

Third, why are you returning a msgbox function value (a VbMsgBoxResult
enum
type) to a variable? Most professional programmers use a debug.print
statement to show a value in the immediate window during debugging because
it
can be read later after the proc executes and can be copied/pasted/edited.

debug.print strsql

Chris

Edison: Help! Help!

1. I had a leave applicantion where Manager able to view their Department
Leave status (Eg: Summay, History Transactions etc)

2. I had achieve it via Queries.

3. Now another issue is I need to PROGRAMATICALLY insert SQL statement to
allow HR View All,
I copied the statement from previous Queries made some modifications,
then do a

strsql = "SELECT AnnualLeave_Table.FromDate...... WHERE ((
(AnnualLeave_Table.FromDate)& _
"Between [Forms]![ViewLeave_Form]![From_Text] And
[Forms]![ViewLeave_Form]!
[To_Text])) " & _
"Order By annualLeave_Table.Name;"
a = msgbox (strsql)
currentdb.execute strsql



'OUTPUT >> though I did a display of the strsql output prior to execute
sql

Run time Error'3065'
Cannot execute Select statement

Help! Help!
Edison

 

[David W. Fenton]

As per this thread's subject line, he's using the
currentdb.execute method, not ADO's connection.execute or
command.execute methods.

I wasn't contradicting, but simply supplying additional info.

 

[edisonl via AccessMonster.com]

Hi Chris, Jeanette,

Thanks will try it out by the way most probably I am using ADO (Access2K) I
believe though I didnt
explicitly declare it.

Anyway Chris what puzzles me is querydef datatype.. will it cease existance
once exit out of the
function ? (In addition, how different between msgbox & debug.print ?)


Secondly, I had c combobox (Actually I pose something similar on another
thread) but didn't get much help on the reply. Here's how it goes (exist in
another function perform another operation):


Strsql = SELECT * FROM yada yada yada WHERE yada yada yada
I had a ComboBox, RowSource = strsql
'Works perfectly.

But the tricky part here is I would like to add value user input into the
combobox (prefabably using append or otherwise you have other suggestions)

' My ComboBox RowSourceType = Table/Query
' Since its more or less related to Queries, Can I pose it here as well ?


Edison



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * *

If the query doesn't already exist you can write one on the fly. Create a
temp query object first. Doesn't matter what the sql is as long as the query
exists before you run the proc.

Public Function showQry()
On Error GoTo ProcErr

Dim qry As QueryDef

Set qry = CurrentDb.QueryDefs("qrytemp")
qry.SQL = "SELECT invoicedate " _
& "FROM tablename " _
& "WHERE invoicedate BETWEEN #" & Forms!Form1!txtStart _
& "# AND #" _
& Forms!Form1!txtEnd & "#"
DoCmd.OpenQuery qry.name

ProcExit:
Set qry = Nothing
Exit Function

ProcErr:
MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
Resume ProcExit
End Function

Chris

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the difference?

[quoted text clipped - 25 lines]

 

[Chris O'C via AccessMonster.com]

1 - You're not using the ADODB library when you use currentdb.execute. No
need to declare it for that.

2 - In the example I showed you, the querydef object is set to nothing,
releasing the pointer in memory, and then goes out of scope when the
procedure exits, so you're fine there. "It doesn't exist" when the procedure
ends, if that answers your question.

3 - The difference between a msgbox and debug.print for showing a variable's
value: The msgbox pauses the code while it displays the value and you have
to click the button and remember the message you're seeing. The debug.print
statement writes the variable's value in the immediate window, where you can
check it later to see if it was correct and copy/paste/edit that value when
you're making corrections. It doesn't require any action from you, like
pushing the "ok" button on the msgbox.

4 - The query to add another name to the Personnel_Table is going to be an
append query. I see two problems. You're using a reserved word for the name
of the column (Name) and it appears you are trying to store the full name
into one column instead of having separate columns for each word in the name:
firstname, lastname, mi.

Anyway, here's the syntax to append a single text value into a text field:

INSERT INTO Personnel_Table ([Name])
VALUES ('John')

I would advise changing field names that use reserved words. Once you make
the change in the table, you have to change all the queries that reference
that table, so it's not a single change. You have dependencies to take care
of.

Chris

 

[Chris O'C via AccessMonster.com]

After you run the append query to insert the new row (for your supervisor's
name), you have to requery the combo box.

StaffName_ComboBox.Requery

That will show all the records in the combo box's record source.

Chris

1 - You're not using the ADODB library when you use currentdb.execute. No
need to declare it for that.

2 - In the example I showed you, the querydef object is set to nothing,
releasing the pointer in memory, and then goes out of scope when the
procedure exits, so you're fine there. "It doesn't exist" when the procedure
ends, if that answers your question.

3 - The difference between a msgbox and debug.print for showing a variable's
value: The msgbox pauses the code while it displays the value and you have
to click the button and remember the message you're seeing. The debug.print
statement writes the variable's value in the immediate window, where you can
check it later to see if it was correct and copy/paste/edit that value when
you're making corrections. It doesn't require any action from you, like
pushing the "ok" button on the msgbox.

4 - The query to add another name to the Personnel_Table is going to be an
append query. I see two problems. You're using a reserved word for the name
of the column (Name) and it appears you are trying to store the full name
into one column instead of having separate columns for each word in the name:
firstname, lastname, mi.

Anyway, here's the syntax to append a single text value into a text field:

INSERT INTO Personnel_Table ([Name])
VALUES ('John')

I would advise changing field names that use reserved words. Once you make
the change in the table, you have to change all the queries that reference
that table, so it's not a single change. You have dependencies to take care
of.

Chris

Thanks will try it out by the way most probably I am using ADO (Access2K) I
believe though I didnt

[quoted text clipped - 17 lines]

' My ComboBox RowSourceType = Table/Query
' Since its more or less related to Queries, Can I pose it here as well ?

 

[edisonl via AccessMonster.com]

Hi Everyone,

Thanks for the effort. Managed to resolve the SQL Statement by creating a
query and run a Docmd.opensql line.

Second issues:

Background: I created a Leave Application, Supervisor Able to apply leave on
behalf of their subordinate.

Therefore: My ComboBox.RecordSource = "SELECT Name FROM Personnel_Table"
WHERE Approval = '" & LoginName_Text & "';"

Result: OK (but Alas I forgot about the Superior Name Himself, Which is
equivalent to LoginName_Text !)

I don't see need to re-insert LoginName_Text into Personnel_Table as it will
create duplicate data!

How Can I achieve it then to include LoginName_Text into ComboBox.
Recordsource ?



' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * *

Good pickup Chris - using db.execute with a select query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

First off, you *can't* use the execute method with a select query. It
requires an action query or a data definition query. What's the

[quoted text clipped - 55 lines]

 

[edisonl via AccessMonster.com]

Hi Chris,

The select statement is fine but after that i Like to add A control value
into my combo box
rowsource without making changes unto the table itself.

Edison



~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

After you run the append query to insert the new row (for your supervisor's
name), you have to requery the combo box.

StaffName_ComboBox.Requery

That will show all the records in the combo box's record source.

Chris

1 - You're not using the ADODB library when you use currentdb.execute. No
need to declare it for that.

[quoted text clipped - 35 lines]