Hi,
Depending on what your Excel code is doing and what's in your queries,
it's often simplest to cut Access out of the loop entirely and instead
write Excel code that uses the DAO library to open the secured database
and execute the queries. I've pasted sample code at the end of this
message to show the sort of thing that's involved.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;192919
This isn't a reply. It's the start of a related message. Why? Because after
signing on with my username and password, I am unable to start a new thread.
Clicking the NEW button causes a "hickup"-like warning beep, and then doesn't
do anything else. This is an intermittent problem. Sometimes it hickups,
sometimes it works. But I can post a reply to someone else's message, so
here I am.
So what's my issue? I have an Access app which contains queries used by an
Excel spreadsheet to refresh its data on open. It worked fine.....until I
rashly thought to add user-level security to my Access app. After reading
stuff by MS on its user-level security, as well as a couple of books by
Access MVPs, I thought I was ready. Even so, just to remain as plain vanilla
as possible, I did everything through the security wizard. I set up my user
in the pre-defined group that allows data adds, changes and deletes, but
nothing else. I set up myself in the Admins group.
Access is closed now, and security seems to work fine. So I open Excel and
click on the "allow refresh" message (forget the exact text). I get a message
saying I don't have permission to use the queries that do the refresh.
Puzzling. How does Excel know who I am? It didn't provide a logon screen, so
I never told it my logon name, never provided my password.
<snip>
The sample procedure below opens a secured mdb and will work with minor
modifications in VBScript, VB and VBA.
If you actually have to automate Access (e.g. to if the queries contain
custom VBA functions), see e.g.
How To Automate a Secured Access Database Using Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;EN-US;192919
Dim dbE 'As DAO.DBEngine 'Remove the apostrophes
Dim dbW 'As DAO.Workspace 'to change from late to
Dim dbD 'As DAO.Database 'early binding.
Dim rsR 'As DAO.Recordset
Set dbE = CreateObject("DAO.DBEngine.36") 'VBS, late binding
'Set dbE = New DAO.DBEngine 'VB/VBA, early binding
dbE.SystemDB = "C:\temp\xx\secured.mdw"
Set dbW = dbE.CreateWorkspace("Secured", "UserName", "Password",2)
Set dbD = dbW.OpenDatabase("C:\temp\xx\test.mdb")
Set rsR = dbD.OpenRecordset("Query1")
With rsR
MsgBox .Fields(0).Value 'demo only
'Add a record:
' .AddNew
' .Fields(FileName).Value = strResultsFileName
' ...other fields if needed...
' .Update
End With
rsR.Close
dbD.Close
dbW.Close
Set rsR = Nothing
Set dbD = Nothing
Set dbW = Nothing
Set dbE = Nothing
'Here's one that opens one secured database and creates an unsecured
one:
Const SystemDB = "D:\Folder\blah.mdw"
Const SecuredDB = "D:\Folder\Secured.mdb"
Const UnsecuredDB = "D:\Folder\Unsecured.mdb"
Const UserName = "User"
Const Password = "Password"
Dim dbESec 'As DAO.DBEngine
Dim dbWSec 'As DAO.Workspace
Dim dbDSec 'As DAO.Database
Dim dbE 'As DAO.DBEngine
Dim dbDNew 'As DAO.Database
Set dbESec = CreateObject("DAO.DBEngine.36")
dbESec.SystemDB = SystemDB
Set dbWSec = dbESec.CreateWorkspace("Secured", _
UserName, Password, 2) '2 = dbUseJet
Set dbDSec = dbWSec.OpenDatabase(SecuredDB)
Set dbE = CreateObject("DAO.DBEngine.36")
'Add code here to make sure UnsecuredDB doesn't
'already exist
Set dbDNew = dbE.CreateDatabase(UnsecuredDB, _
";LANGID=0x0409;CP=1252;COUNTRY=0")
'above string = dbLangGeneral
'Token manipulation of DBs to prove they're available
Msgbox dbDSec.Name & ": " & dbESec.SystemDB _
& Chr(13) & Chr(10) & dbDNew.Name & ": " & dbE.SystemDB
'Add code here to execute SQL queries
...
'Tidy up
dbDSec.Close
dbWSec.Close
dbDNew.Close
