Summary: Use this file to find the information about references used in MSAccess (including GUID etc).

Uses: When needing to find a GUID to be able to enforce it to be turned on for an application to work correctly on different machines.

Example: This requires the use of Microsoft Office Object Library 10.0 or above. If you turn this reference on manually and note it's place in the references list - you can use the GUID Information form to find out the information needed to programmatically turn the reference on.


Questions, comments and amendments appreciated - just PM me.

- GeorgeV

the goal of the attached code is to allow different things to happen in:
MDB (development)
MDE first-run (final pre-deployment test)
MDE second and subsequent runs (the deployed application)

this is a stripped-down demo skeleton of my standard startup form for all applications. code comments include a few suggestions for stuff that you might like to include in the different 'modes' if you decide to go with a similar 'standard startup' scheme.

i hope you find it useful! at the very least it should allow you to lock down your deployed MDE with no risk of locking yourself out of your development MDB (and without the need for dubious hard-coded backdoors).

NOTES:

please examine the code to satisfy yourself that this is something you want to run. start the MDB with Shift held down to review the code.

this A2K code late-binds: no special references needed.

Tools/Startup/DisplayFormPage must be frmStartup to run 'as advertised'

in development mode (MDB) you will be prompted for your error-handling preference: respond NO to the question for the demo as-posted. the YES response might be useful later as you add new code to the startup routines.

PM me if issues.

izy

As a follow up to my earlier post, here's a lot better search engine. It can use text boxes and combo boxes as before, but it can now also use multi-select boxes. It also generates an SQL statement from the form based on the criteria provided before passing it to the report as a WHERE clause, instead of using the stored queries as before.
Note: You can use multiple combinations of text/list/combo boxes, I'm just doing one of each on the form to keep things simple for demonstration purposes.

Keep getting the "locked by another user" error or can't copy new code because users have your app open or slow performance of MSAccess file?

Try this method out. This script resolves:

1. "Locked by another user" or "The database has been placed in a state by user admin on a machine which prevents it from being opened" errors - PERMANENTLY!
2. The ability to have an unlimited number of users opening the front-end.
3. The ability to copy code at ANY time, even while users are using your application!
4. Avoids having to regularly compact/repair the frontend.
5. Not having to redo user's desktop shortcut when a frontend is moved to another location.
6. Seeing who's CURRENTLY using the frontend, when a user LAST opened the frontend, and see what "version" a user last opened.
7. Never having to put code in to automatically "logoff" (or quit/close) the user out of the frontend.
8. Automatically repairing corrupt frontends.
9. Safely "hiding" your "source" frontend file and users never open it but are instead, working off of an exact clone of it.

When run, the script will then take the front-end, clone it with the user's loginID (ie. copy/paste), and launch the cloned file - simple as that. (it automatically grabs the user's loginID!)

Since the cloning script always clones off the source mdb/mde, the user will always have the latest code changes (plus, you can safely hide the source mdb/mde file so the user never sees it!) You can again, copy new code at any time to the source mdb/mde, EVEN while the users are in the cloned mdb/mde files (since they are never in your "source" frontend file but instead, an exact clone.) All you simply "should" do is send them an email telling them you've copied new source code and that they ONLY have to close out and get right back in to see the new changes!! (using the vb shortcut.) (out of courtesy, you might want to relate your coding changes in the email to them.)

There's only 1 line of code you need to change in the vb script (LUName = "F:\MyDBFolder\MyMDBorMDEName.mde) and it's ready to use! And if you ever want to move your frontend to another location, you only have to change the location for LUName in the script and don't need to worry about all the shortcuts scattered on different user's desktops.

You can also add a shortcut on the user's desktop to this script (or just email a shortcut to the vb script) and then have them click on that shortcut whenever they want to get into the mdb/mde. When the script is run, it literally takes less than a second to clone the source mdb with the user's LoginID and open the cloned mdb/mde file (note: providing there aren't network problems which slow down copying/pasting of a file.) Otherwise, the user won't even notice any delay.

Then, simply look in the folder where the script clones to (ie. LUName) and look for a MyMDBorMDEFileName(USERNAME).ldb file and you'll know if that user is using the application (if you want to see who's currently using it.) You'll also see when the user last opened the application by simply looking at the date created on the MyMDBorMDEFileName(USERNAME).mdb (or *.mde) file. And you never have to worry about the mdb or mde being locked by another user! And you can have as many users as you want using your application - providing they all use the vb script to open it (since it only takes 1 user to lock a frontend file.)

Again, IMPORTANT - use this vb script for FRONT-END's only!

Do NOT use this script on an mdb which has the data tables in the frontend (unless they're only temp tables created in code/queries.) (ie. you must have linked MSAccess tables or SQL Server/MySQL tables in the frontend) - it's for frontend's only as you obviously don't want to clone the mdb which has the actual backend data tables.) Otherwise if you use it on an mdb which doesn't have linked data tables, it will clone the actual data tables as well and you'll end up with separate data tables.

