Linked Tables and User Level Security

[mikec]

I am trying to implement a classic split database (tables in one database, apps in the other) with user level security in effect. My problem is that when I try to use the OpenDatabase method to use the table in the data database, I get an error telling me that I have no permission for the database.
I can open the data database directly with the same user name that I'm using with the code. I think the problem is that I need to reference the workgroup .mdw file somehow with the OpenDatabase method. Any ideas?

 

[mikec]

Thanks.

My problem is that I need to use the OpenDatabase method to access/manipulate data in the table database. I won't have different users passwords to insert into the command line. My preference is that Access know that I logged into the application database and then use that information to open the table database. Is there a way to do this?

Thanks,

Mike

 

[john471]

Are you not using Linked Tables in your front end to access the back end? If you were, I believe your code could operate on the linked Front End tables as the currently logged in user, and thereby update the Back End data.

If you are not using Linked tables (and one wonders why you wouldn't be)
you could garner some ideas frorm the code below, which I use to open up another instance of the same database, using the same workgroup file but as a different user

    'AC97 / WIN2K
    Dim mwrkSpcSealed As dao.Workspace
    Dim mdbTargetDB As dao.Database
    Dim mrsCommitteeMembership As dao.Recordset

    Set mwrkSpcSealed = DBEngine.CreateWorkspace("SealedTables", "MySuperUser", "MySuperUserPassword", dbUseJet)

    Set mdbTargetDB = mwrkSpcSealed.OpenDatabase(CurrentDb.Name, False, False)
    
    Set mrsCommitteeMembership = mdbTargetDB.OpenRecordset("tblCommitteeMember")

I used this approach to provide row level security, to only allow specific changes through tightly controlled vba code. There are probably better ways to achieve this, but I was young and foolish.... (all-right, perhaps not "young").

So that leaves me to wonder if...

    'AC97 / WIN2K
    Dim dbTargetDB As dao.Database
    Dim rsYourTableName As dao.Recordset
    Dim szDatabasename as string

    szDatabaseName = "G:\Yourpath\YourDBName.mdb"
     
    Set dbTargetDB = DBEngine.Workspaces(0).OpenDatabase(szDatabaseName, False, False)
    
    Set rsYourTableName = dbTargetDB.OpenRecordset("tblYourTableName")
    'your code goes here 
    'etc
    rsYourTableName.close
    dbTargetDB.close
    set rsYourTableName = nothing
    set dbTargetDB = Nothing

might help you out.

Regards

John.