Invalid SQL Statement in VBA sub

J

JCIrish

The following code is in a query, written in a New Blank Query, not using the
Grid. The code when run yields "Invalid SQL statement Expected "DELETE",
SELECT", etc.
and no data sheet is returned. If I put the code in a standard module and
"run" it in the immediate window, there are no error messages, but no return
either. I'm new to Access (2007) and I don't know where to go. Is it possible
I'm missing something earlier in the process like doing something wrong in
Tools > References, or perhaps in not locating the code in the right place?
(I know the table reference is not good form but I've tried this with other,
no-spaces table names as well). I think my code must be all right? Please
enlighten me, someone. I'm stuck (I've tried it with and without the .Sort
line.


Sub TryOpenDB()


Dim cnX As ADODB.Connection
Set cnX = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset

myRecordSet.ActiveConnection = cnX
myRecordSet.CursorType = adOpenDynamic
myRecordSet.CursorLocation = adUseClient
myRecordSet.LockType = adLockOptimistic
myRecordSet.Sort = "[Security Type]"
myRecordSet.CommandType = adCmdText


myRecordSet.Open "SELECT [Portfolio Holdings and Value].[Security
Name],[Portfolio Holdings and Value].[Security Type],[Portfolio Holdings and
Value].[Market Value] FROM [Portfolio Holdings and Value] ORDER BY [Portfolio
Holdings and Value].[Security Type]"



End Sub
 
K

Ken Snell \(MVP\)

In your code, is the last "code line" (the one with Open) one single line?
Or is it wrapped the way it appears in the post?
 
K

Ken Snell \(MVP\)

By the way, I just noted that you're expecting this code to open the query
in a datasheet view. It will not do that. When you open a recordset this
way, it exists in memory, but does not display on your screen. Instead, save
your query and then use DoCmd.OpenQuery to open it from code.

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
In your code, is the last "code line" (the one with Open) one single line?
Or is it wrapped the way it appears in the post?
--

Ken Snell
<MS ACCESS MVP>



JCIrish said:
The following code is in a query, written in a New Blank Query, not using
the
Grid. The code when run yields "Invalid SQL statement Expected "DELETE",
SELECT", etc.
and no data sheet is returned. If I put the code in a standard module and
"run" it in the immediate window, there are no error messages, but no
return
either. I'm new to Access (2007) and I don't know where to go. Is it
possible
I'm missing something earlier in the process like doing something wrong
in
Tools > References, or perhaps in not locating the code in the right
place?
(I know the table reference is not good form but I've tried this with
other,
no-spaces table names as well). I think my code must be all right? Please
enlighten me, someone. I'm stuck (I've tried it with and without the
.Sort
line.


Sub TryOpenDB()


Dim cnX As ADODB.Connection
Set cnX = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset

myRecordSet.ActiveConnection = cnX
myRecordSet.CursorType = adOpenDynamic
myRecordSet.CursorLocation = adUseClient
myRecordSet.LockType = adLockOptimistic
myRecordSet.Sort = "[Security Type]"
myRecordSet.CommandType = adCmdText


myRecordSet.Open "SELECT [Portfolio Holdings and Value].[Security
Name],[Portfolio Holdings and Value].[Security Type],[Portfolio Holdings
and
Value].[Market Value] FROM [Portfolio Holdings and Value] ORDER BY
[Portfolio
Holdings and Value].[Security Type]"



End Sub
 
J

JCIrish

Thanks much for the response, Ken. The code you asked about is all on one
line. Also, I'm unable to save the query because every time I try to do so I
get the "Invalid SQL..." message. (I did, however insert the DoCmd. I
didn't know about that so that's a big help) Also, I know that this code
works ok on another computer that belongs to another guy I was talking to on
the internet (not on a MS help site). That's one thing that led me to wonder
whether I had goofed somewhere else, like in Tools>References or whatever. By
the way my OS is Vista Ultimate 64 bit, if that's relevant. Any further help
will be much appreciated. I'm stymied here at the outset of my marvelous
Access adventures.

Ken Snell (MVP) said:
By the way, I just noted that you're expecting this code to open the query
in a datasheet view. It will not do that. When you open a recordset this
way, it exists in memory, but does not display on your screen. Instead, save
your query and then use DoCmd.OpenQuery to open it from code.

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
In your code, is the last "code line" (the one with Open) one single line?
Or is it wrapped the way it appears in the post?
--

Ken Snell
<MS ACCESS MVP>



