Linking To A Table In Secured Access Database (.mdw)

[mordja]

Hi,

I need to link to a table in an access database that I have secured with a workgroup. I open the database with a shortcut using the /WRKGRP.

When I try and link to a table inside it I naturally get a "You do not have the necessary permissions" error.

How do you get around this ? can you link to a databse which is secured with a .mdw file ?

Thanks

Mordja

 

[JerryKlmns]

mordja,

I use MS-Access2000 with .mdw security. I use the wrkgadm.exe file that ships with Access and join that .mdw file manually. I have linked tables on other user-level secured Access and everything goes fine.

I' m wondering though if that manual join to that .mdw could be achieved through code (VBScrpt maybe???).

Just to mention that newer versions of Access have this wrkgadm.exe stuff, as a built-in functionality at the menu Tools->Database Utilities ... I think.

 

[mordja]

JerryKlmns,

I was hoping to not have to join my unsecure db (the one Im trying to link from)
to the same workgroup that my secure db is on, as that means I have to enter username/password from unsecure db.
I was hoping to be able to do this linking with vba. Not much luck so far. Ive got this code in my unsecure db, an attempt to bypass the workgroup, but it only works when the unsecure db belongs to the secure db's workgroup !!

DBEngine.SystemDB = "Securedbs.mdw"
DBEngine.DefaultUser = "user"
DBEngine.DefaultPassword = "pwd"

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("Securedbs.mdb")

Im a little lost.

Thanks

Mordja

 

[JerryKlmns]

Haven'ttried somthing like that but I use this code running at Master_Dbase to link a table:MyTable from App_Dbase to BUp_Dbase

    Set BUp_Cnn = New ADODB.Connection
    With BUp_Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = Data_Folder 
                                       & BUp_Dbase
        .Properties("Jet OLEDB:System database") = 
                           Data_Folder & Sys_Dbase
        .Properties("User ID") = PowerUser
        .Properties("Password") = PowerPassword
        .Properties("Persist Security Info") = False
        .Properties("Mode") = adModeShareExclusive
        .Properties("Jet OLEDB:Engine Type") = 5
        .Properties("Locale Identifier") = 1033
        .Open
        .Properties("Jet OLEDB:Connection Control") = 1
    End With
    
    Set cat.ActiveConnection = BUp_Cnn
    Set tbl.ParentCatalog = cat
    
    With tbl
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Datasource") = 
                           Data_Folder & App_Dbase
        .Properties("Jet OLEDB:Remote Table Name") = 
                                             MyTable
        .Name = "N_" & MyTable
    End With
    cat.Tables.Append tbl
    cat.Tables.Refresh
    Set cat = Nothing

But you have to reference your Microsoft ADO Ext. 2.? for DDL and Security

Master_Dbase is also secured, so this is not tested on an unsecured one.


Check also this

 

[mordja]

JerryKlmns,

I used your code from an external database, and had some mixed results. You mention App_Dbase to BUp_Dbase but what is Sys_Dbase relative to these other two. I wasnt sure. Anyhow I sucessfully linked the table but when I try an open it I get "You do not have the necessary permissions" error.

I opened the unsecure db with the secure dbs workgroup and the linked table opened and had linked correctly, but unfortunately this brings me back to square one, how do you link to a table in a workgroup secured db without having to have the database you are linking from also belong to that workgroup ??

Thanks a lot for your help.

mordja

 

[JerryKlmns]

MORDJA,

Sys_Dbase is the system database (.mdw file).

But with a little search on MSDN found this

tbl.Properties("Jet OLEDB:Link Provider String") = _
      tbl.Properties("Jet OLEDB:Link Provider String") & ";UID=Demo;PWD=Demo;"

It says "Append authentication parameters to the end of the existing connection string". I guess it 's worth trying.
Let me know on results.

 

[mordja]

JerryKlmns,

Still no luck, with what you gave me, so far Ive only managed to create the linked table in the unsecured db but cannot access it as it is not on the same workgroup. Ive been trying to read about but all I could gather was that you needed to log in to the secure workspace before creating the linked table.

Dim dbe As PrivDBEngine
    Dim wrk As Workspace
    Dim dbs As Database
    Dim tdf As TableDef
    
    ' Return a reference to a new instance of the PrivDBEngine object.
    Set dbe = New PrivDBEngine
    
    ' Set the SystemDB property to specify the workgroup file.
    dbe.SystemDB = "workgroup.mdw"
    dbe.DefaultUser = "user"
    dbe.DefaultPassword = "pword"
    Set wrk = dbe.Workspaces(0)
    
    ' Open the secured database.
    Set dbs = wrk.OpenDatabase("securedb.mdb")

That provides me access, I can query the secure db, but do not know how to create a linked table from the current db to that.

Ill keep trying.

Thanks

Mordja