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 »
October 18th, 2010
Now here is an interesting question!
My first reaction to such a question is why? Since proper database design implicitly prohibits the users from ever directly accessing the database tables, this should never truly be an issue for any developer. But then again, as I have learnt of the course of the years, there are so many exceptional cases…
A Few Solutions to Make a Table Read-Only
- If you are using an mdb format, then you could simply implement User-Level Security (ULS) and simply apply the proper permissions to make your desired tables read-only to your users.
- A more general solution, and one that would also work in all versions of MS Access, would be to migrate the tables you wish to be in Read-Only mode, into a second back-end file and then set the file attributes to Read-Only. Then link the tables to your front-end.
MS Access General Information, MS Access Tables | No Comments »
September 19th, 2010
I was asked in a forum how one could automate importing the links (path & Filename) of all the files contained within a specified directory. It is relatively easy to accomplish and the procedure below is one possible method.
The procedure has 2 input variables: strPath which is the full path of the directory whose files you wish to import all the file paths from adn strFilter which is an optional input variable should you wish to refine what type of document is imported (for instance is you only want to import PDFs then you’d enter “pdf”, Word documents “doc”, and so on).
Function ImportDirListing(strPath As String, Optional strFilter As String)
' Author: CARDA Consultants Inc, 2007-01-19
' 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).
'
' strPath = full path include trailing ie:"c:windows"
' strFilter = extension of files ie:"pdf". if you want to return
' a complete listing of all the files enter a value of
' "*" as the strFilter
On Error GoTo Error_Handler
Dim MyFile As String
Dim db As Database
Dim sSQL As String
Set db = CurrentDb()
'Add the trailing if it was omitted
If Right(strPath, 1) <> "" Then strPath = strPath & ""
'Modify the strFilter to include all files if omitted in the function
'call
If strFilter = "" Then strFilter = "*"
'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(strPath & "*." & strFilter)
Do While MyFile <> ""
'Debug.Print MyFile
sSQL = "INSERT INTO [YourTableName] (YourTableFieldName) VALUES(""" & MyFile & """)"
db.Execute sSQL, dbFailOnError
'dbs.RecordsAffected 'could be used to validate that the
'query actually worked
MyFile = Dir$
Loop
Error_Handler_Exit:
On Error Resume Next
Set db = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ImportDirListing" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, _
"An Error has Occured!"
Resume Error_Handler_Exit
End FunctionAs always, I hope this is useful to someone.
MS Access Tables, MS Access VBA Programming | 1 Comment »
June 12th, 2010
It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows:
SELECT MsysObjects.Name AS [List OF TABLES]
FROM MsysObjects
WHERE (((MsysObjects.Name) NOT LIKE "~*" AND (MsysObjects.Name) NOT LIKE "MSys*") AND ((MsysObjects.TYPE)=1)) ORDER BY MsysObjects.Name;
You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various values that can be used to return the various objects available in Access:
| Object Type |
Value |
| Tables (Local) |
1 |
| Tables (Linked using ODBC) |
4 |
| Tables (Linked) |
6 |
| Queries |
5 |
| Forms |
-32768 |
| Reports |
-32764 |
| Macros |
-32766 |
| Modules |
-32761 |
MS Access Forms, MS Access General Information, MS Access Queries, MS Access Reports, MS Access Tables | 3 Comments »
June 12th, 2010
One very frequent ‘action’ that programmers need to do is to loop through records. This could be a Table or Query … The basic concept is illustrated below using DAO. Although this can be done using ADO as well, I use DAO as it is native to Access and thus most efficient.
Sub LoopRecExample()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName") 'open the recordset for use (table, Query, SQL Statement)
With rs
If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
'The next 2 line will determine the number of returned records
rs.MoveLast 'This is required otherwise you may not get the right count
iCount = rs.RecordCount 'Determine the number of returned records
Do While Not .BOF
'Do something with the recordset/Your Code Goes Here
.MovePrevious
Loop
End If
End With
rs.Close 'Close the recordset
Error_Handler_Exit:
On Error Resume Next
'Cleanup after ourselves
Set rs = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
MS Access Queries, MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
Ever needed a way to determine if a table contained a specific field? The following procedure permits you to check exactly that.
'---------------------------------------------------------------------------------------
' Procedure : DoesTblFieldExist
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if a field exists with the specified table
' 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:
' ~~~~~~~~~~~~~~~~
' sTableName: Name of the table to check the existance of the field in
' sFieldName: Name of the field to check the existance of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' DoesTblFieldExist("Table1","Field1")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-Feb-02 Initial Release
'---------------------------------------------------------------------------------------
Function DoesTblFieldExist(sTableName As String, sFieldName As String) As Boolean
Dim db As DAO.Database
Dim tdf As TableDef
Dim I As String
Dim bTableExists As Boolean
On Error GoTo Error_Handler
'Initialize our variables
DoesTblFieldExist = False
bTableExists = False
Set db = CurrentDb()
Set tdf = db.TableDefs(sTableName)
bTableExists = True 'If we made it to here without triggering an error
'the table exists
I = tdf.Fields(sFieldName).Name
DoesTblFieldExist = True 'If we made it to here without triggering an
'error the table field exists
Error_Handler_Exit:
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
Exit Function
Error_Handler:
If Err.Number = 3265 And bTableExists = False Then
'Table not found in the current database
MsgBox "The specified Table could not be found in the current database", vbCritical
ElseIf Err.Number = 3265 And bTableExists = True Then
'Field not found in the specified table
Else
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: DoesTblFieldExist" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Function
MS Access Tables, MS Access VBA Programming | 2 Comments »
June 10th, 2010
A simple procedure to test for the existance of a table in the current database.
'---------------------------------------------------------------------------------------
' Procedure : DoesTblExist
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if the specified table exists or not in the current 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).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName: Name of the table to check the existance of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' DoesTblExist("Table1")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2010-Feb-02 Initial Release
'---------------------------------------------------------------------------------------
Function DoesTblExist(sTableName As String) As Boolean
Dim db As DAO.Database
Dim tdf As TableDef
On Error GoTo Error_Handler
'Initialize our variable
DoesTblExist = False
Set db = CurrentDb()
Set tdf = db.TableDefs(sTableName)
DoesTblExist = True 'If we made it to here without triggering an error
'the table exists
Error_Handler_Exit:
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
Exit Function
Error_Handler:
If Err.Number = 3265 Then
'If we are here it is because the table could not be found
Else
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: DoesTblExist" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End FunctionAnother alternate approach is to simply try to utilize the table using code and if it returns an error you know the table does not exist. Both approaches are valid and usage depends on your needs.
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
The following procedure will import all the non-system tables from the specified database into the current database.
'---------------------------------------------------------------------------------------
' Procedure : ImportAllTbls
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Import all the tables from an external Access 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).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sExtDbPath - Full Path & Filename of the Database to import the tables from
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ImportAllTbls "C:\Databases\development01.mdb"
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Jan-07 Initial Release
'---------------------------------------------------------------------------------------
Sub ImportAllTbls(sExtDbPath As String)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = OpenDatabase(sExtDbPath)
For Each tdf In db.TableDefs 'Loop through all the table in the external database
If Left(tdf.Name, 4) <> "MSys" Then 'Exclude System Tables
On Error Resume Next
Access.DoCmd.TransferDatabase acImport, "Microsoft Access", sExtDbPath, _
acTable, tdf.Name, tdf.Name, False
End If
Next tdf
db.Close
Set db = Nothing 'Cleanup after ourselves
Exit Sub
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: ImportAllTbls" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Sub
End Sub
MS Access Tables, MS Access VBA Programming | 3 Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : findmaketbl
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Locate which Make-Table Query is creating a table
' 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:
' ~~~~~~~~~~~~~~~~
' sTableName Name of the Table that you believe is created by a Make-Table Query.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
'
**************************************************************************************
' 1 2009-Jun-12 Initial Release
'---------------------------------------------------------------------------------------
Function findmaketbl(sTableName As String)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String
Set db = CurrentDb
For Each qdf In db.QueryDefs
'qdf.Name 'The current query's name
'qdf.SQL 'The current query's SQL statement
sSQL = qdf.SQL
If InStr(sSQL, " INTO ") And InStr(sSQL, sTableName) Then
'The Query is a Make Table Query and has our TableName we are looking for
MsgBox "Query:'" & qdf.Name & "' is a Make-Table Query for Table '" & _
sTableName & "'.", vbInformation
End If
Next
Set qdf = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: findmaketbl" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : ValInTbl
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Verifies if a value is found in a table
' 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:
' ~~~~~~~~~~~~~~~~
' sTable : Name of the table to search for the specified value
' sField : Name of the field in the specified table to search for the specified value
' sValue : Value to search for
'
' Usage:
'~~~~~~~
' ValInTbl("tbl_Units", "UnitNo", 54201)
' ValInTbl("tbl_Employees", "FirstName", "Daniel")
' ValInTbl("tbl_Followup", "AttendInitSession", True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2009-Mar-14 Initial Release
' 2 2010-Oct-13 Adjust the sSQL string based on the table field type
'---------------------------------------------------------------------------------------
Function ValInTbl(sTable As String, sField As String, sValue As String) As Boolean
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
Set db = CurrentDb()
Select Case db.TableDefs(sTable).Fields(sField).Type
Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbBoolean
sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]=" & sValue
Case dbText, dbMemo
sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]='" & sValue & "'"
Case dbDate
sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE [" & sField & "]=#" & sValue & "#"
End Select
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
ValInTbl = True
Else
ValInTbl = False
End If
Error_Handler_Exit:
On Error Resume Next
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: ValInTbl" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : WipeTables
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Empty all the tables in a db of all data (wipe the db clean (does not
' include system tables)
' Copyright : The following code may be used as you please, but may not be resold, as
' long as the header (Author, Website & Copyright) remains with the code.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function WipeTables() As String
On Error GoTo WipeTables_Error
Dim db As DAO.Database
Dim td As DAO.TableDefs
Set db = CurrentDb()
Set td = db.TableDefs
DoCmd.SetWarnings False 'Turn off confirmation prompt to user
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" Or Left(t.Name, 1) = "~" Then GoTo Continue
DoCmd.RunSQL ("DELETE [" & t.Name & "].* FROM [" & t.Name & "];")
Continue:
Next
DoCmd.SetWarnings True 'Turn back on confirmation prompt to user
Set td = Nothing
Set db = Nothing
WipeTables = True
If Err.Number = 0 Then Exit Function
WipeTables_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: WipeTables" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured! "
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : WipeTable
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Empty all the data of the specified table
' Copyright : The following code may be used as you please, but may not be resold, as
' long as the header (Author, Website & Copyright) remains with the code.
'
' Imput variables:
' ---------------
' strTblName: Name of the table to delete all the records in.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Feb Initial Release
'---------------------------------------------------------------------------------------
Function WipeTable(strTblName As String) As String
On Error GoTo WipeTable_Error
DoCmd.SetWarnings False 'Turn off confirmation prompt to user
DoCmd.RunSQL ("DELETE [" & strTblName & "].* FROM [" & strTblName & "];")
DoCmd.SetWarnings True 'Turn back on confirmation prompt to user
WipeTable = True
If Err.Number = 0 Then Exit Function
WipeTable_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: WipeTable" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured! "
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : listTables
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of all the tables in the database
' Copyright : The following code may be used as you please, but may not be resold, as
' long as the header (Author, Website & Copyright) remains with the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bShowSys - True/False whether or not to include system tables in the list
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-June-01 Initial Release
'---------------------------------------------------------------------------------------
Function listTables(bShowSys As Boolean) As String
On Error GoTo listTables_Error
Dim db As DAO.Database
Dim td As DAO.TableDefs
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td 'loop through all the fields of the tables
If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo Continue
Debug.Print t.Name
'CurrentDb().OpenRecordset ("DELETE * FROM " & t.Name)
Continue:
Next
Set td = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function
listTables_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: listTable" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | 1 Comment »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : listTableFields
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of all the fields (column names) of a give table
' Copyright : The following code may be used as you please, but may not be resold, as
' long as the header (Author, Website & Copyright) remains with the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTblName - Name of the table to list the fields of.
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2007-June-01 Initial Release
'---------------------------------------------------------------------------------------
Function listTableFields(strTblName As String) As String
On Error GoTo listTableFields_Error
Dim db As DAO.Database
Dim tdfld As DAO.TableDef
Dim fld As Field
Set db = CurrentDb()
Set tdfld = db.TableDefs(strTblName)
For Each fld In tdfld.Fields 'loop through all the fields of the tables
Debug.Print fld.Name
Next
Set tdfld = Nothing
Set db = Nothing
If Err.Number = 0 Then Exit Function
listTableFields_Error:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: listTableFields" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
June 10th, 2010
'---------------------------------------------------------------------------------------
' Procedure : DelTbl
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Delete the specified table
' 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:
' ~~~~~~~~~~~~~~~~
' strTable Name of the table to be deleted
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-May-24 Initial Release
'---------------------------------------------------------------------------------------
Function DelTbl(strTable As String) As Boolean
On Error GoTo Error_Handler
DoCmd.SetWarnings False 'Disable prompts to confirm deletion
DoCmd.DeleteObject acTable, strTable
DoCmd.SetWarnings True 'Reenable prompts
If Err.Number = 0 Then
DelTbl = True
Exit Function
End If
Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: DelTbl" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
DelTbl = False
Exit Function
End Function
MS Access Tables, MS Access VBA Programming | No Comments »
