Openin Access 2000 Database on Network Drive with OLE Automation
-
I have not been able to figure out how to open an Access 2000 database on a network drive using OLE Automation from a SQL Server 2000 stored procedure. I am the network and SQL Server administrator so I have complete access to everything on our network. Additionality, none of Access databases have ANY password protection or other security.
If I run a stored procedure from our SQL Server, I can open ANY access database that resides on any of the local drives. However, when I try to open an access database located on ANY network drive (either by using a "mapped" path or UNC path), I receive an error and the database does not open. It certainly looks like a "permissions" issue of some sort -- but I don't see how it can. If anyone has an answer or suggestion(s), I woujld greatly appreciate it. Thank you.
Denis Repke
==== The stored procedure code I'm using follows: ====
DECLARE @cnnAccess int
DECLARE @ConnectionString varchar(500)
DECLARE @hr int
DECLARE @return int
DECLARE @property varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
--This code works if I use the following SET statement:
SET @ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\MyMDB.mdb;'
-- BUT it does NOT work if I use either of these statements:
--SET @ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=N:\Shared\MyMDB.mdb;'
-- OR
--SET @ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=\\VHANC3FPC1\Common\Shared\MyMDB.mdb;'
--Create objects.
EXEC @hr = sp_OACreate 'ADODB.Connection', @cnnAccess OUT
PRINT '@cnnAccess = ' + CAST(@cnnAccess AS varchar(50))
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @cnnAccess, @src OUT, @desc OUT
SET @Log = 'ADODB.Connection not created.' + @CR +
'Source: ' + @src + @CR +
'Description: ' + @desc
PRINT @Log
RETURN
END
EXEC @hr = sp_OAMethod @cnnAccess, 'Open', NULL, @ConnectionString
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @cnnAccess, @src OUT, @desc OUT
SET @Log = 'Database could not be opened.' + @CR +
'Source: ' + @src + @CR +
'Description: ' + @desc
PRINT @Log
RETURN
END
Denis W. Repke -
can you open the access database in anything else other than sql server from the sql server machine? (e.g. from office, if its installed
)I know you can have issues openening access databases held on remote/networked drive with office due to friendly M$ security (?being run from a trusted site?) - i dont know if its along those lines...
I was going put an example up here, but wouldnt you know it, for the first time ever when I try to recreate that problem here, which ive had before, it worked instead!
doh
martin
-
Yes, I CAN open the Access database that was created on the SQL Server from other computers. The database is OK. It's got to be some sort of security issue which, so far, I have not been able to figure out.
Denis
Denis W. Repke -
Check the sql services account. If it's a machine account rather than a network account, that could be it. And stick with UNC names when you're doing this. If you are logged on to the machine when you do it, your permissions as a net admin are giving you permission to everything, if the service account is local and tries to do something, it can't get out of the server.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply