 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Invalid SQL Statement in VBA sub
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 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?  SignatureKen Snell <MS ACCESS MVP> > The following code is in a query, written in a New Blank Query, not using > the [quoted text clipped - 36 lines] > > End Sub 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. SignatureKen 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? [quoted text clipped - 40 lines] >> >> End Sub 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. > 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 [quoted text clipped - 45 lines] > >> > >> End Sub 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. SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 70 lines] >> >> >> >> End Sub 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. > You're saying that, if you paste this SQL statement into the SQL View of a > new query, you cannot save the query? [quoted text clipped - 85 lines] > >> >> > >> >> End Sub There is an SP1 update available for Office 2007: http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997 -25DA01F388F5&displaylang=en My contact at Microsoft is trying to arrange a test on a machine that has VISTA Ultimate 64-bit and ACCESS 2007; but I've not heard from him yet regarding any tests. Post back and let us know if applying SP1 helps or not. SignatureKen Snell <MS ACCESS MVP> > Thanks so much, Ken, for getting back to me. I really appreciate your > help. [quoted text clipped - 7 lines] > someone > else if you can grab his or her attention. 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 > There is an SP1 update available for Office 2007: > http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997 -25DA01F388F5&displaylang=en [quoted text clipped - 15 lines] > > someone > > else if you can grab his or her attention. 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 > There is an SP1 update available for Office 2007: > http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997 -25DA01F388F5&displaylang=en [quoted text clipped - 15 lines] > > someone > > else if you can grab his or her attention. Thanks for the feedback; I'll pass this along to Microsoft. Glad it's now working! SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 32 lines] >> > someone >> > else if you can grab his or her attention. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.