Hi ninjasix8,
I had the same problem and resolved it yesterday as follows. Hope it works for you. Jose from Uruguay.
Problem: Linking or importing files resident in user-level protected databases.
Example:
Database A:
Path-Name: C:\ProjectA\ApplicationA.mdb
User: Boy
Password: ball
Security file: C:\ProjectSecurity\Sec_AppA.mdw
Shortcut: "C:\Program Files\MsAccess.exe " "C:\ProjectA\ApplicationA.mdw" /WRKGRP
"C:\ProjectSecurity\Sec_AppA.mdw"
Database B (assumed as protected, but may be unprotected or only protected at general level):
Path-Name: C:\ProjectB\ApplicationB.mdb
User: Girl
Password: doll
Security file: C:\ProjectSecurity\Sec_AppB.mdw
Shortcut: "C:\Program Files\MsAccess.exe " "C:\ProjectB\ApplicationB.mdw" /WRKGRP
"C:\ProjectSecurity\Sec_AppB.mdw"
Goal: Link or import Table_X and Table_Y from ApplicationA.mdb to ApplicationB.mdb
1) Open ApplicationA and create user Girl with, at least, design permissions.
2) Insert in main module of ApplicationB.mdb:
Dim returnVar as Variant, strT as String, strS as String, strU as String
Public strB as String
strT = "Table_X"
strS = "C:\ProjectA\ApplicationA.mdb"
strU = "Boy" 'see footnote 1
strB = "Girl" 'see footnote 1
returnVar = fetchTable(strT, strS, strU) 'shorter than fetchTable("Table_X",_
' "C:\ProjectA\ApplicationA.mdb","Boy")
strT = "Table_Y"
returnVar = fetchTable(strT, strS, strU)
3) Create function fetchTable:
Function fetchTable(strTable As String, strSource As String, strUser As String)
Dim db As Database
Dim con As Container
Dim doc As Document
Dim wrksp As Workspace
Set wrksp = DBEngine.Workspaces(0)
Set db = CurrentDb()
'------------'next six lines only needed if Girl
'has not full permissions in ApplicationB
'Set con = db.Containers("Tables")
'con.UserName = strB
'con.Permissions = dbSecFullAccess
'Set doc = con.Documents(strTable)
'doc.UserName = strB
'doc.Permissions = dbSecFullAccess
'------------'next three lines only for imported tables
'and if suitable to your particular purposes
On Error Resume Next
db.TableDefs.Delete strTable
On Error GoTo 0
'----------- 'only if Workgroups for front and back-tables are
'different (our case)
Dim strPWD As String
Dim strSecFile As String
Dim dbe As PrivDBEngine
strPwd = "ball" 'correlated with strU/strUser; see footnote 1
strSecFile = "C:\ProjectSecurity\Sec_AppA.mdw"
Set dbe = New PrivDBEngine
dbe.SystemDB = strSecFile
dbe.DefaultUser = strUser
dbe.DefaultPassword = strPWD
Set wrksp = dbe.Workspaces(0)
Set db = wrksp.OpenDatabase(strSource) 'does not truly open database
'but makes it available
'-----------'next line only if workgroup is the same (not our case)
'Set db = wrksp.OpenDatabase(strSource)
Set con = db.Containers("Databases")
Set doc = con.Documents("MSysdb")
doc.UserName = "Users"
doc.Permissions = dbSecDBOpen 'do not use this line if using the same
' user; see footnote 1
Set con = db.Containers("Tables")
Set doc = con.Documents(strTable)
doc.UserName = strUser
doc.Permissions = dbSecReadDef 'do not use this line if using the same
'user; see footnote 1
'-----------'next line: choose if you want to link or import
DoCmd.TransferDatabase [acLink]/[acImport], "Microsoft Access", strSource,_
acTable, strTable, strTable
Set db = CurrentDb()
End Function
Footnotes:
(1) You may opt for passing "Boy" as parameter (with full access to
ApplicationA) or "Girl" (with partial permissions). Both will work. This can be
important if you are reluctant to write down one of the user-password couples
onto code. But, if you choose to pass ApplicationB's user ("Girl") then notice:
strU = "Girl" ; strB = strU ; strPWD = "doll" and you must cancel
'doc.Permissions' lines
(2) If ApplicationB is not protected at user level, you may save some
unnecesary instructions.
Of course, the only available parameters to pass are "Boy" and "ball".
(3) This solution was designed after testing and adjusting examples provided by
reading Sections 13, 14 & 46 of an article from Mary Chipman et all. that you
may found at:
http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2F secfaq.asp
4
