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?

 

[Pat Hartman]

The following is copied from the help screens. It was so difficult to find, I am including it in total. Hope it helps.

Startup command-line options

The following table lists the Microsoft Access command-line options.

Option Effect
database Opens the specified database. Include a path if necessary. The default path is your My Documents folder.
/excl Opens the specified database for exclusive access. To open the database for shared access in a multiuser environment, omit this option.
/ro Opens the specified database for read-only access.
/user user name Starts Microsoft Access using the specified user name.
/pwd password Starts Microsoft Access using the specified password.
/profile user profile Starts Microsoft Access using the options in the specified user profile instead of the standard Windows Registry settings created when you installed Microsoft Access. This replaces the /ini option used in previous versions of Microsoft Access to specify an initialization file. The Microsoft Office 97, Developer Edition, contains tools and information on creating user profiles. For more information, click .
/compact target database Compacts the database specified before the /compact option and then closes Microsoft Access. If you omit a target database name following the /compact option, the database is compacted to the original database name and folder. To compact to a different name, specify a target database. If you don't include a path in target database, the database is created in your My Documents folder by default.
/repair Repairs the database specified before the /repair option and then closes Microsoft Access.
/convert target database Converts a database in an earlier version (1.x or 2.0) to a Microsoft Access 95 database with a new name and then closes Microsoft Access. Specify the source database before the /convert option.
/x macro Starts Microsoft Access and runs the specified macro. Another way to run a macro when you open a database is to use an AutoExec macro.
/cmd Specifies that what follows on the command line is the value that will be returned by the Command function. This option must be the last option on the command line. You can use a semicolon (

as an alternative to /cmd.For information on the Command function, click .
/nostartup Starts Microsoft Access without displaying the startup dialog box (the second dialog box you see when you start Microsoft Access).
/wrkgrp workgroup
information file Starts Microsoft Access using the specified workgroup information file.
Notes

· To run a Visual Basic for Applications procedure when you open a database, use the RunCode action in a command-line macro or the AutoExec macro. You can also run a Visual Basic procedure when you open a database by creating a form with a Visual Basic procedure defined for its OnOpen event. Designate this as the startup form by right-clicking the database window, clicking Startup, and then entering that form in the Display Form box.
· To specify a forward slash (/) or semicolon (

on the command line, type the character twice. For example, to specify the password ;mjs/md on the command line, type ;;mjs//md following the /pwd command-line option.

 

[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.