This example is to be used for MSAccess databases with SQL Server linked tables. When you click the "Find Users Logged In" button in the example, you are prompted for the SQL Server name (this can automatically be coded). Notice that a text file is also generated in the folder called: remoteshareinfo.log. You can then select one of the other buttons to see users in the MSAccess files and which ones they are in as well as other information.

This example shows how to quickly create a right-click popup menu.

Here's the security demo database that I found and used in the creation of my database. It's pretty good and does not use the MS Access User Level Security that is built into Access. I've found it much easier to use this versus the ULS in Access because there can be major issue with the Access ULS if its not done correctly or if the db administrator leaves and does not tell the replacement everything.

This example shows how to use the inbuilt Calender ActiveX control to load a date into a text box/combo box, using only a single calender control for multiple text boxes on the screen.

The attached database is a small example of how we can use the file system object (FSO) in Access for "attaching" files to our database.

the FSO is very customizable, allowing you to add your own filters etc - many examples are applied in this project.

Any paths and names of files selected using the FSO are stored in a table in the database, this can be very useful for bookmarking files, creating favourites list etc etc.

The FSO object requires the use of a reference called "Microsoft Office Object Library 10.0" (or above). The On_Load() event of the form automatically checks the access version and adds the reference by GUID.
More information on GUID's can be found here.

The code is commented pretty well but if you have any questions then feel free to PM me.
-------------------
EDIT: 2007-06-28
Added more functionality including a variation on the FSO which allows you to select folders as opposed to files. Minor coding tweaks and tidying up.
-------------------

~ George

This attachment demonstrates how to create a report based on a date range and city criteria off of a form. The example has a nice calendar system that is completely button driven so the user never has to enter a month, day or year except to click a button.

If you want to base your totals on a date range or some other criteria, take a look at this example. This example also tests to see if the StartDate is after the EndDate on the form.

The example also utilizes making a temp MeasureTotals table to demonstrate the ability to re-run the same report without having to re-query the database (if your data table is extremely large and it takes several minutes to run the query, you can open the same report without re-querying unless you change the criteria). This can be changed so that the report is based off of the query verses the table if need be.

Special thanks goes to Dave Bussan for his contribution on the coding for this.

This sample allows you to automatically create ODBC DSN's on a user's machine rather than doing it manually.

Simply link your tables into this mdb file and click the update button from the user's machine and an ODBC DSN will be created on that user's machine for each linked table database that it recognizes. It automatically recognizes the data source name associated with the linked table so you never have to worry about misspelling the name.

A good tool for distributing your SQL Server linked table Access files. Thanks goes to Dan Wang for his contribution.

Based on a tip from the inexorable Poots and from GolferGuy, and some other additions/modifications I've found useful over the last while, here's v2 of my previous post.

Simply copy the form from the attached dB and paste it into your form, making sure you have the Microsoft ActiveX Data Objects 2.1 Library referenced. Just add some way for you/the admin to access this form.

Enjoy!

This shows some different techniques on searching for data in a table using several different ways. From simple search boxes to utilizing alphabetic buttons to using the OnChange event and other search ways. It also shows a query on how to handle blank valued fields. And how to do date criteria, and also an OR on a field criteria (all in 1 query!) Take the part that works for you. And the last example shows how you can highlight the current record on a continuous form.

Regarding the OnChange search form example, everything I've found uses some complex routine or issues a me.MySearchText.SelStart = me.MySearchText.SelLength command which causes a problem if a space is entered (ie..entering "All State" truncates when you push the space after "All" which makes the searching problematic.) Setting up a complex sql statement or some other complex routine with dozens of lines of code sounded unreasonable and I wanted to do it with 1 very simple query and only 2-3 lines of the simpliest code and method I could find (ie. NO complex Functions)!

So I played around with setting a boolean value to true or false depending on if the spacebar is pushed in the OnKeyPress event for the searchbox. The OnChange event of the searchbox then simply looks at the boolean value and will ignore firing if a spacebar is pushed.

On this search form it also automatically detects the email column of the listbox and is set up so you can quickly locate a name (such as in Outlook), and create an email with a few keystrokes.

Look at the "Sample Business Search Form 2" In this attachment to see how this works.

"Sample Business Search Form 1" is a typical search designed to quickly tab through alphabetical letter buttons.

"Sample Business Search Form 3" and the "Other Search Criteria" forms are some examples showing how you can query a database with blank field values in a search form. No need to write any complex code for searches because you need to search on multiple fields or you have null values in the records - you can do it with a simple expression in 1 very, very simple query!!

"Sample Business Search Form 4" shows you how to quickly select a field name and search for values in that field using the search for every character method. It also uses the up/down arrows in the listbox so you can scroll through the values in the listbox (by simply tabbing to the listbox - no mouse needed to click on the listbox).

There is also a sample form showing how you can highlight the current record you are on in a continuous form.

Again, There's NO complex sql code in any of these examples!! They all use 1 very simple query and only 3-4 lines of easy to understand code in the forms.

It's worth a look.

New version (fixed) and with more options to search by.

This is a very nice little menu system I developed for storing file locations and filenames in a table and then opening those files. It shows how you can populate a table with the file location and file name using the Dir() function with ANY file types and it also shows 3 different ways on how to open a stored filename (of any type) in a table. This program/utility has many, many features including:

