How to leave SQL comments in Access when queries are throw to SQL Server?

Question

we have an application that is in Access forms accessing to SQL Server. We want to keep SQL's comment into the SQL code of our queries, but this code throw an exception (3075) :

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = "SELECT /* 123456 */ NAA_CODE_NATURE_PK, NAA_LIBELLE_NATURE FROM RGZ_NATURES_AFFAIRE ORDER BY NAA_LIBELLE_NATURE"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

We have tried with dbExecDirect, dbSQLPassThrough and finally dbExecDirect + dbSQLPassThrough as the third argument of the OpenRecordset method, but this does not works at all...

Any solution ?

Thanks

I don't see anything wrong with that query. You say it throws an exception, what is the full exception? `3075` means little on it's own. — Thom A, Jun 16 '20 at 15:41
in french "erreur de syntaxe (opérateur absent) dans l'expression "/* toto */ NAA_CODE_NATURE_PK" — SQLpro, Jun 16 '20 at 15:53

Andre · Answer 1 · 2020-06-16T16:24:50.107

1

It works with a Pass-Through query (and only with that), but not like you have tried.

You cannot use comments in Access SQL, only crude workarounds.

The best way is with a querydef object where you set the connect string.

Sub TestSqlComment()

    Dim DB As DAO.Database
    Dim QD As DAO.QueryDef
    Dim RS As DAO.Recordset
    Dim strSQL As String

    Set DB = CurrentDb
    Set QD = DB.CreateQueryDef("")
    ' Set connect string of Pass-Through query to the connect string of an existing linked table
    QD.Connect = DB.TableDefs("RGZ_NATURES_AFFAIRE").Connect

    strSQL = "SELECT /* 123456 */ NAA_CODE_NATURE_PK, NAA_LIBELLE_NATURE FROM RGZ_NATURES_AFFAIRE ORDER BY NAA_LIBELLE_NATURE"
    QD.Sql = strSQL 
    Set RS = QD.OpenRecordset(dbOpenSnapshot)
    Debug.Print RS(0)
    RS.Close

End Sub

Of course you can move all this overhead into a helper function that returns the recordset.

It should also be noted that Pass-Through queries are read-only, so this may be of limited use for a form.

edited Jun 16 '20 at 16:24

answered Jun 16 '20 at 16:18

Andre

26,751
7
36
80

Well... no easy solution, because we cannont rewrite all the application code in this way and having the recordset in readonly mode will be inoperable... – SQLpro Jun 17 '20 at 07:04
Then the answer to your question is: *You can't.*, or use one of the "workarounds". -- If your SQL code is in VBA, then the obvious solution would be to put the comments in VBA. @SQLpro – Andre Jun 17 '20 at 07:43
We needs to have these comments passed through SQL Server to sees them in the SQL Server profiler.... – SQLpro Jun 17 '20 at 16:32
You are looking for something that isn't possible. Even if Access SQL would support comments, they would get filtered out between Access, the ODBC driver, and the server. Only PT queries are sent as-is to the server (with comments), but their result is [read-only](https://stackoverflow.com/questions/18898032/how-to-make-a-passthrough-passthru-query-editable). @SQLpro – Andre Jun 17 '20 at 18:39
I think the only way is to add a false WHERE clause with somtehing like 1 = 1, but with some GUID... – SQLpro Jun 18 '20 at 21:27

score 0 · Answer 2 · answered Jun 16 '20 at 16:05

How do you declare the rs? It should be DAO.Recordset, just like your DAO.Database declaration. The full code would be like this:

Dim strSQL As String
Dim dbs As DAO.Database
DIM rs As DAO.Recordset ' adding this line
Set dbs = CurrentDb
strSQL = "SELECT /* 123456 */ NAA_CODE_NATURE_PK, NAA_LIBELLE_NATURE FROM RGZ_NATURES_AFFAIRE ORDER BY NAA_LIBELLE_NATURE"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The error might be because Access is trying to use the ADO version of recordset.

How to leave SQL comments in Access when queries are throw to SQL Server?

2 Answers2