May 18, 2009 at 11:06 am
I am trying to run the following 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????
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
May 19, 2009 at 5:24 am
I'm guessing you have 2 Access databases set up as linked servers to SQL 2005 - and the problem is with one of them only.
If so - some things to check.
Have you looked at the file locations and permissions of the access databases. Are they on different networked drives?
Do both have the same security settings in Access (Tools...Security menu)
Finally - I've found these linked Server Objects a bit tempremental myself, try removing the Linked Servers and re-adding them, making sure they are both set up in the same way. Make a note of the properties before removing - especially of the working one.
May 19, 2009 at 10:11 am
These are both in the same db, linked to sql server 2005. For now I have solved the problem by unchecking the "use trusted connection" from the problem query, and adding the sql user name and pwd to the odbc connection string. The trusted connection thing works for all of my other pass-thru queries, just not this one.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply