Tag Archives: MS Access Tables

MS Access – VBA – Delete all Linked Tables

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-06-10              Initial Release
' 2         2020-04-09              Code change based on Ben Sacherich's comment
'---------------------------------------------------------------------------------------
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
            db.TableDefs.Delete tdf.Name 'Per Ben Sacherich's recommendation
        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 – Mandatory Fields, Mandatory Entries

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 Sub

As 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 – VBA – Append/Insert Record into Another External Database

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 – VBA – Relink Back-End Tables

In any well developed MS Access database, it becomes necessary to automate the relinking of the back-end database tables. If nothing more than to simplify switching between Development and Production environments.

There are any number of existing resources that you can very rapidly implement.

Below are a few useful links with VBA code samples of how to relink linked-tables through automation:

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:

Reconnect Utility Database

Another good one is the J Street Relinker:

J Street Access Relinker

Hopefully these links will save you some time searching the net.

MS Access – Listing of Database Objects (Tables, Queries, Forms, Reports, …)

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 common values of interest 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

Continue reading →

MS Access VBA – Wipe all the Tables in the Database

The following function will loop through all the tables within the current database, excluding system tables, and delete all their records.

As always, such actions cannot be undone, so be sure to make a backup copy of your database prior to executing the function just in case.

'---------------------------------------------------------------------------------------
' 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 Occurred! "
    Exit Function
End Function

MS Access VBA – Delete All Table Records

Ever needed to wipe a table, delete all the records within a specified table? The following procedure will do exactly that.

'---------------------------------------------------------------------------------------
' Procedure : TableWipe
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Delete all the records form a tble
' 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:
' ~~~~~~~~~~~~~~~~
' sTbl      : Name of the table to empty/wipe
'
' Usage:
' ~~~~~~
' Call TableWipe("tbl_Contacts") 'Deletes all the records from the tbl_Contacts table
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-09-02              Initial Release
'---------------------------------------------------------------------------------------
Public Sub TableWipe(sTbl As String)
On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sSQL                  As String
    
    Set db = CurrentDb
    sSQL = "DELETE FROM [" & sTbl & "];"
    db.Execute sSQL
    
Error_Handler_Exit:
    On Error Resume Next
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: TableWipe" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

MS Access VBA – List Table Fields

Below are 2 procedures that enable you to enumerate a list of field names for a given table.

Approach 1 – using the DAO TableDefs Collection

'---------------------------------------------------------------------------------------
' Procedure : listTableFields
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a listing of all the fields (column names) of a given 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 Occurred!"
    Exit Function
End Function

Approach 2 – using the OpenRecordset Method

'---------------------------------------------------------------------------------------
' Procedure : listTblFlds
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a listing of all the fields (column names) of a given 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:
' ~~~~~~~~~~~~~~~~
' sTblName  : Name of the table to list the fields of.
'
' Usage:
' ~~~~~~
' Call listTblFlds("YourTableName")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-08-26              Initial Release
'---------------------------------------------------------------------------------------
Function listTblFlds(sTblName As String) As String
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim fld                   As Field

    Set db = CurrentDb()
    sSQL = "SELECT *" & _
           " FROM [" & sTblName & "]" & _
           " WHERE (False);"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    For Each fld In rs.Fields    'loop through all the fields of the tables
        Debug.Print fld.Name
    Next

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: listTblFlds" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

MS Access VBA – Delete a Table

There are 3 approaches that come to mind for deleting a table in Microsoft Access:

DoCmd DeleteObject Method

DoCmd.DeleteObject acTable, "YourTableName"

SQL DROP Statement

Dim db As DAO.Database
Set db = CurrentDb
db.Execute "DROP TABLE YourTableName;", dbFailOnError

TableDefs Delete Method

Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "YourTableName"
RefreshDatabaseWindow

RefreshDatabaseWindow does as its name implies and will update the navigation pane’s table listing to reflect the deletion. If you omit this command the table will remain in the listing until you reopen that database.

Relationships!

Do keep in mind you can’t delete tables involved in relationships. You must first delete their relationships prior to attempting to delete the table itself. For that be sure to check out

MS Access – VBA – Delete Table Relationships

Sometimes, say when you wish to delete a table, you may need to delete the relationships associated with a given table. Now I’ve seen code to delete all the database relationships, but never for just a specific table. Yesterday, while trying to help in a forum I created the following procedure that does that; delete…

Continue reading →