Run .txt file to read as SQL query

[dboone25]

Good mornig all.

Trying to figure out a way to read an SQL script that is saved in a .txt format and then opened through VBA if that is even possible.

Had a search on this fantastic thread but unfortunately could not find a solution.

I have tried opening the text file with myconnection string but this comes up with an error ' object does not support this method'.

Has anyone had any luck with something similar?

Private Sub CommandButton4_Click()
' Declare variables.
Dim myFile As String
Dim FileName As Variant
Dim r As Range
Dim i As Long
Dim rc As Range
Dim rr As Long

' Select .txt file.
' Open dialog box title.
' Add title to open file dialogue box " Select .csv file to import
myFile = "CSV Files (*.txt),*.txt"
'Title = "Select .TXT File to Import"
FileName = Application.GetOpenFilename(title:="Select .txt File to Import")
                                                                          
' Select and open the dialog box.
' If no file selected open dialog box.
If FileName = False Then
MsgBox "No File Selected"
Exit Sub
End If

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset
'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=np-2;Initial Catalog=TESTDB;Integrated Security=SSPI;"
    objMyConn.Open
'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.myFile
    'objMyCmd.CommandType = adCmdText
    objMyCmd.Execute
'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open objMyCmd
    
'Loop Names'
    ' Loop to include SQL headers
    Dim title As Integer
'Copy Data to Excel'
    Workbooks.Add.Worksheets("Sheet1").Range("A2").CopyFromRecordset (objMyRecordset)
    
    For title = 0 To objMyRecordset.fields.count - 1
        ActiveWorkbook.Sheets("Sheet1").Cells(1, title + 1) = objMyRecordset.fields(title).Name
    Next title
'Close connection and open messagebox'
objMyRecordset.Close
objMyConn.Close
Set objMyRecordset = Nothing
MsgBox "Complete" ', vbOKOnly, "Finished"

End Sub

 

[Norie]

You could use something like this to read the SQL in the file into a variable,

Dim strSQL As String
Dim rw As String
Dim FF As Integer

    FF = FreeFile

    Open FileName For Input As FF

    Do Until EOF(FF)
        Line Input #FF, rw
        strSQL = strSQL & rw & vbNewLine
    Loop

    Close #FF

and then execute the SQL in the usual way.

 

[dboone25]

Thanks for the response Norie,

Going to ask a silly question as I get a run-time error '75' ( Path/File access error) would I have to include .GetOpenFilename to select the text file?

 

[Norie]

The code I posted would go after the GetOpenfilename section of your code.

In fact the code I posted uses the variable Filename which should include the path and filename for the text file the user selected.

 

[dboone25]

Thanks for the response Norie,

Going to ask a silly question as I get a run-time error '75' ( Path/File access error) would I have to include .GetOpenFilename to select the text file?

Ok still stuck with this one...


First part of the code opens up a dialog box to select the text file...

myFile = "TXTFiles (*.txt),*.txt"
'Title = "Select .TXT File to Import"
FileName = Application.GetOpenFilename(title:="Select .txt File to Import")

I have added 'FileName For Input As FF' straight after the 'declare variables' as seen..

'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset


Dim strSQL As String
Dim rw As String
Dim FF As Integer

    FF = FreeFile

    Open FileName For Input As FF

    Do Until EOF(FF)
        Line Input #FF, rw
        strSQL = strSQL & rw & vbNewLine
    Loop

    Close #FF

Then the rest of the connection string as...

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=np-2;Initial Catalog=TESTDB;Integrated Security=SSPI;"
    objMyConn.Open
'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.myFile
    'objMyCmd.CommandType = adCmdText
    objMyCmd.Execute
'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open objMyCmd
    
'Loop Names'
    ' Loop to include SQL headers
    Dim title As Integer
'Copy Data to Excel'
    Workbooks.Add.Worksheets("Sheet1").Range("A2").CopyFromRecordset (objMyRecordset)
    
    For title = 0 To objMyRecordset.fields.count - 1
        ActiveWorkbook.Sheets("Sheet1").Cells(1, title + 1) = objMyRecordset.fields(title).Name
    Next title

I have changed the section where instead of declaring the SQL text to execute the command using....

objMyCmd.strSQL

But still get a run time error '438' Object doesnt support this property or method.

Could appreciate some help with this...little lost..again..sorry.

 

[Norie]

I think you need to look at the properties of an ADO Command object https://www.google.co.uk/url?sa=t&s...yYKl88O2Fhe9tRH9A&sig2=1kC4Gulvb2Y1N57G8lmERg.

 

[dboone25]

Ok, thanks again...shall have a wee gander