(Solved) – VBA ADOBD.Recordset won’t open if first line in .sql file is a comment

(solved)-–-vba-adobdrecordset-won’t-open-if-first-line-in.sql-file-is-a-comment

I have a macro in excel to run .sql files connecting to Oracle to extract datas.

My problem is I have a very weird “bug” based on if the first line of the .sql file is a comment line.

If there are only the “–” or the whole comment line is after the select, it will work, but if there is something after the “–” on the first line, it won’t work. (It is the same deal with /**/. Can’t put something inbetween the /* and */ else it won’t work).

Here are some example of .sql:

Example 1 (don’t work):

-- test
select 
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Example 2 (works):

--
select 
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Example 3 (works):

select 
-- test
* 
from ABC.ABC_FIELD ABCField 
where what_ever = 15

Here are the code of the VBA that might be relevant. It fail at the rs.Open line (the rs doesn’t open at all).

Public cnn As ADODB.Connection

Set cnn = New ADODB.Connection
        cnn.ConnectionString = "DSN=ABC_ORA;UID=" & UserId & ";PWD=" & Password & ";Persist Security Info=true"
cnn.Open

Dim myFile As String
Dim text As String
Dim textline As String

LaRequete = ""

myFile = Application.Worksheets(FeuilleParametres).Range("FICHIER").Value

Open myFile For Input As #1

Do Until EOF(1)
    Line Input #1, textline
    LaRequete = LaRequete & textline & " " & vbCrLf
Loop
Close #1

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open LaRequete, cnn.ConnectionString 

I hope I provided every needed information to solve this. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *