Tearing my hair out - Security for end/back end database

[Keith Nichols]

Everyone will have access to the front end on the shared network. This is not a database that will be in heavy use.

Hi Groovyjoker,

I am pretty green at Access (as you seem to be), so this question may be simple.

Why have you split the database into a front end/back end if there is only one copy of each and they sit in the same directory on a network? I'm assuming that the database is set so that it can be opened and edited by multiple users simultaneously.

It seems to me that if you aren't distributing the front end, you might as well keep it (your database) all together and save yourself half the greif. Wiser heads - any comments?

Like yourself, I feel there is something, probably simple, that I am missing about command line switches. I seem to be doing everything right but they just will not join Access to the desired WIF. As previously noted, the addressing seems ok and I know the wifs are fine as they work when I manually connect.

what am I missing?

Could some flaw in my security set up prevent the command line switch joining Access to the wif yet allow a manual connection to be made?

Regards,

Keith.

 

[groovyjoker]

Why have you split the database into a front end/back end if there is only one copy of each and they sit in the same directory on a network? I'm assuming that the database is set so that it can be opened and edited by multiple users simultaneously.

They are not on the same directory. Our districts access the network through "R" drive, so their front end is located their. We access through J, so our backend, and frontend is located their.

BTW, a programmer who used to work here set up a database this way. Runs great. Darned if I can mimic it though.

Like yourself, I feel there is something, probably simple, that I am missing about command line switches. I seem to be doing everything right but they just will not join Access to the desired WIF. As previously noted, the addressing seems ok and I know the wifs are fine as they work when I manually connect.

Do you get an error message? When I have someone from the district (R-drive) connect, the following error appears to them:

"(databasename).be.mdb is not a valid path. Make sure the pathname is spelled correctly and you are connected to the server on which the file resides."

I have refreshed the links, and mapped the network drive again....not working.

 

[Keith Nichols]

Hi Joker,

They are not on the same directory. Our districts access the network through "R" drive, so their front end is located their. We access through J, so our backend,

So, you do actually distribute 2 copies of the front end - 1 on the J and one on the R. I might still be missing something here. All users access the same back end on a shared network?

I think you could still run it without a split as long as you didn't give anyone exclusive access. I don't know if there is any merit in this but it looks possible.

Do you get an error message? When I have someone from the district (R-drive) connect, the following error appears to them:

"(databasename).be.mdb is not a valid path. Make sure the pathname is spelled correctly and you are connected to the server on which the file resides."

I have refreshed the links, and mapped the network drive again....not working.

At various points I have had many error messages as I grappled with this issue. However, the fina position was that my shortcuts worked in opening the desired database in whatever location it was but Access remained joined to whatever WIF it was before the shortcut was run. For completeness, I should add that these shortcuts were run when no instance of Access was open on the PC in question.

I have had the error message quoted but only when we had network problems or I had indeed renamed/moved my back end db. I haven't had this error associated with my shortcut issues.


Still looking for an answer on this one.

Regards,

Keith.

 

[MartijnR]

Where did I mention locking the mdw file?

What is the advantage of each user having his/her own mdw file?

About the shortcut, have you looked at the 'start in' value?

 

[Keith Nichols]

About the shortcut, have you looked at the 'start in' value?

I played around with this, tried varoius permutations but none of them had any effect on my shortcut/mdw issue.

Regards,

Keith.

 

[ghudson]

Not trying to beat this dead horse any more but can you list the "exact" path [full path and file name] to where the front end and the back end of your database files are located.

Then list the entire "Target" string you are using in your shortcut to open the front end.

 

[MartijnR]

and any error msg you may get, when you doubleclick the shortcut you've posted plz

 

[Keith Nichols]

Thanks Guys.

Many thanks for taking the time on this. It has obviously gone round in circles a few times and must be pretty boring for all concerned.

This morning I created a new test database and WIF and then made shortcuts with command line switches to change workgroup information file. If I can get this working as intended, then adapting it to the FE/BE split should not present problems.

As before, the shortcuts have no problem running Access and the target database, but the command line switch for the WIF has no effect. It stays connected to whatever WIF it was connected to the last time it ran.

and any error msg you may get, when you doubleclick the shortcut you've posted plz

I get no error messages when the shortcuts are used.

My only thought is: Is there some obscure setting somewhere that can be used to disable this command line switch? I can't think of any other reason it won't work.

Not trying to beat this dead horse any more but can you list the "exact" path [full path and file name] to where the front end and the back end of your database files are located.

Then list the entire "Target" string you are using in your shortcut to open the front end.

Location of Access:

C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE​

Target Database & WIF

C:\EDT DB\Test_DB_For_Shortcut.mdb
C\EDT DB\Test_WIF.mdw​

Default WIF

C:\Program Files\Common Files\System\SYSTEM.MDW​

Shortcut to Test_DB & Test_WIF

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\EDT DB\Test_DB_For_Shortcut.mdb" /wrkgrp "C:\EDT DB\Test_WIF.mdw"
Start in:
"C:\Program Files\Microsoft Office\OFFICE11"​

Shortcut to Access & default WIF

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp "C:\Program Files\Common Files\System\System.mdw"
Start in:
"C:\Program Files\Microsoft Office\OFFICE11"​

System info:

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 1 Build 2600​

Access version:

2003 (11.6355.6408) SP1, part of Office Professional Edition 2003​

Regards,

Keith

 

[ghudson]

Well your test shortcut looks fine. You do not need anything in the startin field for it to work. Your db should not open using the default WIF if it is properly secured.

I noticed that you have not upgraded your Windows XP and your Office 2003 to SP2. Maybe you should! At least the Office SP2 update.

The order of the strings in the target field should not matter but I always use this order...

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /wrkgrp "\\Server\Partition\Directory\Workgroupfile.mdw" "\\Server\Partition\Directory\DatabaseFile.mdb" /user UserName

 

[MartijnR]

By getting no errors, you mean you can enter the database when you doubleclick the shortcut? If so, something is wrong in the security section.

I'm not 2nd guessing ghudson here, but just to be thorough, have you tried it with

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\EDT DB\Test_DB_For_Shortcut.mdb" /wrkgrp "C:\EDT DB\Test_WIF.mdw"
Start in:
"C:\EDT DB"

What other processes are running on your computer?

 

[Keith Nichols]

Your db should not open using the default WIF if it is properly secured.
]

By getting no errors, you mean you can enter the database when you doubleclick the shortcut? If so, something is wrong in the security section.

Maybe this is where I have the problem?!? I can check this out, but it looks like it works. If I manually join the target wif and set a password for Admin all users need a password to get in. Are there any other tests to check security is fully functional?

When I created the test DB I didn't go through all the hoops, i.e. creating a blank database and transferring tables etc, mainly because the db is empty. Could this make a difference?

BTW, on another thread in the same forum, Ghudson wrote something like "It is important not to join the WIF" to someone having simnilar problems (the user wanted to use a FE/BE split database on a stand alone laptop).

Could you briefly explan how you can use a WIF without joining? Would this remove the need to use a dedicated shortcut to un-join you from the wif and return you to the default?

I noticed that you have not upgraded your Windows XP and your Office 2003 to SP2. Maybe you should! At least the Office SP2 update.

Corporate system. If this is the root of my problems, all users wuld need to have SP2 installed by IT.

I'm not 2nd guessing ghudson here, but just to be thorough, have you tried it with

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\EDT DB\Test_DB_For_Shortcut.mdb" /wrkgrp "C:\EDT DB\Test_WIF.mdw"
Start in:
"C:\EDT DB"

I’ll give it a go.

What other processes are running on your computer?

Lotus notes is the only programme that is always running.

 

[john471]

Hello again,

Looks like you've got plenty of people offering help, so pardon me for jumping in (again), but I'm just wondering what makes you think the shortcut /wrkgrp switch is not "working".

You have said on more than one post that you remain "joined" to the default WIF.

The /wrkgrp switch does not join you to that workgroup (and that is part of its beauty), it merely instructs Access to temporarily use a workgroup, in lieue of the one to which you are (and remain) joined, for only this one session/instance (or any time you use a shortcut with the same switch and setting).

Try this to see what I mean.
1) Close ALL instances of Access on your PC
2) Run up access using your "/wrkgrp switched" shortcut
3) Create a new user with a distinct name. Let's go with "HideNseek".
4) Close Access
5) Run up an ordinary (default) session of Access. This will use the WIF to which you are joined (C:\Program Files\Common Files\System\SYSTEM.MDW)
6) In Security / User & Group Accounts - note the absence of our "HideNseek" user
7) Close Access
8) Repeat step "2"
9) In Security / User & Group Accounts - note the presence of our HideNseek user. Delete the test user - the point is proved.

