Set mdw file at startup (1 Viewer)

[jalverson]

I have an 2003 access database and the security mdw file located on our network. If a user doesn't have a network connection, then access uses the system mdw on the c drive. I would like to set the database to always use the network mdw file.

I checked the microsoft access website and they had a one line suggestion to create an autoexec macro and add vb code to set the mdw file when the database opens. Then, set the the main menu form to lauch this macro when the form open.

The only problem is they didn't provide the vb code to set the mdw file. Can someone help provide the vb code to use the network mdw file on open? I appreciate your help.

Regards,
Jeff

 

[ghudson]

Use a custom shortcut and set the properties to the mdw on your network.

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "X:\Testing\YourWorkgroupFile.mdw" "X:\Testing\YourDatabase.mdb.mdb" /user TestUser

 

[jalverson]

Thanks for the response. I did the following steps:

1. I modified the code you provided to match my network path and file names.
2. Opened the access database.
3. Clicked on Edit and then Create Shortcut.
4. In the Full Path Field, I deleted what was there and copied and pasted the code that was modified in step 1.

Result:
Using the new short cut, the database opens correctly, but the network mdw file is not used. The database is still using the mdw located on the c drive.

What am I doing wrong? Thanks, again for your help.

Jeff

 

[ghudson]

Edit:

I bet you are using the option to use a custom shortcut option to be displayed when you open your database. This is not what you want to fix your problem. Right click on your database file, select the Create Shortcut option. Then right click on the new shortcut and paste your new "code" into the Target field of your new shortcut.

 

[jalverson]

Thanks for your help. I was able to get the short cut to work correctly. Clicking on the short cut, the database now uses the network mdw file.

However, I have to go back to my original question of how to create code that sets the mdw file when the database is opened? I was reminded that not all uses use short cuts. Some users open Access first, and then select from the list of last used files or they navigate to the actual network location to open the database. Using either of these other methods instead of the short cut would cause the database to open using mdw file on the c drive.

What do you think? Is possible to use vb code to set the location of the mdw file regardless of the method used to open the database?

Thanks,
Jeff

 

[ghudson]

I believe that it is too late to set the mdw from code, that has to be done before you are opening the db. You could check if the user has access to the network and then reopen the database using the correct mdw but that does not solve your original problem.

I have a suspicion that you are using the default system.mdw file to secure your database. If so, you need to rename the required mdw to something meaningful to your database and use the renamed mdw for your secured database. Users can open your database using the defailt system.mdw file because your database is not really secured. But that leaves another problem and you need to restore your mdw back to the default system.mdw that came with your original Access setup. Which would require you to go back and correctly re-secure your database to the new mdw file which will prevent the users from opening your database without the correct mdw file. Ensure you make copies of all files involved for you can easily lock yourself out of your database if you are not careful.

Also, is your databas split with the backend [tables] on the network and each user has a copy of the front end on their computer [linked to the backend]? If not, it should be to prevent other problems.

 

[jalverson]

Here is the background to the issue we are having:

We use an Access database as a front end to create sales proposals. Access has the forms and reports the sales department uses. The back end is a SQL database that holds all of the data.

This past weekend some of the sales staff was in the office. At some point they lost connectivity to the network. The sales proposal software dropped the network mdw file and started using the local mdw file on the c drive. The local system mdw file doesn't have the correct security permissions, so the sales staff couldn't create or change any sales proposals. They also don't understand how to rejoin the network mdw file to correct the problem.

This morning I was told the sales staff that came in over the weekend was a complete waist of time. We need an IT solution to prevent this from happening again. Bascially, the database must alway use the network mdw file. Sorry if this is to much information.

 

[ghudson]

Is the name of your network mdw file "system.mdw"? If so, that is your problem! Access by default will use the local system.mdw file since all Access databases are secured to the system.mdw file. I advise against joining a computer to a network mdw file since all other access mdb files a users creates and uses must work with the network mdw file and you will upset the rest of your users when they cannot open or use any other databases. If I am on the right track then I have already outlined what you need to do.

 

[jalverson]

After much googling, I found a code solution. I would love to give credit to the website and programmer but in my haste to try and test and implement the code, I lost the website. Here is the solution that I have working:

1. Created a module with this function:

Function SetMDWLink()

'The following example sets the default Workgroup file
'Change the path and file name to match your network.

Application.SetDefaultWorkgroupFile _
Path:="T:\UPC\UPC_Workgroup2.mdw"

End Function

2. Created a Macro called AutoExec with the following:

A. RunCode - function name is SetMDWLink ()
B. OpenForm - Form Name is frmMain_Menu
C. Maximize - Maximizes the screen on open

3. For the event properties on the frmMain_Menu, I set the OnLoad to use the macro AutoExec.
4. On the main toolbar, I selected Tools and then Startup. I set the display form to be frmMain_Menu.

Thanks to the help of this forum, I now have a double proof method for setting the mdw file to the network copy. I'm going to revise everyone's desktop icon to include the code posted in this thread. Plus the database has the added code. This should permanently resolve our issue.

Thanks for the help.
Jeff

 

[gemma-the-husky]

you perhaps should still check this

if the T drive is off line, what happens?

if the data is on the T drive, then it may not be an issue

but is there a possibility that the staff could inadvertently be able to open the database with the default mdw file (system.mdw)?


funny how IT get the blame!

 

[ghudson]

I advise against using the SetDefaultWorkgroupFile for what you are trying to do. That function will join the user’s computer to the network copy of your mdw file that you have used to secure your database. Whenever the user tries to open another database that is not secured to your network mdw file, they will not be able to open [or properly use] the database and they will have problems creating new databases because the default security will be set to the network mdw file you have joined their computer to. Remember, all Access databases are secured and by default, they use their computers default system.mdw file. Messing with the computers system default system.mdw file is only asking for trouble.

You never answered my question but I am still guessing that you have altered your computers system.mdw file with security permissions for your database, which is why the users default system.mdw file on their computer is allowing them full access to your database. Again, I believe that your database is not correctly secured or else the users default system.mdw file would not be able to gain full access to your database.

I wish you best of luck to you on your journey using SetDefaultWorkgroupFile.

 

[webstersc]

GHudson,

I know this post is way outdated but I came across it today in a 2 week long search trying to find an answer to an Access issue one of my users had. While it is only a work around until the person that jacked it up can fix the issue, this was a godsend! Giving this syntax ("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "X:\Testing\YourWorkgroupFile.mdw" "X:\Testing\YourDatabase.mdb.mdb" /user TestUser) got me out of a really big jam since I know next to nothing about Access. I joined this forum just to give you a huge THANK YOU!

 

[ghudson]

GHudson,

I know this post is way outdated but I came across it today in a 2 week long search trying to find an answer to an Access issue one of my users had. While it is only a work around until the person that jacked it up can fix the issue, this was a godsend! Giving this syntax ("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "X:\Testing\YourWorkgroupFile.mdw" "X:\Testing\YourDatabase.mdb.mdb" /user TestUser) got me out of a really big jam since I know next to nothing about Access. I joined this forum just to give you a huge THANK YOU!

You're welcome. I am glad it helped you out of a jam and thanks for taking the time to thank me.