JCIrish said:
The following code is in a query, written in a New Blank Query, not using
the
Grid. The code when run yields "Invalid SQL statement Expected "DELETE",
SELECT", etc.
and no data sheet is returned. If I put the code in a standard module and
"run" it in the immediate window, there are no error messages, but no
return
either. I'm new to Access (2007) and I don't know where to go. Is it
possible
I'm missing something earlier in the process like doing something wrong
in
Tools > References, or perhaps in not locating the code in the right
place?
(I know the table reference is not good form but I've tried this with
other,
no-spaces table names as well). I think my code must be all right? Please
enlighten me, someone. I'm stuck (I've tried it with and without the
.Sort
line.


Sub TryOpenDB()


Dim cnX As ADODB.Connection
Set cnX = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset

myRecordSet.ActiveConnection = cnX
myRecordSet.CursorType = adOpenDynamic
myRecordSet.CursorLocation = adUseClient
myRecordSet.LockType = adLockOptimistic
myRecordSet.Sort = "[Security Type]"
myRecordSet.CommandType = adCmdText


myRecordSet.Open "SELECT [Portfolio Holdings and Value].[Security
Name],[Portfolio Holdings and Value].[Security Type],[Portfolio Holdings
and
Value].[Market Value] FROM [Portfolio Holdings and Value] ORDER BY
[Portfolio
Holdings and Value].[Security Type]"



End Sub
 
K

Ken Snell \(MVP\)

You're saying that, if you paste this SQL statement into the SQL View of a
new query, you cannot save the query?

SELECT [Portfolio Holdings and Value].[Security Name],
[Portfolio Holdings and Value].[Security Type],
[Portfolio Holdings and Value].[Market Value]
FROM [Portfolio Holdings and Value]
ORDER BY [Portfolio Holdings and Value].[Security Type]


I do not have ACCESS 2007 installed on a VISTA 64-bit operating system, so I
cannot test your code. But, I don't see anything obviously wrong with your
posted code or SQL statement.

I'll see if I can engage the attention of another person who might be able
to assist in this thread.
--

Ken Snell
<MS ACCESS MVP>





JCIrish said:
Thanks much for the response, Ken. The code you asked about is all on one
line. Also, I'm unable to save the query because every time I try to do so
I
get the "Invalid SQL..." message. (I did, however insert the DoCmd. I
didn't know about that so that's a big help) Also, I know that this code
works ok on another computer that belongs to another guy I was talking to
on
the internet (not on a MS help site). That's one thing that led me to
wonder
whether I had goofed somewhere else, like in Tools>References or whatever.
By
the way my OS is Vista Ultimate 64 bit, if that's relevant. Any further
help
will be much appreciated. I'm stymied here at the outset of my marvelous
Access adventures.

Ken Snell (MVP) said:
By the way, I just noted that you're expecting this code to open the
query
in a datasheet view. It will not do that. When you open a recordset this
way, it exists in memory, but does not display on your screen. Instead,
save
your query and then use DoCmd.OpenQuery to open it from code.

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
In your code, is the last "code line" (the one with Open) one single
line?
Or is it wrapped the way it appears in the post?
--

Ken Snell
<MS ACCESS MVP>



The following code is in a query, written in a New Blank Query, not
using
the
Grid. The code when run yields "Invalid SQL statement Expected
"DELETE",
SELECT", etc.
and no data sheet is returned. If I put the code in a standard module
and
"run" it in the immediate window, there are no error messages, but no
return
either. I'm new to Access (2007) and I don't know where to go. Is it
possible
I'm missing something earlier in the process like doing something
wrong
in
Tools > References, or perhaps in not locating the code in the right
place?
(I know the table reference is not good form but I've tried this with
other,
no-spaces table names as well). I think my code must be all right?
Please
enlighten me, someone. I'm stuck (I've tried it with and without the
.Sort
line.


Sub TryOpenDB()


Dim cnX As ADODB.Connection
Set cnX = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset

myRecordSet.ActiveConnection = cnX
myRecordSet.CursorType = adOpenDynamic
myRecordSet.CursorLocation = adUseClient
myRecordSet.LockType = adLockOptimistic
myRecordSet.Sort = "[Security Type]"
myRecordSet.CommandType = adCmdText


myRecordSet.Open "SELECT [Portfolio Holdings and Value].[Security
Name],[Portfolio Holdings and Value].[Security Type],[Portfolio
Holdings
and
Value].[Market Value] FROM [Portfolio Holdings and Value] ORDER BY
[Portfolio
Holdings and Value].[Security Type]"