Does that shed any light for you ?

HTH

Regards

John

 

[ghudson]

You can verify which workgroup your "computer" is joined to. In Access 2003 you follow the menu options...

Tools / Security / Workgroup Administrator

Your computer should be joined to the default System.mdw file.

As mentioned above, the shortcut only tells your computer which workgroup to use when opening a db with a custom target string.

 

[MartijnR]

When I read the following
------
Maybe this is where I have the problem?!? I can check this out, but it looks like it works. If I manually join the target wif and set a password for Admin all users need a password to get in. Are there any other tests to check security is fully functional?
------- (sorry for the messy quote)

I'm getting the impression that you are using the wrong mdw file here. Check out John471 's post.

A bit offtopic, I'm certainly not an expert, but I am glad we are all giving the same answers (yay for me )

 

[Keith Nichols]

"Problem" solved

I'm just wondering what makes you think the shortcut /wrkgrp switch is not "working".

The /wrkgrp switch does not join you to that workgroup (and that is part of its beauty), it merely instructs Access to temporarily use a workgroup, in lieue of the one to which you are (and remain) joined, for only this one session/instance (or any time you use a shortcut with the same switch and setting).

Hallelujah!

The subtle thing I had been missing was the difference between "using" and "joining" a WIF. I'm sure somewhere down the line I followed instructions to join & unjoin a WIF via shortcuts. Not to worry - that was the cause of my "problem" - they shortcut was working perfectly, I was expecting it to do something else.

My heartfelt thanks to all you good folk who attempted to unpick this for me.

If any of you have connections at Redmond, maybe you could suggest that the Tools>Security>Workgroup Administrator pop up message box adds a line to tell you which WIF you are using as well as which WIF you are joined to? This was certainly where I was getting led astray.

Kindest regards and once again, thanks,

Keith.

 

[ghudson]

Glad you figured it out.

I am confused by your previous statement because the Workgroup Administrator function does tell you which workgroup file your computer is joined to.

Tools / Security / Workgroup Administrator

As I mentioned above, your computer should be joined to the default System.mdw file.

 

[Keith Nichols]

I am confused by your previous statement because the Workgroup Administrator function does tell you which workgroup file your computer is joined to.

Tools / Security / Workgroup Administrator

As I mentioned above, your computer should be joined to the default System.mdw file.

Hi Ghudson et all,

If I have this right (and all who have followed this thread will realise that this is a big if ), by dint of command line switches, Access can use the target mdw for that session of Access yet stay joined to the default mdw.

Thus when you start a new session using the regular shortcut to Access, you aren't using a WIF that isn't relevant to the current database.

Once you are in a session using a shortcut that targets a WIF, the WorkGroup administrator tells you which WIF Access is joined to, but not which WIF was used when it launched. If this information had been displayed, I would have got to the bottom of this much sooner.

Maybe it is information an experienced datbaser wouldn't need, but rank amateurs, such as myself, get mired in the convoluted and confusingly termed security that Access employs. I can imagine scenarios where a developer works on multiple database and would sometimes want to know which WIF was used for the session rather than the WIF that Access is joined to.

All the above assumes that my understanding is right!

Once again, thanks to all.

Regards,

Keith.

 

[ghudson]

Check this article out...

Understanding the role of workgroup information files in Access security

 

[ghudson]

Now that I better understand what you are looking for... This function will return the full path and name of the workgroup file that the "current" database is using [not the workgroup file that the computer is "joined" to].

Public Function WorkGroupFile() As String

    WorkGroupFile = SysCmd(acSysCmdGetWorkgroupFile)
    MsgBox WorkGroupFile

End Function

Check the help files for the SysCmd for it can provide you with a lot of good info about your db.

 

[Keith Nichols]

Now that I better understand what you are looking for... This function will return the full path and name of the workgroup file that the "current" database is using [not the workgroup file that the computer is "joined" to].

Thanks Ghudson,

I will play around with the other info you gave and review the article you linked.

I still feel that the information should be in plain sight; well presented and timely information oils the wheels in any endeavour and would be especialy useful here.

Regards,

Keith.