MS Access 2007 & .vbs script -> opened read-only

[Philippe Lang]

Hi,

With Office 2003, we can open a MS Access database through a .vbs script,
and call a public sub inside, like this:

Set objAcc = WScript.CreateObject("Access.Application")
objAcc.OpenCurrentDatabase "C:/trusted/reporting.mdb"
objAcc.run "<the_public_sub>", "<the_parameter>"
objAcc.Visible = true

With Office 2007, this does not work anymore. Reason: Error 3027, database
opened read-only. (not the exact error message)

I'm not sure this is a security matter or a bug. Does anyone know how to
revert the pre-office 2007 behaviour regarding external scripts?

Thanks,

Philippe Lang

 

[Philippe Lang]

Hi,

With Office 2003, we can open a MS Access database through a .vbs script,
and call a public sub inside, like this:

Set objAcc = WScript.CreateObject("Access.Application")
objAcc.OpenCurrentDatabase "C:/trusted/reporting.mdb"
objAcc.run "<the_public_sub>", "<the_parameter>"
objAcc.Visible = true

With Office 2007, this does not work anymore. Reason: Error 3027, database
opened read-only. (not the exact error message)

Hi,

I forgot to mention something: Error 3027 appears only when trying to modify
the database in the sub, of course. The minimal code that shows the bug is:


1. VBS file
-----------
Set objAcc = WScript.CreateObject("Access.Application")
objAcc.OpenCurrentDatabase "C:/Users/Philippe
Lang/trusted/reporting07.accdb"
objAcc.run "test_querydef"
objAcc.Visible = true


2. MS Access 2007 public sub
-------------------------------
Public Sub test_querydef()
On Error GoTo test_querydefError

Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim query As String

query = "test_querydef"

Set MyDatabase = CurrentDb()
Set MyQueryDef = MyDatabase.CreateQueryDef(query)

MyDatabase.Close
Set MyDatabase = Nothing

test_querydefExit:
Exit Sub

test_querydefError:
MsgBox "Error in test_querydef: " & Err.Number & " " & Err.Description
Resume test_querydefExit
End Sub



The problem is the call to CreateQueryDef, because the database is
read-only.

The bug appears with either a native Access 2007 database, or an MDB in
compatibility mode.

Best regards!

Philippe Lang

 

[Arvin Meyer [MVP]]

Try putting the database in a Trusted Location:

Office Button >>> Access Options >>> Trust Center

 

[david]

Why are you running a script? In particular, are you testing
this as a logged in user, with r/w/c/d permissions to the
/trusted/ location?

(david)

 

[Philippe Lang]

Try putting the database in a Trusted Location:

Office Button >>> Access Options >>> Trust Center

Hi,

I suspected this could be a potential problem, and I tried that yesterday.
No effect. Even if the database (and the script by the way) are in a trusted
location, the script is only able to open the database in read-only mode,
and thus all attemps to call CreateQueryDef afterwards fail...

This is really strange, and this is linked to Office 2007, not the OS. It
happens under Windows XP and Windows 7 too. Until Office 2003, no problem at
all.

Philippe Lang

 

[Philippe Lang]

Why are you running a script? In particular, are you testing
this as a logged in user, with r/w/c/d permissions to the
/trusted/ location?

Hi David,

I'm running such a script (but not the same one by the way) from a ruby
program, which uses MS Access as a reporting tool. The ruby program modifies
the parameter of a pass-through query inside MS Access before opening a
report that uses this query as a datasource. This was until now how we could
display a report for document id = 1, 2, 3... inside our erp. For an unknown
reason, this stopped working when upgrading to Office 2007, and I have to
admit this is really ambarrasing, since I couldn't find any workaround until
now.

I'm not testing anything particular regarding permissions, or user, or
whatever in this field, since if I double-click on the database by hand, it
opens read-write. If I double-click on the script (which opens the database
afterwards), it opens read-only. It looks like a security feature of Office
2007 to me, or maybe a bug.

I wish there was some registry key called "OpenReadWriteForExternalScripts"!
There is such a key, but for a different purpose:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Internet\OpenDocumentsReadWriteWhileBrowsing
= 1

http://support.microsoft.com/kb/870853

 

[Tom van Stiphout]

On Mon, 3 May 2010 10:48:17 +0200, "Philippe Lang"

This is an illegal path: "C:/trusted/reporting.mdb"
Should be: "C:\trusted\reporting.mdb"

While the database is open readonly, do you have an .ldb file in the
same folder?

Also note that when you open interactively, you do so as the logged-in
user, while the script is likely invoked by a web server so runs under
its security account. Trusted locations are per-user.

-Tom.
Microsoft Access MVP

 

[Philippe Lang]

On Mon, 3 May 2010 10:48:17 +0200, "Philippe Lang"

This is an illegal path: "C:/trusted/reporting.mdb"
Should be: "C:\trusted\reporting.mdb"

While the database is open readonly, do you have an .ldb file in the
same folder?

Hi,

Believe me if you want: when using slashes in the path (you mention it is
illegal), the database is opened read-only, and yes, there is an ldb /
laccdb file that appears. When using backslashes, all problems disappear,
database is opened read-write. Is that a documented feature, or did that
appear with Office 2007 by accident?

Thanks a million times for you help, and best regards,

Philippe Lang

 

[david]

Oh, that.

Files on the local pc, c:\myfolders\myfolders\whatever,
are local, and are trusted if you want to trust yourself.

Files on the internet, //www.google.com/whatever are not local, and are not
trusted.

Yes, it is a very simple test.

But you can add c:/trusted/ to your local or trusted sites, in IE if not in
Office.

(It's not just Access, and it's not new.)

(david)

 

[Philippe Lang]

Oh, that.

Files on the local pc, c:\myfolders\myfolders\whatever,
are local, and are trusted if you want to trust yourself.

Files on the internet, //www.google.com/whatever are not local, and are
not trusted.

Yes, it is a very simple test.

But you can add c:/trusted/ to your local or trusted sites, in IE if not
in Office.

(It's not just Access, and it's not new.)

Thanks for your explanation, I didn't know that. But one small precision:
this "read-only problem" appeared with Office 2007. With Office 2003, same
computer, same OS, you can open a database read-write, event with slashes in
the path.

Best regards,

Philippe

 

[Tom van Stiphout]

On Wed, 5 May 2010 12:53:19 +0200, "Philippe Lang"

What I think David tried to say is that Trusted Locations are new for
A2007 so there was no equivalent feature in A2003. You added the
c:\trusted folder to your trusted locations, which did not help when
you accessed c:/trusted folder. If you had added c:/trusted folder as
well, the outcome may have been different. But I would stay with
backslashes since that has been the standard since the DOS days, and
your notation may not have had any test coverage from the Access team.

-Tom.
Microsoft Access MVP

 

[david]

Actually, I was thinking internet security zones.

http://support.microsoft.com/kb/303650

And yes, internet security zones do affect Access
and OLE/COM and scripting, but I still haven't got
to my A2007 box to test this.

(david)

 

[david]

I just tried to add "d:/david" to my trusted
locations for 2007, and it tells me that

"The remote or network path you have entered is not allowed by your current
security settings",

So yes, I think it is being treated as a remote or network path.

(david)