Getting Data from a Secured Access Database with VBA

[Guest]

I've set up security on an Access database and want to query it from another
MS Office application using VBA scripting. I can easily do this on an
unsecured database.

How do I do this?

Thanks,

VBA Dabbler

 

[TC]

Try something like:

(untested)

dim e as object, db as database
set e = createobject ("DAO.DBEngine.36")
e.xxx = "full path to your workgroup file"
e.yyy = "Fred" ' username.
e.zzz = "s3cr3t" ' password.
set db = e.opendatabase ("full path to your database")

I forget the property names xxx, yyy and zzz above. I think that they
are systemdb, username, password, respectively - but you'll have to
check.

Replace ".36" according to your DAO version.

HTH,
TC

 

[Guest]

Thanks for your response. I tried your suggestions, but unsuccessfully.

I set:
xxx=IniPath, and SystemDB (each separately)
yyy=DefaultUser
zzz=DefaultPassword

I got the following error message:

'Run-time error '3028': Cannot start your application. The workgroup
information file is missing or opened exclusively by another user.

It appears that it's not getting to the workgroup information file.

Any other suggestions?

Thanks,
VBA Dabbler

 

[TC]

Error 3028 can sometimes occur when you use a method of the DBEngine
object without providing the "DBEngine." prefix explicitly.

Don't set IniPath. You need only set SystemDB.

Perhaps try setting SystemDB to a file that you know does not exist, &
confirm you get a different (appropriate) error.

If none of that works, show me the actual code you are using

HTH,
TC

 

[Guest]

I changed the name of the workgroup file (to non-existent one) and got the
same error.

Here's my code:

Sub GetDataFromSecuredDatabase()
Dim dbs As Database
Dim CurrentRS As Recordset
Dim DatabasePath As String
Dim DatabaseName As String
Dim DatabasePathName As String
Dim DBWorkgroupFilePath As String
Dim DBWorkgroupFileName As String
Dim DBWorkgroupPathName As String
Dim DatabaseUserID As String
Dim DatabasePassword As String
Dim SQL_Text As String
Dim NumberofRecords As Integer
Dim DaoDBEngine As Object
Dim i As Integer
Dim j As Integer

'Connect to secured database
DatabasePath = "\\fileserver\MyDatabaseLocation"
DatabaseName = "MyDatabaseFile.mdb"
DatabasePathName = DatabasePath & "\" & DatabaseName
DBWorkgroupFilePath = "\\fileserver\MyDatabaseLocation\Workgroup Files"
DBWorkgroupFileName = "MyWorkgroupFile.mdw"
DBWorkgroupPathName = DBWorkgroupFilePath & "\" & DBWorkgroupFileName
DatabaseUserID = "Scoobie"
DatabasePassword = "snacks"

Set DaoDBEngine = CreateObject("DAO.DBEngine.36")
DaoDBEngine.SystemDB = DBWorkgroupPathName
DaoDBEngine.DefaultUser = DatabaseUserID
DaoDBEngine.DefaultPassword = DatabasePassword

Set dbs = DaoDBEngine.OpenDatabase(DatabasePathName)

Any suggestions?

Regards,
VBA Dabbler

 

[TC]

The code looks aok to me (at least in the relevant parts).

Here's what I personally would do next.

(1) Debug.print the content of DBWorkgroupPathName immediately before
the Set DaoDBEngine line, to ensure that it contains what you expect.
Be sure to check for leading or triling spaces that might have snuck in
somehow. For example, debug.print ">"; DBWorkgroupPathName; "<"

(2) If the name is correct but it still doesn't work, copy that
workgroup file to the local PC & amend the pathname accordingly, & see
if that works. (I'm not suggesting this as a permanent fix. It's just
to see whether the problem is something to do with the workgroup file
being on a different PC.)

(3) If it still doesn't work, open Access "manually" against that
workgroup file, & ensure you can indeed log on using the
*case-dependent* username/password that you are using in your code. If
you can't - there's your problem. If you *can*, debug.print the current
workgroup file name (I think it's a SysCmd value), to ensure that you
are indeed logged in to the file you expect.

I'm confident that with some suitable investigation (like the
suggestions above), you will get it working properly.

HTH,
TC

 

[Guest]

TC,
You're right - the code was correct. What wasn't correct was the path to
the workgroup file - it was off by one character. I passed the 'FileString'
as an argument to a function I developed to test for file existence using the
FileSystemObject - the referenced file wasn't there. After correcting the
path it worked like a charm.

Thanks for your help.

Regards,
VBA Dabbler

 

[TC]

No probs, well done. I thought from the organized look of your code,
that you would get it working.

TC