How can I remove rows starting with ' in a variant?

Question

Following the answer to this question, I execute different queries stored in a .txt file using the following code:

   Dim vSql       As Variant
   Dim vSqls      As Variant
   Dim strSql     As String
   Dim intF       As Integer

   intF = FreeFile()
   Open "MyFile.txt" For Input As #intF
   strSql = Input(LOF(intF), #intF)
   Close intF
   vSql = Split(strSql, ";")

   On Error Resume Next
   For Each vSqls In vSql
      DoCmd.RunSQL vSqls
   Next

The queries are separated by ;, and each query is preceded by a comment line, starting with ' and ending with ;(so that it gets split by vSql = Split(strSql, ";") ).

The resulting vSql variant is composed of valid SQL statements, interspersed with comment lines starting with '. The current code works, but I would like to remove the On Error Resume Next so that a faulty SQL statement returns an error instead of being ignored.

How can I remove the rows starting with ' in the variant before executing the For Each loop? Or is there another way to accomplish my goal? The comment character can be changed if necessary.

In your text files, can you replace the comment character with SQL comment character "--"? — tsdn, May 31 '19 at 08:58
Good idea, but I'm not sure Access handles SQL comments. I'll see if it works. — Damien Cateau, May 31 '19 at 09:00
@JK2017 Your proposition works, Accss seem to handle `--` comments. — Damien Cateau, May 31 '19 at 09:10

score 3 · Accepted Answer · answered May 31 '19 at 09:06

3

Alternatively, this should do it to:

For Each vSqls In vSql
    If Not CStr(vSqls) Like "'*" Then DoCmd.RunSQL vSqls
Next

answered May 31 '19 at 09:06

tsdn

415
4
10

score 0 · Answer 2 · answered May 31 '19 at 09:02

It depends on exactly how the file is stored, but the simplest way is to find the first return character and take the rest of the text.

vSqls=mid(vSqls = Mid(vSqls, InStr(vSqls, vbCrLf) + 2)

or

vSqls=mid(vSqls = Mid(vSqls, InStr(vSqls, vbCr) + 1)

Bear in mind that this will only remove the first line so if there are blanks you will need to check whether the next line starts with a '.

And you might find that your queries don't work if they are spread across more than one line that you might need to deal with that by replacing any remaining carriage returns with a space.

How can I remove rows starting with ' in a variant?

2 Answers2