Link to home
Start Free TrialLog in
Avatar of peggyweber
peggyweber

asked on

Wrapping VBA line of code with a Run SQL insert statement.

Max out my line length in VBA code in Acccess Project.   I need to wrap the code but I keep geting end of line error.  I have seen this question poste but the solution did not work not sure why.  Here is my code that is giving me headaches.  Basically it is two lines as of right now with the second line suppost to start at (idDiscovery....etc....

I like this to create into several lines but be nice if I could get just two to work.

  DoCmd.RunSQL "INSERT INTO dbo.arch_tbl_Project_Date_Exp"&_
  "(idDiscovery, Project_StartDate, estExpeimental_End_Date, estExp_Risk, estRealization_End_date, estReal_Risk, estDevelop_End_Date, estDev_Risk, RMS_Alpha_Expected_EndDate, RMS_Alpha_EndDate, RMS_Alpha_Risk, RMS_Expected_EndDate, Project_EndDate, RMS_Risk, AcutExp_EndDate, ActReal_EndDate, ActDev_EndDate, RMS_Alpha_Actual_Date, RMS_Actual_Release) VALUES ('" & idDiscovery & "','" & Project_StartDate & "','" & estExpeimental_End_Date & "','" & estExp_Risk & "','" & estRealization_End_date & "','" & estReal_Risk & "','" & estDevelop_End_Date & "','" & estDev_Risk & "','" & RMS_Alpha_Expected_EndDate & "','" & RMS_Alpha_EndDate & "','" & RMS_Alpha_Risk & "','" & RMS_Expected_EndDate & "', '" & Project_EndDate & "','" & RMS_Risk & "','" & AcutExp_EndDate & "','" & ActReal_EndDate & "','" & ActDev_EndDate & "','" & RMS_Alpha_Actual_Date & "','" & RMS_Actual_Release & "');""
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try this:

Dim strSQL as String
strSQL =  "INSERT INTO dbo.arch_tbl_Project_Date_Exp "
strsql = strSQL &   "(idDiscovery, Project_StartDate, estExpeimental_End_Date, estExp_Risk, estRealization_End_date, estReal_Risk, estDevelop_End_Date, estDev_Risk, RMS_Alpha_Expected_EndDate, RMS_Alpha_EndDate, RMS_Alpha_Risk, RMS_Expected_EndDate, Project_EndDate, RMS_Risk, AcutExp_EndDate, ActReal_EndDate, ActDev_EndDate, RMS_Alpha_Actual_Date, RMS_Actual_Release) "
strSQL = strSQL & " VALUES ('" & idDiscovery & "','" & Project_StartDate & "','" & estExpeimental_End_Date & "','" & estExp_Risk & "','"
strSQL = strSQL & estRealization_End_date & "','" & estReal_Risk & "','" & estDevelop_End_Date & "','" & estDev_Risk & "','"
strSQL = strSQL & & RMS_Alpha_Expected_EndDate & "','" & RMS_Alpha_EndDate & "','" & RMS_Alpha_Risk & "','"
strSQL = strSQL & RMS_Expected_EndDate & "', '" & Project_EndDate & "','" & RMS_Risk & "','" & AcutExp_EndDate & "','"
strSQL = strSQL  & ActReal_EndDate & "','" & ActDev_EndDate & "','" & RMS_Alpha_Actual_Date & "','" & RMS_Actual_Release & "');""

DoCmd.RunSQL strSQL
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peggyweber
peggyweber

ASKER

getting an ODC error in the DoCmd.RunSQL strSQL

Note: I am programming in Access Project which seems to work differently then standard Access.
add a debug.print strSQL before the docmd.RunSql to see how it all is put together exactly. Then copy paste that into the QBE pane to see if the query will run.

This might help you track down a space, comma, quote, or grammatical type problem.