Connection fails on pass thru query (1 Viewer)

[DataMiner]

I am trying to run the following SQL pass thru query from MS Access 2002:

strSQL = "ALTER VIEW [dbo].[DemandHistoryNormalizedRefresh2] AS" & strSQL
--1st: rewrite DemandHistoryNormalizedRefresh1
DB.QueryDefs("DemandHistoryNormalizedRefresh1").SQL = strSQL
--2nd: Execute DemandHistoryNormalizedRefresh1 to rewrite view DemandHistoryNormalizedRefresh2 in Sql server
DB.Execute "DemandHistoryNormalizedRefresh1", dbFailOnError

This fails on the db.execute line, with error
Connection failed:
SQL State: 28000
SQL Server Error: 18456
Login failed for user 'pc3565k'

But, if I cancel out of the code and then immediately execute the same query, it runs fine.

AND, if I run the code from a different login, it runs fine.

AND, the following nearly identical piece of code runs fine regardless of login:

strSQL = "ALTER VIEW [dbo].[ForecastNormalizedRefresh2] AS" & strSQL
--1st: rewrite ForecastNormalizedRefresh1
DB.QueryDefs("ForecastNormalizedRefresh1").SQL = strSQL
--2nd: Execute ForecastNormalizedRefresh1 to rewrite view ForecastNormalizedRefresh2 in Sql server
DB.Execute "forecastnormalizedrefresh1", dbFailOnError

The connection strings for these two queries are identical, from what I can view in the access query properties box.

What's going on????

She Through Whom All Data Flows

 

[MSAccessRookie]

strSQL = "ALTER VIEW [dbo].[DemandHistoryNormalizedRefresh2] [COLOR=red][B]AS"[/B][/COLOR] & strSQL
--1st: rewrite DemandHistoryNormalizedRefresh1
DB.QueryDefs("DemandHistoryNormalizedRefresh1").SQ L = strSQL
--2nd: Execute DemandHistoryNormalizedRefresh1 to rewrite view DemandHistoryNormalizedRefresh2 in Sql server
 
[COLOR=red][I][B]Insert a Debug.Print or MsgBox statement here[/B][/I][/COLOR]
 
DB.Execute "DemandHistoryNormalizedRefresh1", dbFailOnError

The code above may or may not be missing a space, depending on the content of strSQL at the time of execution. If strSQL contains a leading space, then something else is causing your error. If it does not, then you have an unrecognizable string. You can insert a Debug.Print or MsgBox statement before the db.Execute and see the contents of strSQL .

 

[DataMiner]

The strSQL statement is fine. The code rewrites the pass-thru query in Access, then attemps to run it. The pass through query runs FINE outside of the code, it just won't run from within the code. IT's the statement

DB.Execute "DemandHistoryNormalizedRefresh1", dbFailOnError

which fails.

But if just stop the code and then type into the debug window:

currentDB.Execute "DemandHistoryNormalizedRefresh1", dbFailOnError

It runs fine.

Or if I just go to the query list in access and run it, it runs fine.

Or if I run the code from a different login, it runs fine.