INSTRUCTIONS: You edit the vbs script (in NOTEPAD) and simply change the 1 line for LUName = which is the location of your mde/mdb file (keep the location/file name in quotes (") as the example illustrates.). It's recommended to use the UNC name for the location but this is not a neccessity. If you have foldernames with spaces, use the ~1 for the 8 character foldername (just like you would do for a shortcut). Make sure when you save the script, you change the "Save As Type" to *.* and save it with the *.vbs extension. You can save the script in any folder. I've documented everything in the script so read the comments in the script itself.

Have each user simply click on the vb script whenever they need to get into your application instead of another shortcut to the mde/mdb front-end. 1 vb script which all of the user's can run. Running the vb script also takes less than a second for it to clone and launch the new mde/mdb front-end.

Dan Wang and myself created this script and I have never had any problems with it (except where users logged into the network with the same loginID). Note: you also want to make sure users can execute a vb script (the default). Otherwise, you edit IE (Internet Explorer) to change permissions for users to execute vb scripts.
The script uses the wscript.exe file as normal to execute any vb script.

Again, Here are more details on the advantages of using this script:

1. You'll NEVER have to worry about users not being able to get into a front-end mdb/mde file because a user locked or corrupted the front-end!
2. No need to ever use any "auto logoff or auto logout" code as it DOESN'T MATTER if the user corrupts the cloned mde/mdb file they are in. Just have them get back into the front-end via the vb script and the user get's a "fresh" cloned copy which will fix a corrupt mdb/mde file.
3. Your "source" front-end mde/mdb file will stay SAFE and READY for new code or backup at any time! It's never used! (except to clone off of.)
4. You can copy new code over the "source" mde/mdb code without making users close out of it. (Again, then just simply send an email to the user to close out and get back in (via the vb script again) at their convenience to see the new code.)
5. You can see who is currently in the mde/mdb (i.e. you'll see a file such as MyMDEApplicationPaulK.ldb in that folder). No need to get a 3rd party application to read *.ldb files to see who's using the application or use ANY other utility!! Just look in the LUName folder for *.ldb files with the user's login name to see who's currently in the app.
6. You can also tell when that user LAST got into the mde/mdb file by looking at the creation date of the mde/mdb file with the users loginID!
7. If you have a lot of make-table queries, this is perfect as each user is in their own front-end so there's no issues with tables being locked or needing to run scheduled compact/repair routines. The next time the user get's into the mde/mdb via the script again, they will have a lean (ie. no data in temp tables), non-bloated application which doesn't need to be compacted and repaired.
8. Users can create a shortcut to the vb script on their desktop and just click on the short-cut (when executed, it will clone the mde/mdb file, add the users windows loginID, and automatically launch the cloned mde/mdb file with the users loginID.) You can simply email a shortcut to the vb script to have users run.
9. You can tell if the user is working with the latest code changes! If MyAppName.mde is 6500 KB and MyAppName(USERNAME).mde is 6300 KB, you know they are not working with the latest code changes. Just have them close out and click on the vb script again.
10. You'll have less problems with code that you would with multiple users in the same front-end! (since again, each user is essentially in their OWN front-end.)
11. The script doesn't necessarily need to be used for just MSAccess files. You can make a few modifications to it and utilize this script to clone ANY other file and run them! (See documentation within the script!)

Special thanks goes to Dan Wang for helping with the initial code.

I hope you find this script as useful as I have! I use it quite a bit for not just MSAcces applications, but other file types as well. If you find it useful, you can email me at: pkohn@charter.net.

'vb script created by pkstormy -- make sure to save as *.vbs extension so it can be executed.
'Use notepad to create this and just copy/paste the code below into a notepad file. Again, save as *.vbs extension.
'note: when saving, change *.txt to *.* files and again, save as *.vbs file (ex: MyAppName.vbs)
'Then just have the user's double-click on the MyAppName.vbs file to execute instead of your MSAccess file.

Set WshNetwork = CreateObject("WScript.Network")

'Now simply grab the user's windows loginID and save to variable GetUser to use later in pasting
GetUser = WshNetwork.UserName

Set WshNetwork = Nothing

'change below LUName to point to the location of your "source" MSAccess file, ideally use UNC name
LUName = "c:\HelpForOthers\PKSampleXP.mde"

oldname = LUName

'change below to "accdb" or "accde" or "mdb", whichever you're using. Below simply adds the GetUser to newName variable
'ex: PKSampleXP.mde becomes 'PKSampleXP<LoginID>.mde for newName variable
newName = Replace(LUName, ".mde", "") & GetUser & ".mde"

retval = 0
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

'below simply copies a "source" file (oldname to newName) and pastes it
retval = objFSO.CopyFile(oldname, newName, True)

Set objFSO = Nothing

Dim objShell
Set objShell = CreateObject("Wscript.Shell")

'now it just simply opens the cloned copy (ie. PKSampleXP<LoginID>.mde)
objShell****n "MSAccess.exe " & newName (replace **** with . run without space)

Set objShell = Nothing

The attachment is a great way to get the users windows login ID. It also shows you how to set up a permissions type table (i.e. admin table) so users don't have to enter passwords (I have enough passwords to remember and so do your users!) And it also shows a nice way to setup a MainFormbackground and MainForm so users are forced to click the "Quit" button on the MainForm and cannot access the MSAccess upper menus. I like this way because I don't have to hold down the shift key to make code changes. I can't count the number of times I had to re-get into the database holding down the shift key to simply make code changes.

The key function to test whether the user is in the dbo_AdminTable is the isAdmin function in the module Check for Admin. This routine can be tweaked to add in security levels or whatever your needs are. The concept is the same.

GetUserAndPermissionsSample.Zip also has a 97 version in it.

New version uploaded 11/7/07 (with additional security functions and levels - open a form up allowing edits or not depending on security level.)
New version uploaded 11/13/07 (with capability to hide/unhide tables or Database Window.)

The attachment is a very nice example of how to set up a report form. The example uses a great "click on a month/year" button example which populates a start date and end date to criteria a report off of (also see Calendar example by pkstormy in this section).

New Version shows you how to export data to a csv based on the date range selected on the form!

You can even browse for the folder you want to export the csv file to. It's all automated.

New version uploaded 10/30/07.

The attachment shows you how to create ODBC DSNs in MSAccess (or refresh linked tables). You'll need to change the Server and User in the module SystemDSNCreate
(i.e. LastUser = "Dave"
Server = "SQLSERVER").

Then just delete the current linked tables and link in one or more of the tables from that/those SQL Server database and it will create ODBC System DSNs to all those SQL Server databases.

Special thanks goes to Dan Wang for his work on this.

I've also attached the ODBCCleanUp.zip which when you run the .reg file, fixes the problem when you create a new System DSN and it doesn't show up in the ODBC Administrator. It basically involves resetting a bit in the registry and I believe it is caused when you have 2 versions of Access on the same computer and create a new ODBC DSN via Access. The ODBCCleanUp.zip has not been tested on other machines (but should work ok) and here is what it does:
All it contains is a 3 line entry. It goes to this registry location and removes the 'default' key.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
@=-

This attachment has 3 fun games: Dice, AtTheTrack, and Simon. I found the Dice game addicting and I've yet to complete 50 clicks in the Simon game but there's example of some good vba code in each. The AtTheTrack racing game is a Horse racing type game I made because I couldn't find one like it anywhere. Have fun.

Here's a good example of how to utilize the browse button to select a file and import it into MSAccess.

Here's 3 neat puzzle games in MSAccess. See if you can solve them.

NOTE: All posts by pkstormy have been tested for viruses and guaranteed to be virus free. NO "need to register" activeX controls are ever used and you do not need other 3rd party software to run them (other than MSAccess itself).

Attached are 11 neat visual basic games/puzzles I designed (I didn't see a code bank in the visual basic forum so I posted them here.) All games/puzzles have been fully tested and are guaranteed virus free. You also don't need to register any "activeX" controls as none are used in any of the code. No 3rd party software is needed and you don't need visual basic or visual studio to run them. Just unzip each game/puzzle in the VBGamesByPK.zip file and run the executable. There is NO install/uninstall routine (i.e. it doesn't write to the registry or register any software) - just delete the executable to remove them.

Puzzles are:
5 Gallon Puzzle (easy puzzle taken from the movie Die Hard)
BlueToRed Puzzle (move the red pegs to the blue and vice-versa - challenging)
Circles (my favorite - match the pattern and colors on all the circles - challenging)
LightsOn (turn on all the lights puzzle redone from the web with solution)
Lines (an interesting rotate/swap/color matching lines puzzle)
MathPuzzle (an easy arithmetic puzzle)
PuzzleGame (make 15 in all directions - challenging first puzzle I designed)

Games are:
At The Track (bet on the motorcycle (horse racing type) game with cheats)
21 Card Game (fun solitaire cellphone game I recreated)
Dice Game (my favorite - eliminate all the numbers based on the roll of the dice)
Simon (based on the simon game - see if you can reach 50 clicks.)

If you like one of the puzzles or games and you want the source code, drop me an email at pkohn@charter.net. There are some good vb code samples in them which I can supply you upon request.

Feel free to pass them on to anyone else which might like them. I only ask that you give me feedback on how you like them.

See if you can solve some of the challenging puzzles and drop me an email.

Ever want to search for a business/customer with a form where the user can click on a A, B, C, D, etc.. buttons? This attachment shows how you can easily do this.

Here's another snippet which you might find useful. It shows you how to have 1 (and only need 1) popup calendar form that you can utilize from any other form. When you call this CalPopUp form, you pass the form name (that you want the date populated on), subform name (if the date you want populated is on a subform - otherwise don't pass this value), and the field name (of the date field on the from which you want to populate). Then when you select a date on the CalPopUp form and click Save, it will pass the date selected to the form name, subform name (if it's populated), and field name. Thus, you only need 1 calendar form which you can call from any other form (no need to have a separate calendar form for every date field on every form.)

The attached examples shows a couple of ways you can produce a write conflict. There are many other ways (such as trying to manipulate data even when only one form is open) but this shows how you can most assuredly produce the error. The general concept of this example is that you want to be careful when you open multiple forms based on the same recordset.

This is the same Calendar example as previously posted but shows an updated example to include subforms.

NEW version - uploaded 10/6/07 (now has buttons on calendar popup form to quickly select month/year!)

Also demonstrates using the Screen.ActiveControl.Value function