January 29, 2005 at 10:25 am
January 30, 2005 at 12:47 pm
Here is a way to do it, but that possibly defeats the purpose of the password.
Create a second non-password protected database. From this second database, link to the tables in the first database. You have to provide the password just the first time when you create the link.
Change your linked server in SQL to point to the second database. You can then update and select data. When looking at the linked server tables, I could not see my test table. I was, however, able to do inserts and selects from within Query Analyzer.
I like Access a lot as a GUI for SQL, but Access security is a dog!
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 30, 2005 at 7:43 pm
Thanks Kathi,
Good thinking, client has already front-ended with un-secured dbs so security not an issue.
Battling with updates though. Perhaps I should be using a cursor?
WORKING OK WITH LINKED ACCESS DB:
EXEC sp_addlinkedserver
@server = 'LINKDB',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\DATA03\PROJECTS\MSAccess\LINKPasswordTest.mdb' (manually set property "not using security context)
SELECT *
FROM OPENQUERY(LINKDB, 'SELECT * FROM tblMYPASSTEST')
NOT WORKING:
UPDATE mytbl
SET myField = 'hello'
FROM OPENQUERY(LINKDB, 'SELECT * FROM tblMYPASSTEST') As mytbl
Above is on my dev-laptop however I shall probably have to create a linked-server-login for use on client network. User will be a single ASPNET account which does not have access to the remote resources.
January 31, 2005 at 10:30 pm
PS:
Worked out the update syntax...
update OPENQUERY(LINKSRV,'select statement') set myfield = 'xyz'
Works great until domain access to remote servers becomes an issue, then I get errors like "msg 7357 - since the object has no columns..."
... but that's another issue so thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply