Question Executing statement from VBA Code

I

itsmedd

Registered User.
Local time
Today, 05:06
Joined
Sep 19, 2007
Messages
30
All,
I need experts advice on resolving this issue, i am trying to extract data from a sql server database on to an access database for my custom dashboard.

I am getting the "Runtime Error - 2147467259 (80004005):Driver]Expected lexical element not found: VALUES" while i am trying to execute the below insert statement but i am able to execute same statement without the INSERT INTO DAILY part. Any thoughts would be of great help for me..

Thanks in Advance.

strSQL = "Insert into Daily select HPD_HelpDesk.Case_ID_, HPD_HelpDesk.Arrival_Time, HPD_HelpDesk.Assigned_To_Individual_, HPD_HelpDesk.Category, HPD_HelpDesk.Type, HPD_HelpDesk.Item, HPD_HelpDesk.Assigned_To_Group_, HPD_HelpDesk.Priority, HPD_HelpDesk.Arrival_Time, HPD_HelpDesk.Assign_Time, HPD_HelpDesk.Submitted_By_Group, HPD_HelpDesk.chk_FirstPointResolution, HPD_HelpDesk.Closed_by_Group, HPD_HelpDesk.Closing_Notes, HPD_HelpDesk.Escalation_manager, HPD_HelpDesk.Site, HPD_HelpDesk.Status,HPD_HelpDesk.source ,HPD_HelpDesk.Assignee_Login_Name, HPD_HelpDesk.zWeb_WhoModifiedCall, HPD_HelpDesk.Status_History_New_USER FROM HPD_HelpDesk WHERE HPD_HelpDesk.Helpdesk='ISSC' OR HPD_HelpDesk.Helpdesk='ISSC EU' AND HPD_HelpDesk.Source <> 'Alert' AND ((HPD_HelpDesk.create_Time > {ts '" & startdate & " 00:00:00'}) AND (HPD_HelpDesk.create_Time < {ts '" & Enddate & " 01:00:00'}))"
MsgBox strSQL
rsconn.Execute strSQL
 
A bit confused. You ask:
All,
I need experts advice on resolving this issue, i am trying to extract data from a sql server database on to an access database for my custom dashboard.
Click to expand...

You say you are trying to 'extract' data from an sql server, but yet you are using an INSERT sql statement. Which is it?


As the error statement says, you are not providing the Values for the fields you are specifying in your INSERT statement. An Insert query should look something like this:

Code: 
INSERT INTO tblTableName (FieldName1, FieldName2, FieldName3, etc)
VALUES ("FieldName1Value", FieldName2Value, FieldName3Value, etc)

Also, haven't you posted this question elsewhere?
 
Hi, Thanks for the quick response, yes i did post in vba module but felt that its more visible in general forum. I did try the below statement but still getting the same error message.

"Insert into Daily (Case_ID,Arrival_Time,Assigned_To_Individual,Category,Type,Item,Assigned_To_Group_,Priority,Arrival_Time,Assign_Time,Submitted_By_Group,chk_FirstPointResolution,Closed_by_Group, Closing_Notes,Escalation_manager,Site,Status,source,Assignee_Login_Name,zWeb_WhoModifiedCall,Status_History_New_USER) VALUES " & _
'"(select HPD_HelpDesk.Case_ID_, HPD_HelpDesk.Arrival_Time, HPD_HelpDesk.Assigned_To_Individual_, HPD_HelpDesk.Category, HPD_HelpDesk.Type, HPD_HelpDesk.Item, HPD_HelpDesk.Assigned_To_Group_, HPD_HelpDesk.Priority, HPD_HelpDesk.Arrival_Time, HPD_HelpDesk.Assign_Time, HPD_HelpDesk.Submitted_By_Group, HPD_HelpDesk.chk_FirstPointResolution, HPD_HelpDesk.Closed_by_Group, HPD_HelpDesk.Closing_Notes, HPD_HelpDesk.Escalation_manager, HPD_HelpDesk.Site, HPD_HelpDesk.Status,HPD_HelpDesk.source ,HPD_HelpDesk.Assignee_Login_Name, HPD_HelpDesk.zWeb_WhoModifiedCall, HPD_HelpDesk.Status_History_New_USER FROM HPD_HelpDesk WHERE HPD_HelpDesk.Helpdesk='ISSC' OR HPD_HelpDesk.Helpdesk='ISSC EU' AND HPD_HelpDesk.Source <> 'Alert' AND ((HPD_HelpDesk.create_Time > {ts '" & startdate & " 00:00:00'}) AND (HPD_HelpDesk.create_Time < {ts '" & Enddate & " 01:00:00'})))"
 
Have you done a debug.print strSql?

Also, not sure if this is the issue, but dunno if the {} brackets are valid for a sql statement.

