• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

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 & "');""
0
peggyweber
Asked:
peggyweber
  • 2
1 Solution
 
mbizupCommented:
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
0
 
mbizupCommented:
correction:

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
0
 
peggyweberAuthor Commented:
getting an ODC error in the DoCmd.RunSQL strSQL

Note: I am programming in Access Project which seems to work differently then standard Access.
0
 
eddiejCommented:
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.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now