RonOsborne
asked on
PrivDBEngine and Import Access data
Hi
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("Che
' 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
dbs.close
Set dbs = Nothing
wrk.close
Set wrk = Nothing
Set dbe = Nothing
End Sub
Thanks in advance
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("Che
' 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
dbs.close
Set dbs = Nothing
wrk.close
Set wrk = Nothing
Set dbe = Nothing
End Sub
Thanks in advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("Che
Set db = wrk.OpenDatabase(strImport
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
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("Che
Set db = wrk.OpenDatabase(strImport
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
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.
Cheers!
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.
Cheers!
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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!
Anyway, good luck!
ASKER
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.
Thanks for your help.
ASKER
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. :)