1

So, as the title states, what I'm trying to do is in MS Excel visual basic I'm trying to write all in visual basic a SQL query that connects to a database and there are multiple cells that act as parameters. Right now I'm focusing on just getting the query to return back the unfiltered results. I found a very basic example located here...

Awesome query that a fellow stackoverflow member made

I just straight copied the code he was having problems with and tested it out with my database and a very simple query. This all works fine and dandy.

The issue that I'm running into is I have a little more complex SQL query that I need to use and I'm not quite sure on how the formatting should be in VB.

The SQL query that I need to run is listed below. The database that I'm using isn't very friendly either. When I try putting the entire query into the SQL string it gives me an error that just says SQL syntax error. And when I go to debug it shows the error on the .Refresh line. I realize this query is very large so I trimmed it down and made multiple lines in VB with double quotes marks around each sentence and combined them with & _. (See very bottom)

Anyone have any suggestions?

If anyone wants the full code, let me know but it's extremely similar to the link I posted above, just the ODBC connection information is different.

Thanks in advanced.

SELECT DISTINCT S.SAMPLENAME, S.SAMPLE_DESCRIPTION, B.BATCH_NAME, PR.PROC_NAME, S.SAMPLESTATE, S.SAMPLE_QTY,
                      (SELECT MMNT_NAME FROM apexalpha.CI_AAS_MEASUREMENTUNIT WHERE      (MEASUREMENTUNITID = S.SAMPLE_QTY_MEASUREMENTUNITID)) AS SAMP_MMNT_NAME, ACQP_CTABULAR.ASTIME, S.SAMPLE_DATE, 
                  ACQP_CTABULAR.EREAL, ACQP_CTABULAR.ELIVE, TRACER.CERT_NAME AS TRACER_CERT_NAME, S.TRACER_QTYFROM apexalpha.CI_AAD_BATCH AS B INNER JOIN
                  apexalpha.CI_AAD_SAMPLE AS S ON S.BATCHID = B.BATCHID LEFT OUTER JOIN
                  apexalpha.CI_AAS_PROCEDURE AS PR ON B.PROCEDUREID = PR.PROCEDUREID AND B.REVISION = PR.REVISION LEFT OUTER JOIN
                  apexalpha.CI_AAD_SAMPLE_ANALYSIS AS A ON S.APPROVED_ANALYSISID = A.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_ACQP1_RECORD AS ACQP1 ON A.SAMPLE_ANALYSISID = ACQP1.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_PROC1_COMMON AS PROC1 ON A.SAMPLE_ANALYSISID = PROC1.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_GEOM_RECORD AS GEOM ON A.SAMPLE_ANALYSISID = GEOM.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_ACQP_CTABULAR AS ACQP_CTABULAR ON A.SAMPLE_ANALYSISID = ACQP_CTABULAR.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAD_SAMPLE_RUN AS RUN ON RUN.CURRENT_ANALYSISID = S.APPROVED_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAS_CHAMBER AS C ON RUN.CHAMBERID = C.CHAMBERID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_ANALCNTL1_COMMON AS ANCNTL ON A.SAMPLE_ANALYSISID = ANCNTL.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_ANALCNTL2_COMMON AS ANCNTL2 ON A.SAMPLE_ANALYSISID = ANCNTL2.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_NUCL_RTABULAR AS NUCL_TAB ON A.SAMPLE_ANALYSISID = NUCL_TAB.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_NUCL_COMMON AS NUCL ON A.SAMPLE_ANALYSISID = NUCL.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_NUCL_RECORD AS NUCL_REC ON A.SAMPLE_ANALYSISID = NUCL_REC.SAMPLE_ANALYSISID AND 
                  NUCL_REC.CAMRECORD = NUCL_TAB.CAMRECORD LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_PEAK_RECORD AS PEAK ON A.SAMPLE_ANALYSISID = PEAK.SAMPLE_ANALYSISID AND 
                  NUCL_TAB.NLPEAK = PEAK.CAMRECORD LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_PEAK_COMMON AS PEAK_COMMON ON A.SAMPLE_ANALYSISID = PEAK_COMMON.SAMPLE_ANALYSISID LEFT OUTER JOIN
                  apexalpha.CI_AAS_ELEMENT_REV AS EL_REV ON PR.ELEMENTID = EL_REV.ELEMENTID AND B.ELEMENT_REVID = EL_REV.ELEMENT_REVID LEFT OUTER JOIN
                  apexalpha.CI_AAS_CERTIFICATE AS TRACER ON EL_REV.TRACER_CERTIFICATEID = TRACER.CERTIFICATEID LEFT OUTER JOIN
                  apexalpha.CI_AAS_CERTIFICATE AS CONTROL ON EL_REV.CONTROL_CERTIFICATEID = CONTROL.CERTIFICATEID LEFT OUTER JOIN
                  dbo.CI_SHL_USER AS APPUSR1 ON S.APPROVAL1_USERID = APPUSR1.USERID LEFT OUTER JOIN
                  dbo.CI_SHL_USER AS APPUSR2 ON S.APPROVAL2_USERID = APPUSR2.USERID LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_SPECIAL_RECORD AS SPEC_REC2 ON A.SAMPLE_ANALYSISID = SPEC_REC2.SAMPLE_ANALYSISID AND 
                  SPEC_REC2.CAMRECORD = 2 LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_SPECIAL_RECORD AS SPEC_REC3 ON A.SAMPLE_ANALYSISID = SPEC_REC3.SAMPLE_ANALYSISID AND 
                  SPEC_REC3.CAMRECORD = 3 LEFT OUTER JOIN
                  apexalpha.CI_AAR_CAM_SAMP_COMMON AS SAMP_COM ON A.SAMPLE_ANALYSISID=SAMP_COM.SAMPLE_ANALYSISID
