PrivDBEngine and Import Access data

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
Hi Ron,

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

then do some update/append sql locally.

RonOsborneAuthor Commented:
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. :)

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

RonOsborneAuthor Commented:
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.
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 :)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RonOsborneAuthor Commented:
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.
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!
RonOsborneAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.