' Runs a SQL script from an external file.
'
' @date 20071010
' @param FilePath Path to SQL script file
' @remarks http://www.thescripts.com/forum/thread195269.html

Sub ExecuteSqlScript(FilePath As String)

    Dim Script As String
    Dim FileNumber As Integer
    Dim Delimiter As String
    Dim aSubscript() As String
    Dim Subscript As String
    Dim i As Long
    
    Delimiter = ";"
    FileNumber = FreeFile
    Script = String(FileLen(FilePath), vbNullChar)
        
    ' Grab the scripts inside the file
    Open FilePath For Binary As #FileNumber
    Get #FileNumber, , Script
    Close #FileNumber
    
    ' Put the scripts into an array
    aSubscript = Split(Script, Delimiter)
    
    ' Run each script in the array
    For i = 0 To UBound(aSubscript) - 1
        aSubscript(i) = Trim(aSubscript(i))
        Subscript = aSubscript(i)
        CurrentProject.Connection.Execute Subscript
    
    Next i

End Sub

' Runs a SQL script from an external file.
'
' @date 20071010
' @param FilePath Path to SQL script file

Sub ExecuteSqlScript(FilePath As String)

    Dim Script As String
    Dim FileNumber As Integer
    
    FileNumber = FreeFile
    Script = String(FileLen(FilePath), vbNullChar)
        
    ' Grab the text inside the file
    Open FilePath For Binary As #FileNumber
    Get #FileNumber, , Script
    Close #FileNumber
    
    CurrentProject.Connection.Execute Script

End Sub

Run SQL Script From External File

Visual Basic