You also have spaces in some of your field names (chk_FirstPoint Resolution). Put brackets [] around those (One reason why NOT to use spaces in field names.
 
i did try the debug.print strSQL with no output and also i have corrected the same issue but still i am getting the error message "Runtime Error - 2147467259 (80004005)river]Expected lexical element not found: <identifier>. Please help
 
When you did the debug.print, did you take the Sql string from the Immediate window and paste it into an empty Query to see if it would run?
 
Also, do you really have fields that have, as the last character in their name, an underscore?
examples:
HPD_HelpDesk.Case_ID_
Assigned_To_Group_
 
Did a bit of googling...and are you attempting to add duplicate records as well?
 
Yes i did execute the same query from access and its giving me the same error message.
I do have the field name with _ in our Remedy database
 
itsmedd said:
Yes i did execute the same query from access and its giving me the same error message.
I do have the field name with _ in our Remedy database
Click to expand...

I'm just wondering if those "hanging" underscores are making Access THINK that you are trying to use a line continuation. In Access you use an underscore to denote a line continuation and I think it may be "barfing" on that. So, I think you will need to enclose your field names, at least the ones with the hanging underscore, in square brackets [ ] so that Access doesn't get confused as to what you are doing.
 
Thanks for all your efforts and support, i am still unable to get this working. :(
 
itsmedd said:
Thanks for all your efforts and support, i am still unable to get this working. :(
Click to expand...

So, what do you CURRENTLY have now that you've enclosed things in brackets where they need to be and all?
 
Bob, I have enclosed all the fields in select statement in [] and still getting the error msg lexical element not found : <Identifier>. I dont think its problem with the statement as i am able to execute the select statement, i am getting this error only when i include the Insert part with it.

Insert into Daily (Select [HPD_HelpDesk.Case_ID_], [HPD_HelpDesk.Arrival_Time], [HPD_HelpDesk.Assigned_To_Individual_], [HPD_HelpDesk.Category], [HPD_HelpDesk.Type], [HPD_HelpDesk.Item], [HPD_HelpDesk.Assigned_To_Group_], [HPD_HelpDesk.Priority], [HPD_HelpDesk.Arrival_Time], [HPD_HelpDesk.Assign_Time], [HPD_HelpDesk.Submitted_By_Group], [HPD_HelpDesk.chk_FirstPointResolution], [HPD_HelpDesk.Closed_by_Group], [HPD_HelpDesk.Closing_Notes], [HPD_HelpDesk.Escalation_manager], [HPD_HelpDesk.Site], [HPD_HelpDesk.Status], [HPD_HelpDesk.source], [HPD_HelpDesk.Assignee_Login_Name], [HPD_HelpDesk.zWeb_WhoModifiedCall], [HPD_HelpDesk.Status_History_New_USER] FROM HPD_HelpDesk WHERE HPD_HelpDesk.Helpdesk='ISSC' OR HPD_HelpDesk.Helpdesk='ISSC EU' AND HPD_HelpDesk.Source <> 'Alert' AND (([HPD_HelpDesk.create_Time] > {ts '2009-11-01 00:00:00'}) AND ([HPD_HelpDesk.create_Time] < {ts '2009-11-02 00:00:00'})))
 
Okay, now I have to ask what are these:

{ts '2009-11-02 00:00:00'})))


And why are they in {} brackets?
 
I even tried the below statemen with target table fields, still the same error msg.

Insert into Daily ([Case_ID],[Arrival_Time],[Assigned_To_Individual],[Category],[Type],[Item],[Assigned_To_Group_],[Priority],[Arrival_Time],[Assign_Time],[Submitted_By_Group],[chk_FirstPointResolution],[Closed_by_Group],[Closing_Notes],[Escalation_manager],[Site],[Status],[source],[Assignee_Login_Name],[zWeb_WhoModifiedCall],[Status_History_New_USER]) VALUES (Select [HPD_HelpDesk.Case_ID_],[HPD_HelpDesk.Arrival_Time],[HPD_HelpDesk.Assigned_To_Individual_],[HPD_HelpDesk.Category],[HPD_HelpDesk.Type],[HPD_HelpDesk.Item],[HPD_HelpDesk.Assigned_To_Group_],[HPD_HelpDesk.Priority],[HPD_HelpDesk.Arrival_Time],[HPD_HelpDesk.Assign_Time],[HPD_HelpDesk.Submitted_By_Group],[HPD_HelpDesk.chk_FirstPointResolution],[HPD_HelpDesk.Closed_by_Group],[HPD_HelpDesk.Closing_Notes],[HPD_HelpDesk.Escalation_manager],[HPD_HelpDesk.Site],[HPD_HelpDesk.Status],[HPD_HelpDesk.source],[HPD_HelpDesk.Assignee_Login_Name],[HPD_HelpDesk.zWeb_WhoModifiedCall],[HPD_HelpDesk.Status_History_New_USER] FROM HPD_HelpDesk WHERE HPD_HelpDesk.Helpde
sk ='ISSC' OR HPD_HelpDesk.Helpdesk ='ISSC EU' AND HPD_HelpDesk.Source <> 'Alert' AND (([HPD_HelpDesk.create_Time] > {ts '2009-11-01 00:00:00'}) AND ([HPD_HelpDesk.create_Time] < {ts '2009-11-02 00:00:00'})))
 
those {} are for my Start and End date timestamp without which my select statement wouldnt work.
 