WHERE (S.SAMPLE_DESCRIPTION = '2014-246')

This is the SQL code chunk in VB..

'--build SQL statement
sSQL = "SELECT DISTINCT S.SAMPLENAME, S.SAMPLE_DESCRIPTION, B.BATCH_NAME, PR.PROC_NAME, S.SAMPLESTATE, S.SAMPLE_QTY, (SELECT MMNT_NAME FROM apexalpha.CI_AAS_MEASUREMENTUNIT WHERE (MEASUREMENTUNITID = S.SAMPLE_QTY_MEASUREMENTUNITID)) AS SAMP_MMNT_NAME, ACQP_CTABULAR.ASTIME, S.SAMPLE_DATE, ACQP_CTABULAR.EREAL, ACQP_CTABULAR.ELIVE, TRACER.CERT_NAME AS TRACER_CERT_NAME, S.TRACER_QTY " & _
"FROM apexalpha.CI_AAD_BATCH AS B INNER JOIN apexalpha.CI_AAD_SAMPLE AS S ON S.BATCHID = B.BATCHID LEFT OUTER JOIN apexalpha.CI_AAS_PROCEDURE AS PR ON B.PROCEDUREID = PR.PROCEDUREID AND B.REVISION = PR.REVISION " & _
"LEFT OUTER JOIN apexalpha.CI_AAD_SAMPLE_ANALYSIS AS A ON S.APPROVED_ANALYSISID = A.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_ACQP1_RECORD AS ACQP1 ON A.SAMPLE_ANALYSISID = ACQP1.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_PROC1_COMMON AS PROC1 ON A.SAMPLE_ANALYSISID = PROC1.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_GEOM_RECORD AS GEOM ON     A.SAMPLE_ANALYSISID = GEOM.SAMPLE_ANALYSISID " & _
"LEFT OUTER JOIN apexalpha.CI_AAR_CAM_ACQP_CTABULAR AS ACQP_CTABULAR ON A.SAMPLE_ANALYSISID = ACQP_CTABULAR.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAD_SAMPLE_RUN AS RUN ON RUN.CURRENT_ANALYSISID = S.APPROVED_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAS_CHAMBER AS C ON RUN.CHAMBERID = C.CHAMBERID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_ANALCNTL1_COMMON AS ANCNTL ON A.SAMPLE_ANALYSISID = ANCNTL.SAMPLE_ANALYSISID " & _
"LEFT OUTER JOIN apexalpha.CI_AAR_CAM_ANALCNTL2_COMMON AS ANCNTL2 ON A.SAMPLE_ANALYSISID = ANCNTL2.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_NUCL_RTABULAR AS NUCL_TAB ON A.SAMPLE_ANALYSISID = NUCL_TAB.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_NUCL_COMMON AS NUCL ON A.SAMPLE_ANALYSISID = NUCL.SAMPLE_ANALYSISID " & _
"LEFT OUTER JOIN apexalpha.CI_AAR_CAM_NUCL_RECORD AS NUCL_REC ON A.SAMPLE_ANALYSISID = NUCL_REC.SAMPLE_ANALYSISID AND NUCL_REC.CAMRECORD = NUCL_TAB.CAMRECORD LEFT OUTER JOIN apexalpha.CI_AAR_CAM_PEAK_RECORD AS PEAK ON A.SAMPLE_ANALYSISID = PEAK.SAMPLE_ANALYSISID AND  NUCL_TAB.NLPEAK = PEAK.CAMRECORD LEFT OUTER JOIN apexalpha.CI_AAR_CAM_PEAK_COMMON AS PEAK_COMMON ON A.SAMPLE_ANALYSISID = PEAK_COMMON.SAMPLE_ANALYSISID LEFT OUTER JOIN apexalpha.CI_AAS_ELEMENT_REV AS EL_REV ON PR.ELEMENTID = EL_REV.ELEMENTID AND B.ELEMENT_REVID = EL_REV.ELEMENT_REVID " & _
"LEFT OUTER JOIN apexalpha.CI_AAS_CERTIFICATE AS TRACER ON EL_REV.TRACER_CERTIFICATEID = TRACER.CERTIFICATEID LEFT OUTER JOIN apexalpha.CI_AAS_CERTIFICATE AS CONTROL ON EL_REV.CONTROL_CERTIFICATEID = CONTROL.CERTIFICATEID LEFT OUTER JOIN dbo.CI_SHL_USER AS APPUSR1 ON S.APPROVAL1_USERID = APPUSR1.USERID LEFT OUTER JOIN dbo.CI_SHL_USER AS APPUSR2 ON S.APPROVAL2_USERID = APPUSR2.USERID LEFT OUTER JOIN apexalpha.CI_AAR_CAM_SPECIAL_RECORD AS SPEC_REC2 ON A.SAMPLE_ANALYSISID = SPEC_REC2.SAMPLE_ANALYSISID AND  SPEC_REC2.CAMRECORD = 2 " & _
"LEFT OUTER JOIN apexalpha.CI_AAR_CAM_SPECIAL_RECORD AS SPEC_REC3 ON A.SAMPLE_ANALYSISID = SPEC_REC3.SAMPLE_ANALYSISID AND  SPEC_REC3.CAMRECORD = 3 LEFT OUTER JOIN apexalpha.CI_AAR_CAM_SAMP_COMMON AS SAMP_COM ON A.SAMPLE_ANALYSISID = SAMP_COM.SAMPLE_ANALYSISID " & _
" WHERE SAMPLE_DESCRIPTION = ?;"
Improve this question

1 Answer 1

Reset to default
0

As I recall there is a limit of lines that you can concatenate by & _. It was approx 28 lines. Solution is to make two string variables, assign their values keeping limit of lines in mind and produce query string as query = str1 & str2. GL!

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

The code block that's on the bottom is the one that's being ran in VBA. Its only like 9 lines long. I had it broken up differently before and the error said the max length is 128 characters I believe . So I don't believe the size of the concatenated lines is the problem. Thanks for the quick reply though.
Are you trying to execute sSQL with '?' at end?
The "?" at the end of the code block above is for a parameter. Check the link I posted abovee and you'll see the code that I'm using. The SQL in that example is relatively simple.
Interesting, I am seeing such parameterization for a first time. Is query without parameter works correctly? Does parameter column consists ' marks in each parameter?
The query without the parameter does not work. I commented out the "where" clause and the section of code below it that sets up the parameter and it just gives me a run-time error 1004 SQL Syntax error. But the query I pulled straight out of SQL management studio.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.