Import SQL via TXT to .CommandText

[rynsmns]

Hello All,

I have a need to import a text file that stores a SQL query that is over 1K lines of code directly into the Command Text of a ODBC data connection in Excel. I know the query works if I manually copy/paste the code directly into the command text and refresh the connection.

Here is the code I am working with. I am using a reference in VBA to the Microsoft Scripting Runtime library:

  Dim ts As TextStream
  Dim fso
  Dim s As String
  Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Set fso = CreateObject("scripting.filesystemobject")
    Set ts = fso.OpenTextFile("c:\test\SQL.txt", ForReading, False)
    s = ts.ReadAll()
    With ActiveWorkbook.Connections("SQL Query").ODBCConnection
        .BackgroundQuery = False
        .CommandText = s
    End With
End Sub

I know this code works with a simple query like SELECT * FROM TABLE but when I attempt to use the more complicated query I get a run time error 1004 Application Defined or Object Defined Error.

I imagine this is some sort of limitation revolving around the number of lines in the code.

Does anyone have a better method or perhaps knows a workaround to the issue I am facing?

 

[par60056]

You say that the query is over 1K lines of code.

Is it possible that you are exceeding the 64K characters limit for a string and so the command is truncated?

I have not worked with the TextStream in the scripting library. Can you read in smaller chunks of the file and make an array of strings to pass to .CommandText?

 

[rynsmns]

I have refined the code a bit and I did some testing. I found that the VBA code works with a bit over 650 lines of SQL code. I am going to work and reduce the complexity of the SQL code to around that range.

Remember it requires a reference to the Windows Scripting Runtime library:

Sub ImportSQLFromText()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fso, ts, fileObj, TextLine, FileName
    Dim s As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Obtain a file object for the file.
    FileName = "c:\test\test.txt"
    Set fileObj = fso.GetFile(FileName)
  
    'Open a text stream for input.
    Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault)
    'Read the text stream into the variable.
    s = ts.ReadAll()
    'Close the text stream
    ts.Close
    With ActiveWorkbook.Connections("Shrinkage Query from BUSPROD").ODBCConnection
        .BackgroundQuery = False
        .CommandText = s
    End With
End Sub

 

[par60056]

I don't have a good way to test this.

Simplifying the query is a good idea.

Or you can try this:

Sub ImportSQLFromText()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fso, ts, fileObj, TextLine, FileName
    Dim s(5000) As String
    dim linenum as Integer
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Obtain a file object for the file.
    FileName = "c:\test\test.txt"
    Set fileObj = fso.GetFile(FileName)
  
    'Open a text stream for input.
    Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault)
    'Read the text stream into the variable.
    linenum=0
    While ts.AtEndOfStream <> True
      s(linenum) = ts.ReadLine
      linenum=linenum+1
    wend


     's = ts.ReadAll()
    'Close the text stream
    ts.Close
    With ActiveWorkbook.Connections("Shrinkage Query from BUSPROD").ODBCConnection
        .BackgroundQuery = False
        .CommandText = s
    End With
End Sub

 

[rynsmns]

I don't have a good way to test this.

Simplifying the query is a good idea.

Or you can try this:

Sub ImportSQLFromText()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fso, ts, fileObj, TextLine, FileName
    Dim s(5000) As String
    dim linenum as Integer
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Obtain a file object for the file.
    FileName = "c:\test\test.txt"
    Set fileObj = fso.GetFile(FileName)
  
    'Open a text stream for input.
    Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault)
    'Read the text stream into the variable.
    linenum=0
    While ts.AtEndOfStream <> True
      s(linenum) = ts.ReadLine
      linenum=linenum+1
    wend


     's = ts.ReadAll()
    'Close the text stream
    ts.Close
    With ActiveWorkbook.Connections("Shrinkage Query from BUSPROD").ODBCConnection
        .BackgroundQuery = False
        .CommandText = s
    End With
End Sub

Thanks for trying par, I appreciate it. It did not work. Errors out at the .CommandText = s line and says that application error or something like that. I think you are right about the 64k limit. I believe I can limit the complexity of the SQL code within the limit and maintain it like that.

I appreciate your assistance.

 

[par60056]

ISTM that whenever I recorded making a query it would come back as setting the CommandText using an array of strings (Usually in the weirdest possible way)

But I bet the end result commandtext variable is still limited to 64K so trying to shorten the query is probably best.

Anything that long I try to define a view in the database and just query with appropriate conditions from Excel.