Hi,
I have an access project which is liked via OLE DB to a SQL Server. It uses NT authentication.
I want to create a query on it. As far as I can see (new to Access projects) the way to do this is to create a new view, which actualy does create a view in the SQL Server db.
So I have given myself 'create view' privileges on the SQL Server.
I create the view - fine. When I go to save it, it fails because whatever name I give to it, the Access project puts my username in front before it passes it to the SQL Server.
This is what the profiler says is trying to be run:
select * from CREATE VIEW <domain>\<user>.View2
AS
SELECT *
FROM TestIPAllFields
Now this fails because of the '\' and Access tells me that.
The name of the view needs to be enclosed in square brackets before SQL server will allow it.
Has anyone come across this problem before, and how did you solve it?
Actually, I would prefer if possible to allow users to create standard Access queries (not stored on the SQL Server at all) if this is possible. I tried creating an Access database and linking the tables through from the Access project, unfortunately when I went to select them it only offered me the tables - and it's actually the views I need!! Any suggestions?
I have an access project which is liked via OLE DB to a SQL Server. It uses NT authentication.
I want to create a query on it. As far as I can see (new to Access projects) the way to do this is to create a new view, which actualy does create a view in the SQL Server db.
So I have given myself 'create view' privileges on the SQL Server.
I create the view - fine. When I go to save it, it fails because whatever name I give to it, the Access project puts my username in front before it passes it to the SQL Server.
This is what the profiler says is trying to be run:
select * from CREATE VIEW <domain>\<user>.View2
AS
SELECT *
FROM TestIPAllFields
Now this fails because of the '\' and Access tells me that.
The name of the view needs to be enclosed in square brackets before SQL server will allow it.
Has anyone come across this problem before, and how did you solve it?
Actually, I would prefer if possible to allow users to create standard Access queries (not stored on the SQL Server at all) if this is possible. I tried creating an Access database and linking the tables through from the Access project, unfortunately when I went to select them it only offered me the tables - and it's actually the views I need!! Any suggestions?