Solved Run SQL Batch

[sonic8]

Hi Everybody!

I've got a .sql text file containing a number (currently ~50) of individual SQL statements, all correctly terminated with a semicolon.
Is there any ready-made tool or module to run all these statements against the current Access database?

(I know how I could write such a thing myself. I just expected that something capable of this must already exist. - Google is failing me in this regard.)

PS: This is intended as one-time operation (migration of database structure) under administrator supervision. I do not intend to embed this into a user-mode application.

Best regards,
Philipp

 

[Ranman256]

paste each of them into queries,
put all queries into a macro,
run macro.

 

[sonic8]

paste each of them into queries,

Sure, this would work theoretically.
However, it is super tedious and error-prone. Furthermore I absolutely do not want to have 50+ additional queries in the database.

 

[Ranman256]

pasting into the queries will prevent errors, 2 keystrokes each, copy, paste.
50 queries will not fill up the db.

 

[sonic8]

@Ranman256, please review the PS I added to the original post.

pasting into the queries will prevent errors,

The process of copying and pasting 50 SQL statements from a file to 50 individual queries is a tedious and error prone manual process.

 

[ebs17]

My tool of choice would be called Split.
ReadFile

Sub DoIt()
    Dim sArr() As String
    Dim i As Long
    Dim db As DAO.Database
  
    Set db = CurrentDb
    sArr = Split(ReadFile("X:\Anywhere\some.sql"), ";")
    For i = 0 To UBound(sArr)
        db.Execute sArr(i), dbFailOnError
    Next
    MsgBox "ready"
End Sub

It's best to combine this into one transaction.
Attention: Additional semicolons may occur when defining parameters in the queries.

 

[sonic8]

Thank you @ebs17 , this is the direction I was thinking of.

Additional semicolons may occur when defining parameters in the queries.

This is the reason why I hoped for a some tried and tested tool, which handles this already.

 

[cheekybuddha]

Attention: Additional semicolons may occur when defining parameters in the queries.

Also within text value strings.

Since there is no proper SQL parser you can use in Access, then Split() is probably your best bet Philipp, but watching out for the usual caveats.

Or is there anything else that could better delimit the queries (eg ";" & vbNewLIne & vbNewLine)?

 

[ebs17]

However, required parameters would have to be served with values. Where should a tool get these from for automated execution?

In Jet-ready queries, semicolons can also appear in UNION queries. These would be another problem.
Or should semicolons themselves appear in names for fields and tables?

I would be concerned if you see the existing queries. Taking all the problems and all sorts of nonsense into account takes a lot of work and requires a high degree of masochism.

Addition: You could first revise the contents of the sql file before applying the split. With RegEx & Co. you could change the separators between queries to unique character strings.
Something like RegEx would necessarily be based on accurate specifications.

 

[sonic8]

Attention: Additional semicolons may occur when defining parameters in the queries.

I misunderstood this remark as "Additional semicolons may occur in literals". - This is a very real possibility, while parameter queries will never exist in my .sql file.

Or is there anything else that could better delimit the queries (eg ";" & vbNewLIne & vbNewLine)?

Wow! Why didn't I think of this. It is so simple and still solves my problem.
Of course this is not a generic solution, but for my specific purpose it is perfectly fine to use Split with a delimiter of ";" & vbCrLf.

@ebs17 and @cheekybuddha thank you both very much! Combining your input lead to the following working solution.

Sub RunUpdateBatchFile(ByVal BatchFileName As String)

    Dim sArr() As String
    Dim i As Long
    Dim db As DAO.Database
 
    Set db = CurrentDb
    sArr = Split(ReadFile(BatchFileName), ";" & vbCrLf)
    For i = 0 To UBound(sArr) - 1
       db.Execute sArr(i), dbFailOnError
    Next

End Sub

 

[cheekybuddha]

ebs17 said:
Attention: Additional semicolons may occur when defining parameters in the queries.