itsmedd said:
those {} are for my Start and End date timestamp without which my select statement wouldnt work.
Click to expand...

Well, you can't use them like that in Access. I'll bet that is what is causing your problem. Now, refresh my memory. Are you dealing with SQL Server? If so, the date/time stamp fields should be date/time fields if you are trying to get data. You cannot, nor should you even be trying, to query the special SQL Server "timestamp" field. It is not what you think it is, if that is what you are trying to do.
 
yes i am using SQL Server and removing the ts nor the {} didnt help me resolve the issue.
 
So, what happens when you use this:
Code: 
Insert into Daily (
[Case_ID],
[Arrival_Time],
[Assigned_To_Individual],
[Category],
[Type],
[Item],
[Assigned_To_Group_],
[Priority],
[Arrival_Time],
[Assign_Time],
[Submitted_By_Group],
[chk_FirstPointResolution],
[Closed_by_Group],
[Closing_Notes],
[Escalation_manager],
[Site],
[Status],
[source],
[Assignee_Login_Name],
[zWeb_WhoModifiedCall],
[Status_History_New_USER]
) VALUES (Select 
[HPD_HelpDesk].[Case_ID_],
[HPD_HelpDesk].[Arrival_Time],
[HPD_HelpDesk].[Assigned_To_Individual_],
[HPD_HelpDesk].[Category],
[HPD_HelpDesk].[Type],
[HPD_HelpDesk].[Item],
[HPD_HelpDesk].[Assigned_To_Group_],
[HPD_HelpDesk].[Priority],
[HPD_HelpDesk].[Arrival_Time],
[HPD_HelpDesk].[Assign_Time],
[HPD_HelpDesk].[Submitted_By_Group],
[HPD_HelpDesk].[chk_FirstPointResolution],
[HPD_HelpDesk].[Closed_by_Group],
[HPD_HelpDesk].[Closing_Notes],
[HPD_HelpDesk].[Escalation_manager],
[HPD_HelpDesk].[Site],
[HPD_HelpDesk].[Status],
[HPD_HelpDesk].[source],
[HPD_HelpDesk].[Assignee_Login_Name],
[HPD_HelpDesk].[zWeb_WhoModifiedCall],
[HPD_HelpDesk].[Status_History_New_USER] 
FROM [HPD_HelpDesk] 
WHERE [HPD_HelpDesk].[Helpdesk] ='ISSC' OR 
[HPD_HelpDesk].[Helpdesk] ='ISSC EU' AND 
([HPD_HelpDesk].[Source] <> 'Alert' AND 
([HPD_HelpDesk].[create_Time] > #2009-11-01 00:00:00#) AND ([HPD_HelpDesk].[create_Time] < #2009-11-02 00:00:00#))

I went through and formatted it for easier reads and found you had the bracketing all wrong. You don't start the bracket at the beginning of the table name and put the ending at the field name. You bracket EACH part (although you don't neccessarily have to bracket the table names if no spaces or special characters but I did anyway for consistency. Also, I tried something different for the dates so Access would parse it for you hopefully correctly.

I will post the not line split version of the same SQL string below in another post.
 
The unsplit lines:
Code: 
Insert into Daily ([Case_ID],[Arrival_Time],[Assigned_To_Individual],[Category],[Type],
[Item],[Assigned_To_Group_],[Priority],[Arrival_Time],[Assign_Time],[Submitted_By_Group],[chk_FirstPointResolution],[Closed_by_Group],[Closing_Notes],[Escalation_manager],[Site],[Status],[source],[Assignee_Login_Name],[zWeb_WhoModifiedCall],[Status_History_New_USER]) VALUES (Select [HPD_HelpDesk].[Case_ID_],[HPD_HelpDesk].[Arrival_Time],[HPD_HelpDesk].[Assigned_To_Individual_],[HPD_HelpDesk].[Category],[HPD_HelpDesk].[Type],[HPD_HelpDesk].[Item],[HPD_HelpDesk].[Assigned_To_Group_],[HPD_HelpDesk].[Priority],[HPD_HelpDesk].[Arrival_Time],[HPD_HelpDesk].[Assign_Time],[HPD_HelpDesk].[Submitted_By_Group],[HPD_HelpDesk].[chk_FirstPointResolution],[HPD_HelpDesk].[Closed_by_Group],[HPD_HelpDesk].[Closing_Notes],[HPD_HelpDesk].[Escalation_manager],[HPD_HelpDesk].[Site],[HPD_HelpDesk].[Status],[HPD_HelpDesk].[source],[HPD_HelpDesk].[Assignee_Login_Name],[HPD_HelpDesk].[zWeb_WhoModifiedCall],[HPD_HelpDesk].[Status_History_New_USER] FROM [HPD_HelpDesk] WHERE [HPD_HelpDesk].[Helpdesk] ='ISSC' OR [HPD_HelpDesk].[Helpdesk] ='ISSC EU' AND ([HPD_HelpDesk].[Source] <> 'Alert' AND ([HPD_HelpDesk].[create_Time] > #2009-11-01 00:00:00#) AND ([HPD_HelpDesk].[create_Time] < #2009-11-02 00:00:00#))
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom