Error 2128 on import of SQL query, which use "UNION ALL"

[Tanarri]

Hi,

on "rebuild from source" on my own access-accdb I get an error on importing a special query.

In my project I have 1100 queries, and only two queries which uses "UNION ALL" fail on import. On the export of the database I set the checkbox "Save Query SQL" and on "build from source" I activated "Force import of original SQL of queries"

As a temponary workaroud I added on error resume next at "modVCSUtilities.LoadComponentFromText" , because I don't know how to prevent the error better, but it didn't help. Those 2 queries won't be imported.

    ' Only run conversion if needed.
    If blnConvert Then
        ' Perform file conversion, and import from temp file.
        strTempFile = GetTempFile
        If intType = acModule Then
            ' Convert back to ANSI for VBA modules
            ConvertUtf8Ansi strSourceFile, strTempFile, False
        Else
            ' Other objects converted to UCS2
            ConvertUtf8Ucs2 strSourceFile, strTempFile, False
        End If
        Perf.OperationStart "App.LoadFromText()"
        ' WORKAROUND, didn't help
        On Error Resume Next
        Application.LoadFromText intType, strName, strTempFile
        ' WORKAROUND
        On Error GoTo 0
        Perf.OperationEnd
        DeleteFile strTempFile, True
    Else

A error file "error.txt" is written by access 2013 for the error on import in the directory of the newly builded access-accdb. Content:

Beim Importieren des Objekts 'qry_frm_beraterrechte_berater' wurde von Microsoft Access ein Fehler festgestellt.

In Zeile 13 wurde ein Fehler entdeckt.
Ressourcenfehler

Here is one of the query, which generates the import error. (I have added *.txt so github accepts the files) qry_frm_beraterrechte_berater.sql.txt qry_frm_beraterrechte_berater.bas.txt

Any hints?

More Infos:

• Yes, the query is fully functional in my programm, and is called many times in my forms
• not, the table "st_berater" does not exist at the moment, when vcs is importing the query
• I manually executed Application.loadfromtext 1 ,"qry_frm_beraterrechte_berater","C:\Users\<snipped>\Desktop\qry_frm_beraterrechte_berater.bas" in my access-accdb (table "st_berater" exists) and got also an error 2128

Greetings Tanarri

[hecon5]

Are these linked / passthrough queries, or local? I had some issues with this, and making them linked seems to help.

[joyfullservice]

This actually sounds a lot like the issue discussed in #76. Testing on my system, I can reproduce the same issue. If I create a query and paste in the SQL, it saves fine, exports and imports fine. But if I edit the query in the designer then export it, it changes the format, (as described in #76) and it no longer imports correctly.

Ignoring the error and using the option to force the import of the original SQL will probably make it work, but ideally it would be good to see if we can resolve the error. (Or at least clearly pinpoint what the problem is.)

[hecon5]

@joyfullservice, setting the .SQL works for me, but you're right, it does mess up the designer view; on these more complex queries, I can see how some users wouldn't like that.

@Tanarri; it looks like you forced SQL import, right?

Also, looking at the query: I see UNION ALL\015\012SELECT, and no "[Space]" after the first query.

This and this seem to point to an unusual experience I've had in Access SQL: if you have a newline (\015\012) without a space before the line on some queries (specifically Union), the SQL will sometimes be incorrectly parsed as UNIONSELECT, which isn't valid. What happens when you add a space? (I know, this is an absolutely dumb fix, but just thinking aloud).

[hecon5]

(I know, the second one is pertaining to field names with spaces, but the effect was the same for me)

[Tanarri]

Hi all,

I did more test and would like to share the results:

First, here is the original SQL text of the query:

PARAMETERS pBerater Long, pFirma Short;
SELECT Final.Rechtename, IIf(Sum([hasRight])>=1,"Erlaubt","Verweigert") AS Recht
FROM (SELECT r.RechteID, r.RechteName, IIf(Sum(counter)>=1,1,0) AS hasRight
FROM tbl_rechte AS r LEFT JOIN (SELECT rr.*, IIF(NZ(rr.RollenID,0)<>0,1,0) AS [counter]
    FROM tbl_beraterrollen AS ar INNER JOIN tbl_rollenrechte AS rr ON ar.RollenID = rr.RollenID
    WHERE (ar.Berater=[pBerater]) AND (ar.Firma=[pFirma]))  AS br ON r.RechteID = br.RechteID
GROUP BY r.RechteID, r.RechteName
    UNION ALL
SELECT r.RechteID, r.RechteName, IIF(a.Einstellung=True,1,-1) AS hasRight
FROM tbl_rechte AS r INNER JOIN tbl_beraterrechte_anpassung AS a ON r.RechteID = a.RechteID
WHERE (((a.Berater)=[pBerater]) AND (a.Firma=[pFirma]))
)  AS Final
GROUP BY Final.Rechtename, Final.RechteID;

@hecon5 :

• There are no field names with spaces
• The tables are linked to a MS-SQL Server

Next, I created a new query in my access-db with the above SQL Text (executed it and it worked) Then I exported the query and imported the query with following commands, and the it worked correctly!

Application.saveastext 1, "qry_frm_beraterrechte_berater","C:\Users\albiker_e\Desktop\qry_frm_beraterrechte_berater.bas" 
Application.loadfromtext 1 ,"qry_frm_beraterrechte_berater","C:\Users\albiker_e\Desktop\qry_frm_beraterrechte_berater.bas" 

The I opened the query in designer and saved it! I could export the query, but the exported .bas file looked different. And this .bas file I wasn't able to import again --> Error 2128 --> also on every attempt to import the query there will be created a new error.txt file Content of a example error.txt file

Beim Importieren des Objekts 'qry_frm_beraterrechte_berater' wurde von Avtg ein Fehler festgestellt.

In Zeile 13 wurde ein Fehler entdeckt.
Ressourcenfehler

Importing the defect query with V2.3.3 with "build from source" and enabled "Force import of original SQL of queries" wasn't also not successful, because "modVCSUtilities.LoadComponentFromText" has no error-handler. So the import raised an error. But I think there is a chance to import the query even if the .bas raised an error in "modVCSUtilities.LoadComponentFromText" (Correct me if I am wrong) We had to to:

• Add error handling in "modVCSUtilities.LoadComponentFromText" and return a errorvariable to the calling procedure
• then in the block "clsDbQuery_Import" we had to ADD a new QueryDef if it doesn't exist. At the moment the procedure assumes that the QuerDef exists, which can be, but don't have to.
• set the SQLText

Greetings

[hecon5]

@Tanarri, @joyfullservice, adding error handling here would be good, here's my suggestion:

• Add error handling (catch/catchall) in the query.

• Use Force SQL Import option to set the sql on error; this cuts down on prompts to user during import.

