This database demonstrates how to insert a clock in your forms to display the current time as well as a demonstration of how to create a timer used to determine the amount of time which a form has been open for.
Download the sample database: MS Access Clocks and Timers Sample Database
MS Access Samples | 
Comments Off
This database demonstrates some of the most common ways to work with dates. It demonstrates how to extract only a part of a date (year, month or day), how to display a date in different formats (ISO, American,…), how to determine the number of days, weeks between 2 dates, etc.
Download the sample database: MS Access Working with Dates Sample Database
MS Access Samples | Comments Off
PC InformationA simple HTML Application (HTA) which can retrieve an extensive set of information on various computer aspects. Currently, it can return information on hardware, printer, memeory, cpu and hard drives, processes, video and audio,… It can also query other computers (as long as you have admin priviledges on the remote machine). Simply add remote computer names to the associated text file.
Download the sample database: PC Information Utility HTA
Uncategorized | Comments Off
This database example show the proper way to work with images within a database (which is not to embed them into the database).
Download the sample database: MS Access Images Sample Database
MS Access Samples | Comments Off
If you are using user-level security (ULS), the following is a simple demonstration of a form that can be added to any database to permit the user the ability to change their password. Useful when you create an mde or disable the standard toolbars for increased security but still want to give the user the ability to rotate their own password.
Download the sample database: User-Level Security Change of Password Form Demonstration
Special Note & Warning
Be very careful using this sample. If you run it on a database which does not have a designated mdw security database assigned, it will alter the system.mdw, thus affecting ALL your databases! It is only meant to be run on secured databases using ULS security. Any other applications can have disastrous results on your databases. I mention this because I recently was e-mail by someone who made this mistake. Below is the solution to fix such a mistake:
So if I understand the situation properly, you ran the chgPwd.mdb on your computer, in a db, or on its own in a db that wasn’t already secured? I would assume that by doing so you inadvertantly applied a password against your system mdw database. So in fact you secured the master system.mdw database, thus imposing a password against all databases running on your computer. The fix, you’d need to either reinstall Access, or copy over a system.mdw from another clean PC over to your.
MS Access Samples | Comments Off
This database provides a single form which permits the user to either import or export data to/from the database from a selected source (spreadsheet).
Download the sample database: MS Access Data Transfer Sample Database
MS Access Samples | Comments Off
This is a very simple database which provides the user a form which permits them to convert color values between OLE Color values, RGB values and HEX values.
Download the sample database: MS Database Color Converter Sample Database
MS Access Samples | Comments Off
This database demonstrates common programming concepts, such as: basing one combo box’s list on anothers value, changing a control’s background color, changing a form’s background color. This sample database will be updated as I have time to add to it.
Download the sample database: Common MS Access Issues Sample Database
MS Access Samples | Comments Off
If you have ever tried to use the File Scripting Object to perform a FolderMove, you’ve quickly learnt that it spits out a Permission Denied error when you try to move a folder to another drive or try to move a folder which is not on the same drive as the move.exe. So how can you get around this problem, well actually, it is quite easy. The function below will perform the move for you. Instead of simply moving the directory, we actually copy it and then delete the source directory. Easy as pie! Enjoy
'--------------------------------------------------------------------------------------- ' Procedure : MoveFolder ' Author : CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Move a folder ' Better version of the FSO's MoveFolder method which is basically a "rename" ' method, hence it only works if the source and destination reside on ' the same volume (same as move.exe under WinXP) and typically returns ' a permission denied error. ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). It may not be sold/resold or reposted on other sites (links ' back to this site are allowed). ' ' Input Variables: ' ~~~~~~~~~~~~~~~~ ' sFolderSource Folder to move ' sFolderDestination Folder to move the folder to ' bOverWriteFiles Whether to overwrite file(s) if the folder already exists ' ' Usage Example: ' ~~~~~~~~~~~~~~~~ ' MoveFolder("C:\Temp", "D:\Development\New") ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 2010-Nov-14 Initial Release '--------------------------------------------------------------------------------------- Function MoveFolder(sFolderSource As String, sFolderDestination As String, _ bOverWriteFiles As Boolean) As Boolean On Error GoTo Error_Handler Dim fs As Object MoveFolder = False Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles fs.DeleteFolder sFolderSource, True MoveFolder = True Error_Handler_Exit: On Error Resume Next Set fs = Nothing Exit Function Error_Handler: If Err.Number = 76 Then MsgBox "The 'Source Folder' could not be found to make a copy of.", _ vbCritical, "Unable to Find the Specified Folder" Else MsgBox "The following error has occured" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: MoveFolder" & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical, "An Error has Occured!" End If Resume Error_Handler_Exit End Function |
Below is a simple little function which will allow you to make a copy of a folder since it uses the File Scripting Object it can be used in all VBA Applications (Word, Excel, Access, PowerPoint, …).
'--------------------------------------------------------------------------------------- ' Procedure : CopyFolder ' Author : CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Copy a folder ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). It may not be sold/resold or reposted on other sites (links ' back to this site are allowed). ' ' Input Variables: ' ~~~~~~~~~~~~~~~~ ' sFolderSource Folder to be copied ' sFolderDestination Folder to copy to ' bOverWriteFiles Whether to overwrite file(s) if the folder already exists ' ' Usage Example: ' ~~~~~~~~~~~~~~~~ ' CopyFolder("C:\Temp", "D:\Development\New", True) ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 2010-Nov-14 Initial Release '--------------------------------------------------------------------------------------- Function CopyFolder(sFolderSource As String, sFolderDestination As String, _ bOverWriteFiles As Boolean) As Boolean On Error GoTo Error_Handler Dim fs As Object CopyFolder = False Set fs = CreateObject("Scripting.FileSystemObject") fs.CopyFolder sFolderSource, sFolderDestination, bOverWriteFiles CopyFolder = True Error_Handler_Exit: On Error Resume Next Set fs = Nothing Exit Function Error_Handler: If Err.Number = 76 Then MsgBox "The 'Source Folder' could not be found to make a copy of.", _ vbCritical, "Unable to Find the Specified Folder" Else MsgBox "The following error has occured" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: CopyFolder" & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical, "An Error has Occured!" End If Resume Error_Handler_Exit End Function |
If you ever made a report and bound a textbox to a Yes/No field you will have been surprised to see the output returned as 0s and 1s!
So how can we change it so they get returned as Yes/No values in the report?
I have seen people resort to IIF() statements to convert -1 to Yes and 0 to No, but you need not even do this!
The easiest, and I suppose proper way to handle this case, is to set the textbox’s Format property to Yes/no in the Format tab of the Properties window. Voila! You now have Yes/No value returned in your report.
So how can you run a query in another database?
Well, that depends! It depends on whether you simply need to run an action query or if you actually wish to get the results returned to you.
One method is to use DAO programming to access the remote db and simply execute the query. This implies that you are simply wanting to run an action query.
Dim db As DAO.Database Set db = DBEngine.Workspaces(0).OpenDatabase("FullPathAndFileNameOfThe2ndDb") db.Execute "TheQueryNameYouWishToExecute", dbFailOnError Set db = Nothing |
Another method which will actually return the results of a SELECT query… is to add the 2nd database as a reference in your 1st database and then call a function which open you query. You can find a sample database illustrating this technique at http://www.access-programmers.co.uk/forums/showthread.php?t=156716 (in the second post by MStef).
MS Access Queries, MS Access VBA Programming | No Comments »
As you start to do more and more advanced vba development you may have the need to determine whether your user is using the full blown version of MS Access or the runtime version. Some code/procedures will throw errors in the runtime, that normally would not in the full blown version of MS Access. So how can you determine this? Well, it is surprisingly simple! A simple IF statement can determine this for you, as shown below.
If SysCmd(acSysCmdRuntime) = False Then 'The user is using a full blown version of MS Access Else 'The user is using the runtime version of MS Access End If |