End Sub
 
J

JCIrish

Thanks so much, Ken, for getting back to me. I really appreciate your help.
My problem still exists. I'm wondering at this point if there is something
missing in my installation of Access 2007. For example I've also received the
message at times, "ODBC driver manager data source name not found and no
default driver specified". I don't know how to verify if the Access on my
computer is missing files. I've heard something about a Service Pack for
Access 2007 and I'm loolking into that. I would be glad to hear from someone
else if you can grab his or her attention.

Ken Snell (MVP) said:
You're saying that, if you paste this SQL statement into the SQL View of a
new query, you cannot save the query?

SELECT [Portfolio Holdings and Value].[Security Name],
[Portfolio Holdings and Value].[Security Type],
[Portfolio Holdings and Value].[Market Value]
FROM [Portfolio Holdings and Value]
ORDER BY [Portfolio Holdings and Value].[Security Type]


I do not have ACCESS 2007 installed on a VISTA 64-bit operating system, so I
cannot test your code. But, I don't see anything obviously wrong with your
posted code or SQL statement.

I'll see if I can engage the attention of another person who might be able
to assist in this thread.
--

Ken Snell
<MS ACCESS MVP>





JCIrish said:
Thanks much for the response, Ken. The code you asked about is all on one
line. Also, I'm unable to save the query because every time I try to do so
I
get the "Invalid SQL..." message. (I did, however insert the DoCmd. I
didn't know about that so that's a big help) Also, I know that this code
works ok on another computer that belongs to another guy I was talking to
on
the internet (not on a MS help site). That's one thing that led me to
wonder
whether I had goofed somewhere else, like in Tools>References or whatever.
By
the way my OS is Vista Ultimate 64 bit, if that's relevant. Any further
help
will be much appreciated. I'm stymied here at the outset of my marvelous
Access adventures.

Ken Snell (MVP) said:
By the way, I just noted that you're expecting this code to open the
query
in a datasheet view. It will not do that. When you open a recordset this
way, it exists in memory, but does not display on your screen. Instead,
save
your query and then use DoCmd.OpenQuery to open it from code.

--

Ken Snell
<MS ACCESS MVP>


In your code, is the last "code line" (the one with Open) one single
line?
Or is it wrapped the way it appears in the post?
--

Ken Snell
<MS ACCESS MVP>



The following code is in a query, written in a New Blank Query, not
using
the
Grid. The code when run yields "Invalid SQL statement Expected
"DELETE",
SELECT", etc.
and no data sheet is returned. If I put the code in a standard module
and
"run" it in the immediate window, there are no error messages, but no
return
either. I'm new to Access (2007) and I don't know where to go. Is it
possible
I'm missing something earlier in the process like doing something
wrong
in
Tools > References, or perhaps in not locating the code in the right
place?
(I know the table reference is not good form but I've tried this with
other,
no-spaces table names as well). I think my code must be all right?
Please
enlighten me, someone. I'm stuck (I've tried it with and without the
.Sort
line.


Sub TryOpenDB()


Dim cnX As ADODB.Connection
Set cnX = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset

myRecordSet.ActiveConnection = cnX
myRecordSet.CursorType = adOpenDynamic
myRecordSet.CursorLocation = adUseClient
myRecordSet.LockType = adLockOptimistic
myRecordSet.Sort = "[Security Type]"
myRecordSet.CommandType = adCmdText


myRecordSet.Open "SELECT [Portfolio Holdings and Value].[Security
Name],[Portfolio Holdings and Value].[Security Type],[Portfolio
Holdings
and
Value].[Market Value] FROM [Portfolio Holdings and Value] ORDER BY
[Portfolio
Holdings and Value].[Security Type]"



End Sub
 
J

JCIrish

Thanks, Ken. Sorry to be so late in getting back to you. Been swamped wih
other concerns lately. I will download and install SP1 in the next couple of
days. Thanks for the link. You really have gone "above and beyond" on this
issue and I appreciate it.

JCIrish
 
J

JCIrish

Ken,

Just wanted to let you know that I finally got around to installing SP-1 on
my computer. After installation I wrote a small bit of VBA code to open a db
file and return the number of records. It worked fine. Sooo, I'm not
well-enough informed in this area to draw any firm conclusions, but it all
makes me wonder. Thanks again for your help.

JCIrish
 
K

Ken Snell \(MVP\)

Thanks for the feedback; I'll pass this along to Microsoft. Glad it's now
working!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top