[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


PrivDBEngine and Import Access data

Posted on 2004-11-16
Medium Priority
Last Modified: 2008-01-09
I'm trying to import data from one secured DB to the currently open (secured) DB, all msAccess 97 files
Each mdb is opened by it's own mdw file with usernames and passwords
Found the following code to open DB, I think it works, need the transfer method and to then close anything

Sub OpenSecureDbAndImport()

Dim prvDB As PrivDBEngine
Dim db As Database, wrk As Workspace
Dim strImportDb , strImportSystemDb, strUserName, strUserPwd as String
strImportDb = "Path to mdb file" (reference to form)
strImportSystemDb = "Path to mdw file" (reference to form)
strUserName = "User Log On Name" (reference to form)
strUserPwd = "User Log On Password" (reference to form)
Set prvDB = New PrivDBEngine
prvDB.SystemDB = strImportSystemDb
Set db = OpenDatabase(strImportDb)
Set wrk = prvDB.CreateWorkspace("CheckPwd", strUserName, strUserPwd, dbUseJet)

' Need to transfer/open data and create tmptblTargetName tables of data from strImportDb, data may need to be imported as SourceName is a query in strImportDb.
' This example fails because of permissions, DoCmd.TransferDatabase acImport, "Microsoft Access", strImportDb, acQuery, "SourceName", "tmptblTargetName", False

' Here I will place code to run various append and update queries from the data in the tmptblTargetName's
Set dbs = Nothing
Set wrk = Nothing
Set dbe = Nothing
End Sub

Thanks in advance
Question by:RonOsborne
  • 4
  • 4
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 300 total points
ID: 12610781
Hi Ron,

can you manage to establish linked tables to the other catalog?
DoCmd.TransferDatabase acLink ...

then do some update/append sql locally.


Author Comment

ID: 12610872
No, not able to DoCmd.TransferDatabase
DoCmd.TransferDatabase gets a error 3033 "You don't have the neccessary permissions to use the 'ObjectName' object"
I think I read some where that the PrivDBEngine function would only allow for certain functions to be used, but I can't find that info again.
So I'm not even sure if the PrivDBEngine log on is working!
But if I use MsgBox dbs.TableDefs(0).Name it will return the first table name (which is correct) so I assume the PrivDBEngine log on is working to a degree.

But I need to be transfering data, and that I have know idea on how to do.

I'll continue to scratch my head about it. :)

LVL 58

Expert Comment

ID: 12611049
Interesting question (thanks Alan! :)

The problem you get with DoCmd.TransferDatabase is that it's an Application method. It will run in the CurrentDatabase, with the current WIF and current user. I like the trick with PrivDBEngine to use an alternate WIF, but that will not solve the problem...

BTW, you used just OpenDatabase() [defaults to DBEngine.Wordkpaces(0)]. go this way instead:

    Set prvDB = New PrivDBEngine
    prvDB.SystemDB = strImportSystemDb
    Set wrk = prvDB.CreateWorkspace("CheckPwd", strUserName, strUserPwd, dbUseJet)
    Set db = wrk.OpenDatabase(strImportDb)

But now, if you use db.Execute ... you have the rights of the user you specified, while if you use just CurrentDB.Execute, you have those of the current user...

This *will* be a headache. For every method, every collection, you will need to understand the rights associated with it. Again, all Application methods will run in the devault workspace...

I would not like to be in those shoes, I would create a spy!

In both WIFs, create the same user, say "007", with an impossible personal ID, but the same in both. Set a password for the user (does not need to be the same). Then, give that user the licence to kill in both databases. Now, when you to cross the border, use the spy!

Good Luck
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 58

Expert Comment

ID: 12611117
Hey, just got another idea!!!

In database A, using WIF A, you need data from database B, using another WIF. You can probably (this is not tested) open A with WIF B as some power user 'Guru'. Then create a query with the full path and name of database B and OWNERACCESS:
* create a new query, do not select a table
* in query property Source Database, enter the full path of B
* Add the table or tables you need
* Set the property Run Permissions to: Owner's

Test and save...

Reopen A with WIF A, and see if the query works. It should, as it's owner is 'Guru' defined in WIF B. the OWNERACCESS option should thus allow to run that from A... but wait, will the user from A have rights for the query itself???

Hmmm, I really think you need a spy afterall.


Author Comment

ID: 12611574
Comment for Harfang
Thanks for your OWNERACCESS query ideas, I put it together and it works in a test environment, but I'm not sure if I can use it.
I will have to test it in place, with the real WIF, and it will require a major rewrite of how database A functions.

Can you elaborate on the db.Execute runtine more, as would prefer to go that way.

Best regards
Ron O.
LVL 58

Accepted Solution

harfang earned 1200 total points
ID: 12611918
Well, the <database object>.Execute method executes data manipulation queries. It can, for example, transfer data from one table to another or delete a table. To do this, however, it will check for access rights. These are defined by a screening process:
Each object has rights defined for users and/or groups (independant of the WIF)
Each user belongs to certain groups (defined in the current WIF)
The user and all the groups he belongs to are searched in the list of access rights of the object, and cumulated.
The attempted operation will succeed if all needed rights have been found (e.g. modify design or delete data)

Now every database object is part of a workspace (logged in as a given user name), which in turn is part of a DBEngine object (connected to a given WIF). So the database object defines the rights, compared to the user of the workspace and the groups known to the engine...

If you want to transfer data, using .Execute, you will need read rights on the source table and append rights on the target. The only workaround I can think of is to use queries with OWNERACCESS instead of tables to provide extended rights.

Hmm, this is getting confused. Better use concrete examples as soon as you have some :)


Author Comment

ID: 12612008
Thanks for the reply Harfang
By the look of the db.Execute method, it won't work at all, as the permissions will always stop execution.
I thought that I could simply the log on process for users, but it looks like I'll have to stick with my existing data transfer method, it uses 2 seperate shortcuts, 1 for normal use and 1 for when a user needs to access database B.

I'll split the points between yourself (400) and Alan Warren (100)

Thanks for the explainations.

Best regards
Ron O.
LVL 58

Expert Comment

ID: 12612319
As said before, you can use the spy method (same user in both WIF files). This way, the same engine has access to both databases. You do not even need PrivDBEngine, simply  opening another workspace with that user is enough, no?

Anyway, good luck!

Author Comment

ID: 12620266
That is about how it works at present, but works by Group permissions, I was just trying an alternative method, which would have simplified the whole program installation.

Thanks for your help.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question