PrivDBEngine and Import Access data

Posted on 2004-11-16
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
    LVL 26

    Assisted Solution

    by:Alan Warren
    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

    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

    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
    LVL 58

    Expert Comment

    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

    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

    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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    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, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now