Execute .sql file from ADO

[snakehips2000]

I'm looking to execute a SQL Server script which I've saved as a .sql file, from Excel VBA, and export the results into another Excel workbook via ADO.

Could someone please supply a simple example?

It's the referencing of the .sql file which I can't get my head around. Presumably, I also need to use the CopyFromRecordset method at some stage too?

Thanks

 

[SkipVought]

Here's an example VBA function using ADO...

Function GetIMT(sPN As String) As String
'SkipVought/2006 Sep 25/
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
':this function returns IMT Owner for a given Part Number
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010prod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT RC.OWNER "
    sSQL = sSQL & "FROM FPRPTSAR.PART_MASTER PM "
    sSQL = sSQL & "  ,  FPRPTSAR.RESPONSIBILITY_CODES RC "
    sSQL = sSQL & "WHERE PM.PART_id ='" & Trim(sPN) & "' "
    sSQL = sSQL & "  AND PM.RESP_CODE=RC.RESP_CODE"
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
               
    On Error Resume Next
               
    rst.MoveFirst
    
    If Err.Number = 0 Then
        GetIMT = rst(0)
    Else
        GetIMT = "NONE"
    End If

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

If you need to return data to a sheet, then the CopyFromRecordset method would be used...

  SomeSheetObj.SomeRangeObj.CopyFromRecordset rst

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[snakehips2000]

Thanks for the response. I think I've either understated the problem or you've overlooked something.

I'm trying to use the script contained within the actual .sql file itself, i.e. not hard-code the "SELECT ..." statement as per your example.

So, I need something which accepts the path to the file and then executes the file's contents.

Thanks

 

[PHV]

You don't know how to read a text file in VBA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

not sure that basic read will work for a .sql file.

With reference set to microsoft scripting runtime:

Dim FSO As FileSystemObject
Dim tsInput As TextStream
Dim strFile As String
Dim strQuery As String

strFile = "Full\Path\To\File.sql"
Set FSO = New FileSystemObject
Set tsInput = FSO.OpenTextFile(strFile, 1)

Do While Not tsInput.AtEndOfStream

strQuery = strQuery & vbCrLf & tsInput.ReadLine

Loop
tsInput.Close
Set FSO = Nothing

will read the contents of a .sql file to a string variable

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question