I misunderstood this remark as "Additional semicolons may occur in literals". - This is a very real possibility, while parameter queries will never exist in my .sql file.

I too misunderstood this - I thought Eberhard meant that semi-colons might be used as function parameter delimiters instead of commas!

thank you both very much! Combining your input lead to the following working solution.

 

[NauticalGent]

ReadFile

Very handy Function, thanks for posting that...

Sub DoIt()

Channeling your inner Paradox developer!?!?

 

[Lightwave]

Hi Everybody!

I've got a .sql text file containing a number (currently ~50) of individual SQL statements, all correctly terminated with a semicolon.
Is there any ready-made tool or module to run all these statements against the current Access database?

(I know how I could write such a thing myself. I just expected that something capable of this must already exist. - Google is failing me in this regard.)

PS: This is intended as one-time operation (migration of database structure) under administrator supervision. I do not intend to embed this into a user-mode application.

Best regards,
Philipp

OK so I am writing this and I can see its been marked as solved but here's an option that someone might find userful.
You can create a table put all the queries in a table and use a function to run the queries. I've used it a few times it can be extremely powerful in certain circumstances.
Link to blog post about it

Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL = rstZ!SQL
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL update queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

 

[sonic8]

@Lightwave, thank you for the suggestion!

You can create a table put all the queries in a table and use a function to run the queries. I've used it a few times it can be extremely powerful in certain circumstances.

I could.
However, this would immediately lead to the next question: How do I read individual SQL statements from a .sql batch file and write each of them as a record to a table. - See, you just moved the problem to another area without actually solving it.

Sorry for shooting down some suggested solutions, but I want to keep my SQL Statements in the text file. This is much more comfortable than having them in an Access table or as separate Access queries. (If you ever edited long code in an Access text box you'll understand.)

The solution outlined above worked well for me in an dry-run of the migration.
I even enhanced it to support comments in my SQL file, as long as I put the comments only between statements and also terminate them using a semicolon.
@ebs17 will be delighted. - To correctly detect the comments, I had to resort to using a RegExp even thougt looking at those makes me sick.

To dodge snarky comments from @NauticalGent, I also include my implementation of the ReadFile function. ;-)

Public Sub RunUpdateBatchFile(ByVal BatchFileName As String)

    Dim sArr() As String
    Dim i As Long
    Dim db As DAO.Database

    Set db = CurrentDb
    sArr = Split(ReadFile(BatchFileName), ";" & vbCrLf)
    For i = 0 To UBound(sArr) - 1
        If Not IsComment(sArr(i)) Then
            db.Execute sArr(i), dbFailOnError
        End If
    Next

End Sub

Private Function ReadFile(ByVal FileName As String) As String

    Const ForReading  As Integer = 1
    Const UnicodeFormat As Integer = -1
    Const AsciiFormat As Integer = 0
   

    Dim FSO As Object
    Dim File As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
       
    Set File = FSO.OpenTextFile(FileName, ForReading, False, AsciiFormat)
     
    Dim FileContent As String
    FileContent = File.ReadAll
    File.Close
   
    ReadFile = FileContent

End Function

Private Function IsComment(ByVal inputString As String) As Boolean
    Static RegEx As Object
   
    Dim matchCount As Long
   
    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        With RegEx
            .Global = True
            .IgnoreCase = True
            .Multiline = False
            .Pattern = "^(\W)*\-{2,}.*"
        End With
    End If
    matchCount = RegEx.Execute(inputString).Count
   
    IsComment = CBool(matchCount > 0)

End Function

 

[NauticalGent]

To dodge snarky comments from @NauticalGent....

Me? "Snarky"!?! That hurts...

 

[ebs17]

As the possibilities increase, so do the demands.

 

[cheekybuddha]

@Philipp, I don't think the seafaring one (@NauticalGent) was being snarky. Eberhard posted a link to an implementation of ReadFile in Post #6!