October 4th, 2011
We tend to see numerous questions relating to MS Access Autonumbers. Specifically, regarding autonumber not following sequence, skipping sequence.
Regardless of how one might interpret what is written in the help files, AutoNumbers should never, ever, ever, be relied upon as a sequential number. Nor should they ever be used/displayed to the end-user. The simple fact of the matter is that AutoNumbers are merely unique identifiers for each record. Even when set to be incremental, the AutoNumber can be indexed even though no record was actually inserted into the table. This is not a bug, this is simply the way Access was developed.
So what does one do if they require a sequencial number to attribute to each record. The solution is actually very simple. Create a new field in your table and then you can used an equation such as =Dmax(…)+1 to generate the next number in your sequence. But at the end of the day, if you want a sequential number that will not jump sequence and can be faithfully relied upon, you have to create it yourself!
MS Access, MS Access General Information, MS Access Tables | 
No Comments »
August 19th, 2011
Another interesting question I was once asked on an Access forum was how can one retrieve a random record in a form?
I was actually perplexed as to how to approach this request, but it really isn’t that complicated at the end of the day. The code below demonstrates one possible method.
'---------------------------------------------------------------------------------------
' Procedure : GetRndRec
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Goto/retrieve a random record
' 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).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Dec-21 Initial Release
'---------------------------------------------------------------------------------------
Function GetRndRec()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tblName As String 'Table to pull random record from
Dim iRecCount As Long 'Number of record in the table
Dim iRndRecNum As Integer
tblName = "YourTableName"
Set db = CurrentDb()
Set rs = db.OpenRecordset(tblName, dbOpenSnapshot, dbReadOnly, dbReadOnly)
If rs.RecordCount <> 0 Then 'ensure there are records in the table before proceeding
With rs
rs.MoveLast 'move to the end to ensure accurate recordcount value
iRecCount = rs.RecordCount
iRndRecNum = Int((iRecCount - 1 + 1) * Rnd + 1) 'Get Random Rec Number to use
rs.MoveFirst
.Move CLng(iRndRecNum)
GetRndRec = ![YourFieldName]
End With
End If
Resume Error_Handler_Exit
On Error Resume Next
'Cleanup
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetRndRec" & vbCrLf & _
"Error Description: " & Err.Description _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
MS Access Forms, MS Access Tables, MS Access VBA Programming | No Comments »
August 18th, 2011
This was the question put forth by someone on an Access Forum recently and I thought I’d share one possible way to determine this.
This is a brute force method, but it works! I simply loop through all the tables one by one and loop through all the fields within each table one by one. It is that simple. Here is the code.
'---------------------------------------------------------------------------------------
' Procedure : WhereFieldLocated
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine/Locate in which Table(s) a field is located
' 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:
' ~~~~~~~~~~~~~~~~
' sFieldName: The name of the field you are trying to locate
'
' Usage:
' ~~~~~~
' WhereFieldLocated "Filed1"
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-Aug-17 Initial Release
'---------------------------------------------------------------------------------------
Function WhereFieldLocated(sFieldName As String)
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim fld As Field
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the tables in the database
If Left(t.Name, 4) = "MSys" Then GoTo Continue
For Each fld In t.Fields 'loop through all the fields of the table
If fld.Name = sFieldName Then
Debug.Print t.Name
End If
Next
Continue:
Next
Set td = Nothing
Set db = Nothing
End Function
MS Access Tables, MS Access VBA Programming | 1 Comment »
July 21st, 2011
Now here is a question I had myself many years ago and could never find a proper answer to! And yet it is such a basic element and such an important one at that!!!
So when one creates a split database (which they all should be), where do you create the tables relationships exactly? Do you create the MS Access table relationship within the Front-End or the Back-End?
The short answer is, in the Back-End. Here are a few explanation from a couple fellow MVPs taken from a recent forum question.
The relationships need to be established in the backend. In fact, you can build a diagram in the frontend but for the backend tables, the referential integrity will not be enforced if the relationships aren’t in the backend — Bob Larson, Access MVP
If you create relationships in the front end the only thing it achieves is that it determines the default joins types when you create a query in design view. To ensure data integrity through enforced relationships they must be created in the back end. So, the recent advice you were given is wrong. You can if you wish create them in the front end in addition to, but certainly not in place of, those in the back end. — Ken Sheridan
What is important to understand here is the fact that you should always create your table relationships in the Back-End of your database. That said, as Ken stated it can be useful to merely recreate them within the Front-End as well to simplify Query building, but then this then incurs extra overhead when the database is modified (Now you have to update the relationships in both locations). The other time when you might create MS Access table relationships within the Front-End of your database is because you have lookup table, reference tables in the Front-End. In such a case, obviously you can’t create the necessary table relationships in the Back-End since the tables don’t exists there. As such, you’d create the necessary relationships directly within the Front-End to ensure referential integrity.
MS Access General Information, MS Access Tables | No Comments »
June 10th, 2011
Ever needed to delete the linked tables out of your database. Sadly Access does not allow one to make multiple selection of Access object to perform batch actions on, such as delete. So if you have quite a few tables to delete it can be frustrating and a waste of time. This is why I create the very simply procedure found below.
'---------------------------------------------------------------------------------------
' Procedure : DeleteAttachedTbls
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Deletes all the linked tables from the active database. It only removes
' the links and does not actually delete the tables from the back-end
' database.
' 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).
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2011-Jun-10 Initial Release
'---------------------------------------------------------------------------------------
Function DeleteAttachedTbls()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
DoCmd.SetWarnings False
Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next
Error_Handler_Exit:
DoCmd.SetWarnings True
Set tdf = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: DeleteAttachedTbls" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
MS Access Tables, MS Access VBA Programming | 2 Comments »
May 25th, 2011
Here is a simple bit of code that permits you to hide the MS Access’ main object browser, to stop nosy users from accessing tables, queries, etc…
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
In conjunction with the code to determine whether the user is running the runtime or full version of Access (see MS Access – Determine if Runtime or Full Version
) you could insert a section of code such as:
If SysCmd(acSysCmdRuntime) = False Then
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
End IfThis would also be a good place to enable any custom command bars/ribbons and/or disable any built-in command bars/ribbons…
MS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | No Comments »
May 3rd, 2011
Here is a common question: “How can I make a field mandatory to my users?”
Well, as always, there are different techniques that can be employed. I will breifly covert 2: using Table Field Properties and using Form Events.
Table Setup
The easiest method is to merely set the table’s Required field property to Yes.
That said, this approach does present limitations. Mainly the fact that the message returned to the user references the Field Name (see the example below) and not its’ Caption or Description or Form Control Name, so the message in fact ends up confusing most users! This is why, using form event can come in handy.
The field ‘YourFieldName’ cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field.
Form Event
Happily, we can add a lot more functionality and user-friendliness to our form by using its’ BeforeUpdate event to perform our validation. By inserting a little VBA code, for instance, something along the lines of:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[YourControlName]) Then
Cancel = True
MsgBox "You must enter a value for 'YourControlNameOrYourDescription'. Please make a valid entry or press ESC to Cancel"
'You could set the focus on the specific control if your wish, change the background color, ...
End If
End SubAs you can see, both are easy to implement, but the second method offers much more control and you can explain to your users in plain English to problem. Furthermore, you could also perform more advance data validation to not only ensure they have made an entry, but that it meet the require format…
MS Access Forms, MS Access Tables | No Comments »
April 11th, 2011
So how can one take data from one database and insert it into another database?
There are 2 methods that come to mind and both are quick and easy to implement.
Specifying the Database to Append to in Your SQL Statement
The first method is to modify your query to include the external database as a reference. A basic Insert query would look something along the lines of:
INSERT INTO TableName ( Field1, Field2, ...)
VALUES ( Value1, Value2, ...);
but did you know you can also include, as part of your SQL statement, the database to append the data into?! So we could easily modify the SQL statement like:
INSERT INTO TableName ( Field1, Field2, ...) In 'FullPathAndDbNameWithExtension'
VALUES ( Value1, Value2, ...);
Here’s a concrete example:
INSERT INTO Temp1( FirstName, LastName) In 'C:\Databases\Clients.mdb'
VALUES ( "Don", "Hilman");
Using Linked Tables
Another approach is to merely create a linked table to your secondary database and then run a normal append query on your linked table!
Either way, as you can clearly see for yourself, it is not a hard thing to insert data into a table contained within another external database!
MS Access Queries, MS Access Tables | No Comments »
April 7th, 2011
In any well developed MS Access database it becomes necessary to automate the relinking of the back-end database tables. There are any number of existing resources that you can very rapidly implement.
Below are a few useful link to get you going:
In complexe database setups, it may become necessary to relink your database to multiple back-ends. I started out writting my own code to do this and then came accross nice sample from David Fenton Associates:
Hopefully these links will save you some time searching the net.
MS Access, MS Access Tables, MS Access VBA Programming | No Comments »
