MS Access Project uses incorrect SQL if RecordSource starts with leading space.

I had a MS Access 2002 VB code


Form.RecordSource = ” Select * from dbo.[TempBarcodesList]  where SessionID= ” & Quoted(hdnSessionID)


with a leading space before Select in SQL statement.


Profiler showed that the following SQL was send to the database 


 exec sp_executesql N’SELECT * FROM   Select * from dbo.[TempBarcodesList]  where SessionID= ”2006-02-22 12:07:06.327”  WHERE “SessionId” = @P1 AND “AutoID” = @P2′, N’@P1 varchar(23),@P2 bigint’, ‘2006-02-22 12:07:06.327’, 22 


and it obviosly had sintax errors.


When the leading space  was remove access inserted brackets and generated SQL became correct:


exec sp_executesql N’SELECT * FROM ( Select * from dbo.[TempBarcodesList]  where SessionID= ”2006-02-22 11:49:48.043” ) AS DRVD_TBL WHERE “SessionId” = @P1 AND “AutoID” = @P2′, N’@P1 varchar(23),@P2 bigint’, ‘2006-02-22 11:49:48.043’, 12 


 


 

Advertisements