Having issues passing a Variable to RS.Open

[amit.narayan.rai]

Hi,

I am trying to pull data from Oracle Database to Excel using OLEDB.

Here is the code...which is giving me problems..

Dim SqlScriptName As String
    Dim SqlScript As String
    Dim FilePath As String
Sub SQLScripts()
SqlScriptName = frmInput.lstSQLScripts.Value


    Dim RS As ADODB.Recordset
    Dim col As Integer
    Dim row As Integer
        SqlScriptName = frmInput.lstSQLScripts.Value
        Sheets(SqlScriptName).Visible = True
        Sheets(SqlScriptName).Select
        Call CleanUniversal


        Set cN = New ADODB.Connection
        Set RS = New ADODB.Recordset
  
        SqlScript = GetFileContent(FilePath)
    
cN.Open strConnect
                        
RS.Open SqlScript, cN
   
' Now actual data as fetched from select statement
  col = 0
  row = 1
  Do While Not RS.EOF
    row = row + 1
    col = 0
    
    Do While col < RS.Fields.Count
      Cells(row, col + 1) = RS.Fields(col).Value
      col = col + 1
    Loop
  
    RS.MoveNext
  Loop
End Sub

GetFileContent is a small function which parses data from a text file and puts into SqlScript variable.

The issue is when I try to pass the actual script to RS.Open it works fine...like

RS.Open "Select * from TABLE_NAME, cN

but when i use the function GetFileContent to read the same query lying outside...it does not work..however it does not give me an error too..

Here is the code for GetFileContent..

Function GetFileContent(Name As String) As String
    Dim intUnit As Integer
    
    On Error GoTo ErrGetFileContent
    intUnit = FreeFile
    Open Name For Input As intUnit
    GetFileContent = Input(LOF(intUnit), intUnit)
ErrGetFileContent:
    Close intUnit
    Exit Function
End Function

I have tried to Debug the Code and SqlScript variable correctly imports the text from text file.

Any help will be very much appreciated..

Thanks
Amit Narayan

 

[Andrew Poulsom]

I would do this:

Debug.Print SqlScript

and make sure that what's in the Immediate Window is exactly what you expect.

 

[amit.narayan.rai]

Thanks Andrew...I have tried that and it seems that when I am fetching the Text from Text file using GetFileContent function the variable which I need to replace is coming in String format..

example....
function is fetching query like this

select * from TABLE_NAME where COLUMN = '" & VARIABLE "'

VARIBALE is a vb variable which gets assigned from a USERFORM like this : frmInput.cboStudy2.Value

when I passing direct query instead of SqlScript..it is working fine..

Thanks

 

[Kyle123]

You can't do that I'm afraid, you will need to replace the bits in your string with variables in code, for example, you can wrap variables in curly brackets:

SqlScript = "select * from TABLE_NAME where COLUMN = '{VARIABLE1}'"

Then you can use

sqlscript = replace(slqscript,"{VARIABLE1}", MyVBAVariable)

 

[amit.narayan.rai]

Thanks for your reply Kyle....Fortunately..I was able to do exactly this...and just wanted to thank all you guys for awesome support...

Cheers..