• If the error is 2128 and Force SQL Import is not set, ask the user if they want to just import the SQL. Then setup the query, and set the sql, like @Tanarri did. (The integrated VCS fork I use this is actually what we do, and while it loses the visual capabilities, 90% of my queries are built by either SQL directly or programmatically, so it's a non-issue for me to lose the Query editor display).

[hecon5]

Actually, I think a better way to handle this is to modify LoadComponentFromText to only import the name and connection string (for pass-through queries) if Options.ForceImportOriginalQuerySQL is true.

Then set the SQL.

We should still add error handling, too.

[hecon5]

@Tanarri

The tables are linked to a MS-SQL Server

Does this mean they're "local" tables which Access then grabs (I see dbo_names, which is typical for this case) or is the query a "Passthrough" where Access just sends the whole query along to the server to handle? I don't see a connection string in your provided query.bas file, so this is my assumption. In these cases, I had a lot of luck with just making the query a Passthrough, and then it imports fine. Depending on the query complexity, it may also give you a pretty significant performance boost, too.

However: this would require your users to have rights to pass queries through, and not all servers will allow this.

@joyfullservice: is there a handy way to get the connection string and other properties from the .bas file? The only one I see that's evident is the query name, but getting the connection string would be real useful. I see dbMemo is a field and connect has an entry, is there a parser you've built/have?

Example Query Passthrough (Uses UNION) file

dbMemo "SQL" ="SELECT DISTINCT t.ID, t.Field\015\012FROM dbo.tblName AS t\015\012INNER JOI"
    "N dbo.qrySomeQuery\015\012AS q ON t.ID= q.ID\015\012UNION SELECT '', "
    "'(ALL)' \015\012FROM dbo.tblName AS t\015\012ORDER BY t.ID;\015\012\015\012"
dbMemo "Connect" ="ODBC;Description=SQLServerzTest;DRIVER=ODBC Driver 13 for SQL Server;SE"
    "RVER=SERVERNAME.DOMAIN.SOMETHING;Trusted_Connection=Yes;APP=Microsoft "
    "Office 2013;DATABASE=DBNAMMEEEEE;Encrypt=Yes;ApplicationIntent=READWRITE;\015\015"
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
Begin
End

This could either allow us to make a query via the "slow" method of making a query and then setting the properties separately, or just grab the bits we need.

Thoughts?

[Tanarri]

@hecon5 The Query is no passthrough query. It a "normal" query which gets the data from a MS-SQL Server.

More tests: I did recreate the query with the original SQL text and did not(!) go to design view

PARAMETERS pBerater Long, pFirma Short;
SELECT Final.Rechtename, IIf(Sum([hasRight])>=1,"Erlaubt","Verweigert") AS Recht
FROM (SELECT r.RechteID, r.RechteName, IIf(Sum(counter)>=1,1,0) AS hasRight
FROM tbl_rechte AS r LEFT JOIN (SELECT rr.*, IIF(NZ(rr.RollenID,0)<>0,1,0) AS [counter]
    FROM tbl_beraterrollen AS ar INNER JOIN tbl_rollenrechte AS rr ON ar.RollenID = rr.RollenID
    WHERE (ar.Berater=[pBerater]) AND (ar.Firma=[pFirma]))  AS br ON r.RechteID = br.RechteID
GROUP BY r.RechteID, r.RechteName
    UNION ALL
SELECT r.RechteID, r.RechteName, IIF(a.Einstellung=True,1,-1) AS hasRight
FROM tbl_rechte AS r INNER JOIN tbl_beraterrechte_anpassung AS a ON r.RechteID = a.RechteID
WHERE (((a.Berater)=[pBerater]) AND (a.Firma=[pFirma]))
)  AS Final
GROUP BY Final.Rechtename, Final.RechteID;

Then I exported the query and imported the query with following commands, and they worked correctly also after importing!

Application.saveastext 1, "qry_frm_beraterrechte_berater","C:\Users\albiker_e\Desktop\qry_frm_beraterrechte_berater.bas" 
Application.loadfromtext 1 ,"qry_frm_beraterrechte_berater","C:\Users\albiker_e\Desktop\qry_frm_beraterrechte_berater.bas" 

Content of the exported qry_frm_beraterrechte_berater.bas (no design view, only SQL Text)

dbMemo "SQL" ="PARAMETERS pBerater Long, pFirma Short;\015\012SELECT Final.Rechtename, IIf(Sum("
    "[hasRight])>=1,\"Erlaubt\",\"Verweigert\") AS Recht\015\012FROM (SELECT r.Rechte"
    "ID, r.RechteName, IIf(Sum(counter)>=1,1,0) AS hasRight\015\012FROM tbl_rechte AS"
    " r LEFT JOIN (SELECT rr.*, IIF(NZ(rr.RollenID,0)<>0,1,0) AS [counter]\015\012   "
    " FROM tbl_beraterrollen AS ar INNER JOIN tbl_rollenrechte AS rr ON ar.RollenID ="
    " rr.RollenID\015\012    WHERE (ar.Berater=[pBerater]) AND (ar.Firma=[pFirma]))  "
    "AS br ON r.RechteID = br.RechteID\015\012GROUP BY r.RechteID, r.RechteName\015\012"
    "    UNION ALL\015\012SELECT r.RechteID, r.RechteName, IIF(a.Einstellung=True,1,-"
    "1) AS hasRight\015\012FROM tbl_rechte AS r INNER JOIN tbl_beraterrechte_anpassun"
    "g AS a ON r.RechteID = a.RechteID\015\012WHERE (((a.Berater)=[pBerater]) AND (a."
    "Firma=[pFirma]))\015\012)  AS Final\015\012GROUP BY Final.Rechtename, Final.Rech"
    "teID;\015\012"
dbMemo "Connect" =""
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbBoolean "OrderByOn" ="0"
dbByte "Orientation" ="0"
dbByte "DefaultView" ="2"
dbBinary "GUID" = Begin
    0x99ff7f7c2e1b924595ffb5793520eb38
End
dbBoolean "FilterOnLoad" ="0"
dbBoolean "OrderByOnLoad" ="-1"
dbByte "PublishToWeb" ="1"
Begin
End

Please not the tag "dbMemo Connect = " has no content. But this is no problem (I think), because the query is no passthrough query.

If the query was ever opened in design view the exported .bas file looks different. It looks like the query is totaly rewritten in a Subquery and "grouping" on top level. I can't see a chance to import a such a messed up query again correctly.

Content of qry_frm_beraterrechte_berater.bas: (if opened and saved in design view)

Operation =1
Option =0
Begin InputTables
    Name ="SELECT r.RechteID, r.RechteName, IIf(Sum(counter)>=1,1,0) AS hasRight\015\012FRO"
        "M tbl_rechte AS r LEFT JOIN (SELECT rr.*, IIF(NZ(rr.RollenID,0)<>0,1,0) AS [coun"
        "ter]\015\012    FROM tbl_beraterrollen AS ar INNER JOIN tbl_rollenrechte AS rr O"
        "N ar.RollenID = rr.RollenID\015\012    WHERE (ar.Berater=[pBerater]) AND (ar.Fir"
        "ma=[pFirma]))  AS br ON r.RechteID = br.RechteID\015\012GROUP BY r.RechteID, r.R"
        "echteName\015\012    UNION ALL\015\012SELECT r.RechteID, r.RechteName, IIF(a.Ein"
        "stellung=True,1,-1) AS hasRight\015\012FROM tbl_rechte AS r INNER JOIN tbl_berat"
        "errechte_anpassung AS a ON r.RechteID = a.RechteID\015\012WHERE (((a.Berater)=[p"
        "Berater]) AND (a.Firma=[pFirma]))\015\012"
    Alias ="Final"
End
Begin OutputColumns
    Expression ="Final.Rechtename"
    Alias ="Recht"
    Expression ="IIf(Sum([hasRight])>=1,\"Erlaubt\",\"Verweigert\")"
End
Begin Parameters
    Name ="pBerater"
    Flag =4
    Name ="pFirma"
    Flag =3
End
Begin Groups
    Expression ="Final.Rechtename"
    GroupLevel =0
    Expression ="Final.RechteID"
    GroupLevel =0
End
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbBoolean "OrderByOn" ="0"
dbByte "Orientation" ="0"
dbByte "DefaultView" ="2"
dbBinary "GUID" = Begin
    0x99ff7f7c2e1b924595ffb5793520eb38
End
dbBoolean "FilterOnLoad" ="0"
dbBoolean "OrderByOnLoad" ="-1"
dbByte "PublishToWeb" ="1"
dbByte "RecordsetType" ="0"
dbBoolean "TotalsRow" ="0"
Begin
    Begin
        dbText "Name" ="Final.Rechtename"
        dbLong "AggregateType" ="-1"
    End
    Begin
        dbText "Name" ="Recht"
        dbLong "AggregateType" ="-1"
    End
End
Begin
    State =0
    Left =0
    Top =0
    Right =1242
    Bottom =851
    Left =-1
    Top =-1
    Right =1226
    Bottom =555
    Left =0
    Top =0
    ColumnsShown =543
    Begin
        Left =48
        Top =12
        Right =192
        Bottom =156
        Top =0
        Name ="Final"
        Name =""
    End
End

Suggestions welcome...

[Tanarri]

@hecon5 Your thoughts in https://github.com/joyfullservice/msaccess-vcs-integration/issues/165#issuecomment-778157035 are the correct way to import - I think: -> Create QueryDef + Set SQL + Set "Connect" if not empty + something else?

[joyfullservice]

@joyfullservice: is there a handy way to get the connection string and other properties from the .bas file? The only one I see that's evident is the query name, but getting the connection string would be real useful. I see dbMemo is a field and connect has an entry, is there a parser you've built/have?

I have not yet done any special parsing on the exported query .bas files. (But I am considering it for another situation where the column name definitions seem to come and go in exported pass through queries that execute stored procedures. For that, I am leaning towards a "sanitize" function, similar to what we do for forms and reports.)

The problem of Access creating an export file that cannot successfully be imported seems to be quite rare, and only involves queries with certain characteristics like more complex joins/unions/parameters. One thought would be to take the normal approach of importing the .bas file wherever possible (for the advantages of faster speed and more original information preserved) and use the .sql overlay (ForceImportOriginalQuerySQL) only for those cases where the direct import of the object fails.

One important consideration here is whether or not Access will silently import an invalid query export without any errors till the query is actually run. This would be a more serious issue that would almost necessitate the sql overlay on all the queries since we don't really have another way to determine whether the imported query is valid or not. However, I don't think this is the case. From what I understand, LoadFromText will throw an error, and an error file will be created any time the file cannot be imported.

If the above assumption is correct, (that Access will always throw an error) then I believe we should be safe to take the approach of only doing the sql overlay when encountering an error during the import. This would require creating *.sql files for each query, but I would expect that this is typically being done anyway. With this in place, the main purpose of the ForceImportOriginalQuerySQL option will be for those that are editing the SQL files externally and prefer to make their changes that way instead of through the Access designer/editor.

@Indigo744 - Do you have any thoughts on this? I know you had encountered this issue in #76 and added the option for the sql overlay after importing the queries.

[hecon5]

@Tanarri and @joyfullservice, after looking at the difference: on error, perhaps run a sanitize-light like @joyfullservice mentioned, scrub out the SQL, output columns, guid, anything else we figure out 'breaks' it, re-import it, then set SQL from the SQL file, this would avoid having to actually parse the relevant bits, and let Access handle that.

As to the SQL: Access requires at least an "SELECT" statement, so we'll have to figure out how to deal with that while scrubbing.

[joyfullservice]

This seems to be related to #219 where the internal query optimizer in Microsoft Access is making changes to the queries. Since we don't really have any control over the internal query optimizer, I propose that we should go ahead and close this issue as out of scope for this project. (See my comment here regarding project scope)