SQL to VBA

[AvicTest]

Hello all,

I've been reading (lurking) these boards for a while now - all help so far has been much appreciated however, I now need something that I couldn't already find! I have been given an SQL query and asked if I can pull this data into Excel rather than having to use SQL Dev and emailing it manually every day.

I have pasted my code below, when running the query within vba I am seeing an error "SQL command not properly ended". Many thanks for any help in advance (connection details changed).


Sub Label()

Dim Cnn As Object
Set Cnn = CreateObject("ADODB.Connection")

Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")

Dim ConnectionString As String
Dim StrQuery As String


ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=111.11.11.1)(PORT=1111))" & _
"(CONNECT_DATA=(SERVICE_NAME=RTCIS))); uid=1111_ro;pwd=1111;"

StrQuery = ""
StrQuery = StrQuery & "select subsit, arecod, locatn, serflg, fulflg, height, width, depth " & vbCrLf
StrQuery = StrQuery & "from locatn " & vbCrLf
StrQuery = StrQuery & "where arecod = 'RO' and not exists " & vbCrLf
StrQuery = StrQuery & "(select 'TRUE' from unitld " & vbCrLf
StrQuery = StrQuery & " where locatn.subsit = unitld.subsit and locatn.locatn = unitld.locatn) " & vbCrLf
StrQuery = StrQuery & "and (subsit like '&&subsit' or upper ('&&subsit') = 'ALL') " & vbCrLf
StrQuery = StrQuery & "order by subsit, arecod, locatn.locatn;"

Cnn.Open ConnectionString

Cnn.CommandTimeout = 900


rst.Open StrQuery, Cnn

Sheets(1).Range("A2").CopyFromRecordset rst


End Sub

 

[6StringJazzer]

I am a little rusty on this but you don't need any of the "& VbCrLf". I do not know if they are causing a problem but the first and easiest thing I would try is removing them all.

 

[Norie]

Can you run queries in SQL Dev?

If you can then try outputting StrQuery, copying it and running it there.

 

[AvicTest]

I have removed all "& VbCrLf" but still receive the error.

Unfortunately, I do not have access to SQL DEV. I had to ask for the data I required and this was the reponse:

select subsit, arecod,locatn, serflg, fulflg, height, width, depth
from locatn
where arecod = 'RO' and not exists
(select 'TRUE' from unitld
where locatn.subsit = unitld.subsit and locatn.locatn = unitld.locatn)
and (subsit like '&&subsit' or upper ('&&subsit') = 'ALL')
order by subsit, arecod, locatn.locatn;

 

[AvicTest]

It was simply because the query ended in a semi colon !