1. Quickly search any drive/directory, find files of ANY specified type (ex: mdb, dll, exe, doc, xls, xlw, bmp, etc...etc....), and STORE those file locations in a table for quick access via the menu.
2. FSO file searching ability to find files of ANY type (You do NOT need the Office Object Library 10 or any other additional references in this mdb example).
3. "keyword" searches for filenames and quickly find any filepath/name with the wording "shortcut" or any wording you specify.
4. Code to open stored files of any types (3 different methods of opening files - if the first method fails, it automatically executes the 2nd method (which utilizes the windows file association). If that fails, it will prompt you for the program name to open the file.)
5. 2 listboxes on the main form, 1 to select files newer than the date you specifiy and the other to list files older than the date you specify.
6. Quick tabs for listing MSAccess, Word, Excel, and Picture files.
7. JetUsersLog incorporated into it to find any users in any specified mdb file.
8. Sort file listing by Date, foldername, or filename.
9. Tracks users and which files they open at what time.
10. Plus many, many more features - a great utility regardless if you're an MSAccess user or not.

There is also code in this utility to create a desktop shortcut to the users desktop for a selected file.

And lastly, there is code (in the main form's onLoad event) which will remove all of MSAccess menu's and only show the form (ie. see module2).

Hold down the shift key while opening to see the code behind the form.

Also has the resize module in it for resizing forms.
Also has the FormInfo module for removing caption bars or manipulating forms.

NEW VERSION - Just uploaded on 9/10/07!

New version uploaded 3/1/08! (MenuSystemInAccessByEDP2008 - trimmed out excess code).

While this isn't rocket science, I have posted several times asking folks how to programaticaly embed a file into an OLE field from a form. I did not get any workable solutions. I am not sure if it is because I am the only one with a need to do this or if it is that no one knew how. So, after much digging and experimenting, I present to you a working solution if you have this need.

Important note:
Whether you are planning to Embed or Link the file, the "OLE Type Allowed" MUST be set to either for the Bound Object Frame.

The database consists of a form that opens when you open the database. When you click the "Attach File" button, a TextBox and a Bound Object Frame are displayed. The standard Windows File Open Dialog Box displays with a caption of "Select File to Attach!". The caption can be changed to suit your taste. Once a file is selected, it will be embedded or linked. The code currently Embeds the file. In order to Link the file (usefull for graphics files, comment out the Embed line and uncomment the Link line in the code.

I hope this is helpful to someone.


Enjoy,
Brent Blevins

The attached is a slightly modifed version of the JetUserLog_v2 nckdryr supplied above.

Modified are the following:

.AddItem removed from code (this was causing problems in MSAccess 2000 as this method is apparently not available for MSAccess 2000/earlier)

Added - capability to select the mdb file name.

Thanks goes to nckdryr for supplying the original code (thanks Nick!)

something neat i designed and wanted to share...

This code allows you to use a button, that will open another form and reference the memo/notes field of that particular record.

In theory this is easy. however this code is designed so that a single "notes" form can be used for an unlimited number of other forms

example:
you have 4 tables all which have a notes portion

Vendors....Customers....Purchase Orders....Receipts

since they all have a common field "NOTES" you can create a single form named "notes" then use the following code on each of these forms to open the notes form and display the code for that particular record.


in this example frm_memonotes is my "notes" form
place in a button on any form.

The attached example shows a way you can enter key words in a table and search through a table/fields for those key words, storing the wording found after the key word match (until a space is found or another character you designate) in another table (where you specify the fields to store the values.)

For example, in the "SearchForValues" table you can enter RC: (and designate to store matches after RC: in the RC field) and MC: (and designate to store matches after MC: in the MC field), etc...etc... If the string value in the search field is RC: 334X MC: 333 ... 334X will be stored in the RC field and 333 will be stored in the MC field. You can have an unlimited number of search values to search for and fields designated to store the values in. It will loop through the entire search table searching for all matches of the designated values and storing the found values after the match in a table. You can also designate to exclude certain values in the string match so in the above example, you could choose to exclude X from the RC: match and 334 instead of 334X will be stored.

I've used this routine to import a file into a table and search through that table for key wording, storing the values after the key wording in a table. If you happen to find this useful, let me know.

The attached zip contains several word documents which have several different vba coding examples. For example, there are word documents which shows the following code in vba:

1. Relinking tables
2. Browse for a file
3. Bypass Autoexec
4. Calculate the first day of the month
5. Calculate work days
6. Change cursor
7. Linking tables
8. Compact and Repair
9. Concatenating
10. Convert a number to text
11. Convert a currency value to the written out string for a check (ie. $10.00 = Ten and 00/100 dollars)
12. Count mouse ticks
13. Create DSN's on the fly
14. Decompiling on the fly
15. Changing default printer
16. Change mouse pointer
17. Disable mouse wheel
18. Check to see if a table exists
19. Execute a DTS via vba
20. Extracting numbers from a text field
21. Get age example
22. Get Date function
23. Get FullName in a domain
24. Get rid of close X on a maximized form
25. Get UserName
26. Importing Outlook messages
27. Key stop code
28. Listing files in a directory
29. Map a network drive in vba
30. Detecting multiple users in the same mdb
31. Password prompt
32. Reading the registry in vba
33. Retrieve domain name
34. Running wizards via vba
35. Set priority to MSAccess
36. Shell command in vba
37. SQL Server syntax on Like statement
38. Output an MSAccess report to a PDF
39. Use FTP in MSAccess