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
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