Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Calculating Work Days from Date Range

Introduction.

How to find the number of workdays (excluding Saturdays and Sundays) from a date range in Microsoft Access?

The logic is simple, first find out how many whole weeks are there in the date range. Multiplying whole weeks by 5 gives the number of workdays from whole weeks.  From the remaining days find and exclude Saturdays and Sundays, if any. Add the remaining days to the total workdays.

DateDiff(), and DateAdd() functions are used for calculations, and the Format() function gets day-of-the-week in the three-character form to find Saturday and Sunday to exclude from the remaining days.

Find the VBA Code segments for the above steps below, and the full VBA Work_Days() Function Code at the end of this Article.

  1. Find the number of Whole Weeks between Begin-Date and End-Date:

    WholeWeeks = DateDiff("w", BeginDate, EndDate)

    The WholeWeeks * 5 (7 - Saturdays & Sundays) will give the number of working days in whole weeks. Now, all that remains to find is how many working days are left in the remaining days if any?

  2. Find the date after the whole weekdays:
    DateCnt = DateAdd("ww", WholeWeeks, BeginDate)
  3. Find the number of workdays in the remaining days by checking and excluding Saturdays and Sundays:
    Do While DateCnt <= EndDate
          If Format(DateCnt, "ddd") <> "Sun" And _
            Format(DateCnt, "ddd") <> "Sat" Then
             EndDays = EndDays + 1
          End If
          DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
        Loop
    
  4. Calculate the Total Workdays:

    Work_Days = Wholeweeks * 5 + EndDays

The Whole Calculation in Work_Days Function.

The full VBA Code of the Work_Days() Function is given below:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer
         
   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
'Number of whole weeks
   WholeWeeks = DateDiff("w", BegDate, EndDate)
'Next date after whole weeks of 7 days each
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0 'to count number of days except Saturday & Sunday

   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop
'Calculate total work days and return the result
   Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
                Work_Days = 0
    Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

The above VBA Code was taken from Microsoft Access Help Document.

Share:

Microsoft Access Form Move Size Action

Introduction.

We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you are able to work with other forms.

This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button - - > Access Options - - > Current Database - - > Document Window Options - - > Overlapping Windows to open the forms in this way, otherwise they will be opened in the Tabbed style in the Application Window.

The Pop-Up Forms.

Popup Forms will open on the exact location of the Application Window, from where you have saved it during design time. If you need more details on this topic visit this Article Link: Positioning popup Forms.

We can open a Microsoft Access Form and move it to a particular location of the Application Window is in the resized state, if necessary, with MoveSize Action on Docmd Object.

The MoveSize Action.

Here, we will learn the usage of MoveSize Action of the DoCmd Object in VBA.

View the YouTube Demo Video given below for reference. Select the 720p HD Option from the Settings for better quality viewing.

Demo Video

When the Supplier Code is selected on the Supplier List Form, the related Product List is displayed,  above the Supplier Form to the right of the main form. The width of the Product List Form is not changed, but the height of it changes, depending on the number of items on it.

We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try out, right away.

The list of Tables, Queries, and Forms required, to build this database, is given below.

    Tables:

  • Suppliers
  • Products
  • Queries:

  • Suppliers Extended
  • ProductListQ
  • SQL Code:

    SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
    FROM Products
    WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));
    

    Forms:

  • Supplier List
  • Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
  If frm.Name = ProductForm Then
    DoCmd.Close acForm, ProductForm
    Exit For
  End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub

Note: Don't forget to change the Overlapping Windows option in the Access Option settings mentioned in paragraph two from the top.

  1. Open Supplier List Form.
  2. Click on the Supplier ID Field (with the Company column heading) of any record, to open the  Supplier products List to display, in Resized Product List Form, and Moved to its specified location.

Download the Demo Database.


Download Demo MoveSize Demo.zip

Share:

PROPER Function of Excel in Microsoft Access

Introduction.

We have the UCase() Function in Access to convert all letters in a string of Text into upper-case letters(UPPER() Function in Excel).

? UCase("LeARn mS-access Tips aNd tRicKs")

Result: LEARN MS-ACCESS TIPS AND TRICKS

LCase() Function (LOWER() in Excel) of Access converts all the Text in a string into Lower-Case letters.

? LCase("LeARn mS-access Tips aNd tRicKs")

Result: learn ms-access tips and tricks

The Built-in Function PROPER() of Excel converts the first letter of each word in a string of text, into upper case and the rest of the text into Lower-case. 

I don’t say there is no Function in Access to do that job, but its name is not PROPER()

Microsoft Access Function StrConv() can do what PROPER() Function does in Excel.

Its usage is as given below:

? StrConv("LeARn mS-access Tips aNd tRicKs",3)

Result: Learn Ms-access Tips And Tricks

The STRCONV() Function.

The STRCONV() Function needs two parameters:

  1. The Text value to be converted
  2. The conversion type parameter - is an integer value.

    Here, parameter value 3 stands for Proper-case conversion.

I know what you are thinking; can we do other conversions also with this Function?

Yes, STRCONV(Text,1) for UCase() Function and STRCONV(Text,2) for LCase() Function

There are other usages for the STRCONV() function, with different parameters. If you are interested to explore further then type STRCONV in the search control of Microsoft Access Help Documents and get the details.

If you think that the name of the function is difficult to memorize, and the requirement of a second parameter is also too much work, then we can go by the Excel way and define a PROPER() Function in Access and use it wherever you want.

Here is the VBA Code:

Public Function PROPER(ByVal strText As String) As String
PROPER = StrConv(strText, 3)
End Function

Copy and Paste the above VBA Code into a Global VBA Module of your Project. If you have a Library Database then copy and paste the code into it, so that you don't have to duplicate it in all your other Projects.

I will not be surprised if you name the function as PCase() in line with the other text conversion functions LCase() and UCase().

  1. Roundup Function of Excel in MS-Access
  2. Proper Function of Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel-2
  11. Opening Excel Database Directly
  12. Create Excel Word File from Access
Share:

RUNSQL Action in MACRO and VBA

Introduction.

The  Microsoft Access beginners can get confused with the usage of RUNSQL Action in Macro and DoCmd.RUNSQL method of Visual Basic for Applications (VBA). Whether you run this Action in Macro or in VBA you must provide the SQL Statement of an Action-Query or Data-Definition Query Types only. If you are not sure which are these types of queries then refer to the following list:

Action Query Type.

Action Query Types
Query Type Statement
Append INSERT INTO
Delete DELETE
Make-Table SELECT ... INTO
Update UPDATE

Data Definition Queries.

Data-Definition Query Types
Query Type Statement
Create a table CREATE TABLE
Alter a table ALTER TABLE
Delete a table DROP TABLE
Create an Index CREATE INDEX
Delete an Index DROP INDEX

Using the SQL Statement of any other Query type in RUNSQL Action will end up in errors. In Macro the length of an SQL statement can be a maximum of 256 characters or less.

The DoCmd.RUNSQL method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as a parameter to this command. But, in VBA you can load the SQL Statement of a predefined query into a String Variable and use it as the parameter to the DoCmd.RUNSQL command. 

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RUNSQL Action of Macro Modifies/Deletes information of several records in one go. Before executing the Action Query Microsoft Access gives out a warning message, appropriate to the action specified, and waits for the User's responses. The User must respond to proceed/cancel the action specified.

Once the procedure is perfected, by test running the Action Query several times, we can ask Microsoft Access to turn OFF the Warning Messages during the execution of RUNSQL Action. After the Query running step, give control back to MS-Access by turning ON the Warning Message detection. From that point onwards MS-Access will be watching for unexpected errors in the System and warns the User, as and when it happens.

The SetWarnings Action in Macro and DoCmd.SetWarnings Method in VBA is used for this purpose when data processing for a report involves one or more action query steps placed in a Macro. Check the image of a Macro, given below in the design view, with the SetWarnings settings, before and after the RUNSQL Action in the Macro. The first Action in the Macro is Setwarnings with the Parameter value NO to turn off the Warning Messages when the RUNSQL Action executes in the next step. The SetWarnings Action with the Parameter value YES turns ON the Warning Messages in step three, so that MS-Access can take care of future unexpected System Errors of any kind, as and when it happens.


Example-2:

Create a Table with the Data-definition SQL

Warning: Double-check that you are not using any existing table name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open them in any of the following Views:

  • Design View
  • Datasheet View
  • Print Preview
  • Pivot Table
  • Pivot Chart
Share:

Hyperlink Opens Objects from Another Database

Introduction.

Hyperlinks in Microsoft Access are an excellent tool to open internal or external objects very easily, without writing macros or VBA Code. Hyperlink Address and Hyperlink SubAddress properties are available on Label controls, Command Buttons, and Image controls.

We have already looked into a few examples earlier, dealing with hyperlinks. Links to those articles are given at the end of this page for reference.

If you have not yet tried anything with these properties of the above controls, then let us go straight into a demo run and learn the usage of both these properties.

The Sample Trial Run.

  1. Create a new Blank Form.
  2. Insert a Label control on the Form.
  3. While the label control is still in the selected state display its Property Sheet (F4).

    Note: Hyperlink Address and Hyperlink SubAddress Properties are used for different purposes.

    • Hyperlink Address: for opening external objects, like Word Documents, Excel Worksheets, .PDF files, etc.
    • Hyperlink SubAddress: for opening internal objects, like Forms, Reports, Tables, Queries, and Macros.

    Let us open a Report from your database using the Hyperlink SubAddress property setting.

  4. Find the Hyperlink SubAddress property and write the following text into it:
    Report <Your Report Name>

    Replace <Your Report Name> text with one of your own report names (without the < > brackets). The correct format for writing a value into this property is <Object Type> i.e. Form, Report, Table, Query, or Macro followed by a space, followed by the actual object name.

  5. Change the Caption Property value of the Label to Open Report.
  6. Save the Form, with the Label control's Hyperlink SubAddress value set with the above changes.
  7. Open the Form in normal view and click on the label control.

You will see how your report is open in Print Preview.

Opening an Excel or Word File.

Now, let us try to open an MS-Word Document using the other property, the Hyperlink Address setting.

  1. Open your Form and create a second Label control on it.
  2. Display the property sheet of the label control.
  3. Change the Caption property value to Open Word Doc.
  4. Set the Hyperlink Address property value with the full pathname of a Word Document on your computer, like C:\My Documents\Letter.doc
  5. Save the Form with the changes.
  6. Open the Form in normal view and click on the second label to open the Document in MS Word.

Note: You can open Web pages by setting the Hyperlink Address property value to a web address: say https://www.msaccesstips.com

The HyperLink Base.

If your external files, which you plan to open in Access, are all in one location C:\My Documents\ then you don’t need to duplicate it in every control but to specify the Path (C:\My Documents\) at one place: Hyperlink Base and only needs to write the document name (like Letter.doc or any other file on that location) in the Hyperlink Address property.

Let us try that:

  1. Click on the Office Button at the left top corner and highlight the Manage option to display Database options (Access 2007). In Access 2003 and earlier versions, you will find this option in Tools Menu.

  2. Select Database Properties.

  3. Select the Summary Tab.
  4. Type C:\My Documents\ in the Hyperlink Base control (see the image given above)  and click OK to save it.
  5. Open your Form and remove the C:\My Documents\ text typed in for an earlier example, leaving the Letter.doc file name intact.
  6. Save the Form and click on the label control to open the Word Document.
  7. You may try to open any other document you have in that location, with only the file name change in the label.

Opening Objects from another Microsoft Access Database.

If you have followed me so far with the above sample exercises, then with a few changes we can do it.

  1. First, remove the text from the Hyperlink Base control ('C:\My Documents\') and leave the control empty.
  2. Create another Label control on your Form.
  3. Change the Caption Property value to Ext. Database.
  4. Set the Hyperlink Address property value to your external database path, like C:\mdbs\myDatabase.accdb.
  5. Set the Hyperlink SubAddress property value to Report myReport. Change the report name to match yours.
  6. Save the Form with the change.
  7. Open the Form in normal view and click on the label control.

Your external database will open first and will show your report in print preview.

Note: If your database is secured, then it will prompt for User ID and Password. You may try to open other objects: Form, Query, Macro & Table.

Share:

Microsoft Access Tutorial Database

Introduction.

During the installation of Microsoft Access 2003 or earlier versions, the NorthWind.mdb sample database also will be installed on your computer automatically. The sample database is very helpful for those who are starting with their self-learning practices of Microsoft Access.

Those who are migrating from Microsoft Excel Worksheets and tables created in line with what they have practiced in Worksheets find themselves faced with a lot of issues. In fact, Excel itself has database management support, even though they are not as flexible as Microsoft Access. Excel Users can browse for database basics in Excel Help documents to set up a database in Excel and learn to organize information by following the database management rules and retrieval methods, like sorting, searching, filtering, and so on. This helps to learn database management system rules, and it will help you to organize Excel worksheets or databases in such a way that they can be easily linked/imported into Microsoft Access if the need arises at a later stage.

Coming back to the sample database creation in Microsoft Access 2007, it doesn't create a sample database (Northwind) during the installation of MS Office on your computer. But, you can create one yourself from the sample Templates provided. It contains all the tutorial materials you need to learn Microsoft Access.

How to Create NorthWind.accdb database.

  1. Open Microsoft Access 2007

    You will find several Database Template Categories displayed on the left panel and the Featuring category, is selected by default. In the middle window, the template  Blank Database is displayed, and other templates are designed for specific purposes.

  2. On the left panel find the Sample Template category and select it.
  3. Click on the Northwind 2007 Template.

    The database will be saved in the active folder by default. You may change the folder by clicking on the folder icon to the right of the database file name.

  4. Click Create Button to create the sample database in your preferred folder specified.

Always use the sample database as a reference point for all your issues regarding Table designing, setting up Relationships, Queries, Forms, Reports, and Macros are designing. Do a few trial and error practices on the specific task you are trying to accomplish, with whatever knowledge and ideas you have. You will get a better insight into the task you are trying to design and you can find solutions in most cases yourself. If you are not able to do it then with the trial-and-error background you will be in a better position to explain your specific issues and seek help from other sources.

Access Users Forums.

You can search this Website for topics that you are interested in, or post your queries and get help from experts in Microsoft Access Users' Forums on the Internet. Links to some of the popular Forum Websites are given below:

  1. http://www.access-programmers.co.uk/forums/
  2. http://www.accessforums.net/#access-forums
  3. http://www.mrexcel.com/forum/microsoft-access/
Share:

Designing About Form for MS-Access Project

Introduction

This is all about Microsoft Access About Form.

Your Microsoft Access Application development process is complete and ready. At this stage, you may design a small Form, known as Microsoft Access About Form for your Project. About Form will display the customized logo and the name of the Project, Version Number, Copy Rights information, and other details, which you may wish to include on the form.

Sample About Form image of Windows Live writer Application is given below as an example:

Designing an Access About Form is easy. You need two Label controls, to display the Application Name & Current Version Number, and a Text-box to display the Copy Rights information. If you have designed a Logo, to give a unique identity to your Application, then that also can be included in the About Form.

Designing a Simple Access About Form.

Let us design such a small About Form, to know what it takes to create one with a simple Logo. Our sample About Form in design view is given below for reference.

I have designed a simple logo in MS Word, captured a screenshot of it, modified it in MS Paint, and saved it as a .bmp file.

  1. Open your Microsoft Access Application.
  2. Create a new Blank Form.
  3. Set the Width Property value of the Form to 3.93"
  4. Click on the Detail Section of the Form to select it.
  5. Set the Height property value to 1.53".
  6. Insert an Access Image control from the Tool-Box at the left side of the Form and select the Project Logo image from your computer. You may select the Picture Property of the Image control and click on the build (. . .) button to browse and select the logo image from your computer, if you wish to change the image later.

    The Image Properties.

  7. Change the Image property values as given below:
    • Picture Tiling: No
    • Size Mode: Zoom
    • Picture Alignment: Center
    • Picture type: Embedded
  8. Ms-Access Label & Text Controls.

  9. Insert a Label control to the right of the logo and top of the Form, and re-size it to make it wide enough to write the Application name in bold letters.
  10. Write the name of your Project in the Caption property, change the font size big enough to your liking, make it bold, and align the text to the center.
  11. Create another Label control below the first one, with the same width as the first label, and write the Version number of your Project, make it bold, and align the text to the center.
  12. Insert a Textbox below the earlier labels, and change its width as wide as the top labels.
  13. Copy and paste the following expression into the Control Source property of the textbox.
    ="Copyright " & Chr$(169) & Year(Date()) & " All Rights Reserved".
    
  14. Change the following Property values of Text-Box as given below:
    • Border Style: Transparent
    • Text Align: Center
    • Enabled: No
    • Locked: Yes
    • Tab Stop: No

    MS-Access Command Button.

  15. Create a Command Button below the textbox and position it to the center horizontally.
  16. Make the following changes to the Command Button:
    • Change the Name property value to cmdOK.
    • Change the Caption property value to OK.
    • Click on the Event Tab of the property sheet.
    • Select the On Click property and select [Event Procedure] from the drop-down list.
    • Click on the Build (...) button to open the VBA Module with the empty procedure start and end lines.
    • Copy the middle line of the Code given below and paste the line in the middle of the start and end lines of the VBA procedure (Private Sub cmdOK_Click() . . . End Sub). Or copy all three lines and paste them, overwriting the existing lines in the VBA Module.
    • Private Sub cmdOK_Click()
      DoCmd.Close
      End Sub
      

      When the Access User clicks on the Command Button the above Code will run and the About Form will be closed.

  17. Save the Form and rename it as About or frmAbout.

  18. Open the About Form in normal View and see how it looks on the Access Application Window.

    The Property Value Changes.

    As you can see the Access About Form needs some changes to make its appearance like a real About Form. Let us do that to give it the final touches.

  19. Make the following changes in the Form's Property Values as shown below:
    • Caption: About <your Project name here>
    • Pop Up: Yes
    • Modal: Yes
    • Default View: Form View
    • Allow Form View: Yes
    • Auto Center: Yes
    • Auto Resize: Yes
    • Fit to Screen: No
    • Border Style: Dialog
    • Record Selectors: No
    • Navigation Buttons: No
    • Dividing Lines: No
    • Scroll Bars: Neither
    • Control Box: Yes
    • Close Button: No
    • Min Max Button: None
  20. Save the Form after the above changes.

    View The Application About Form in Normal View.

  21. Open the About form in Normal View.

    A sample Image of the completed About Form in normal view is given below.

You must add an Option in the Customized Menu of your Project to enable the User to open the Access Application About Form if he/she wishes to do so. Alternatively, you may add a Command Button on the Main Form of your Project to do that.

Share:

Appending Data from Excel to Access

Introduction

Last week we tried out an interesting method of writing a range of excel data range directly into a Microsoft Access Table. Each row of cells is transferred into the table as a single record. If you have not come across that article then you may find it here.

This is an equally interesting method.  The selected Worksheet contents are appended to the Access Table with its specified columns in the SQL.

A sample image of the Worksheet data is given below:


The VBA Code

Private Sub CommandButton1_Click()
On Error GoTo CommandButton1_Click_Error
'Create Database connection Object
Set cn = CreateObject("ADODB.Connection")
'Access Database must be in the same location of the Worksheet 
dbpath = Application.ActiveWorkbook.Path & "\Database4XL.accdb"
'Get Workbook Full Pathname
dbWb = Application.ActiveWorkbook.FullName
'Get Active worksheet name
dbWs = Application.ActiveSheet.Name
'Create Data Target Connection string to open a session for data transfer
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
'Datasheet name to add at the end of the Workbook Name, to complete the
'FROM clause of the SQL String.
dsh = "[" & dbWs & "$]"
'Open session
cn.Open scn

'Append Query SQL String
ssql = "INSERT INTO Table1 ([Desc], [Qrtr1], [Qrtr2], [Qrtr3], [Qrtr4]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

'Run SQL
cn.Execute ssql

MsgBox "Data Added to " & dbpath & " Successfully."

CommandButton1_Click_Exit:
Exit Sub

CommandButton1_Click_Error:
MsgBox Err & " : " & Err.Description, , "CommandButton1_Click()"
Resume CommandButton1_Click_Exit

End Sub

Courtesy:
The above VBA code was taken from a Forum Post on www.mrexcel.com/Forum and modified to run on the same sample data presented in the earlier article published last week.

The Code on the Worksheet VBA Module is run from the Command Button1 Click Event Procedure.

  1. Roundup Function of Excel in MS-Access
  2. Proper Function of Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel-Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel- 2
  11. Opening Excel Database Directly
  12. Create Excel Word File from Access
Share:

Writing Excel Data directly into Access Table

Introduction.

We can Import data from external data sources; from another Access database, dBase, FoxPro, Excel, and so on. We can Export data from Access into these Applications as well.

Following is a list of topics I have published earlier; either on importing, exporting, or working with external data sources from Microsoft Access:

Today, we will explore how to add a range of Excel cell data directly into Access Table, by running the VBA Code from within Excel.

The Algorithm of the program is as given below:

  1. Create an Access Application Object and open it.

  2. Open the target database within the Access Application.

  3. Keep the Access Application window hidden.

  4. Open the target table from the Database.

  5. Take the count of Rows from one of the Excel data columns.

  6. Open a repeating loop to write the excel data one row at a time, from the second row onwards.

  7. Repeat the writing action till all the rows are transferred to the Access Table.

  8. Close the table, and database, and quit the MS-Access Application.

The Excel VBA Code is Run by clicking a Command Button on the Excel Sheet. A sample image of the Excel Sheet with data and Command Button is given below:

Target Access Table Structure image is given below:


The Excel VBA Code

Sub Button1_Click()
    Dim objAcc As Object
    Dim recSet As Object
    Dim DataRow As Long, EndRow As Long
    
    On Error GoTo Button1_Click_Err
    
    'Create Access Application Object
    Set objAcc = CreateObject("Access.Application")
    'Open Database in Microsoft Access window
    objAcc.OpenCurrentDatabase "F:\mdbs\Database4XL.accdb", True
    'Keep Access application window hidden
    objAcc.Visible = False
    
    'Open Access Table to add records from Excel
    Set recSet = objAcc.CurrentDb.OpenRecordset("Table1")
    'Take actual row counts of data for transfer
    EndRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    With recSet
      For DataRow = 2 To EndRow
        .AddNew
        ![Desc] = Sheet1.Range("A" & DataRow).Value
        ![Qrtr1] = Sheet1.Cells.Range("B" & DataRow).Value
        ![Qrtr2] = Sheet1.Cells.Range("C" & DataRow).Value
        ![Qrtr3] = Sheet1.Cells.Range("D" & DataRow).Value
        ![Qrtr4] = Sheet1.Cells.Range("E" & DataRow).Value
        .Update
      Next
    End With
    recSet.Close
    objAcc.Quit
    Set objAcc = Nothing

Button1_Click_Exit:
Exit Sub

Button1_Click_Err:
MsgBox Err & " : " & Err.Description, , "Button1_Click()"
Resume Button1_Click_Exit
    
End Sub
Courtesy:
The non-functional raw VBA Code presented by a User at www.mrexcel.com/forum/microsoft-access, was modified by me to make it functional and was originally submitted there.

The first field of the table is an ID field with the data type AutoNumber. The ID field value is automatically generated when data is inserted into the other fields.

  1. Roundup Function of Excel in MS-Access
  2. Proper Function of Excel in Microsoft Access
  3. Appending Data from Excel to Access
  4. Writing Excel Data Directly into Access
  5. Printing MS-Access Report from Excel
  6. Copy Paste Data From Excel to Access2007
  7. Microsoft Excel Power in MS-Access
  8. Rounding Function MROUND of Excel
  9. MS-Access Live Data in Excel
  10. Access Live Data in Excel- 2
  11. Opening Excel Database Directly
  12. Create Excel Word File from Access

Share:

Legacy Custom Menus Toolbars in Access2007

Introduction.

Custom Menu of Access 2003 in Access 2007.

You have spent a lot of time designing Custom Menus and Toolbars in your MDB Database. You have upgraded to Microsoft Access 2007 Version. You opened your MDB database in the new version of MS-Access. The customized menus and toolbars you have designed for your application are disappeared. 

The Add-In Menu.

Your Custom Menus and Tool Bars are not going anywhere, all you have to do is to find them.

Custom Menus and Toolbars are safely kept inside the Add-in Menu of Microsoft Access2007. Click on the Add-in Menu to find all of them there.  Check the sample image given below for reference.

Have you noticed the group name Custom Toolbars appearing at the bottom of the Add-in Menu?

But, Access 2007 Menu Bars also appear on the top.  You want to turn off the default Access2007 Menus and Toolbars from the top and replace it with your Custom Menus and Toolbars.


Access Option Changes.

Do the following to get that done:

  1. Click on the Office Button (Top left corner).
  2. Select Access Options.
  3. Select the Current Database.
  4. Move the scroll bar down to find the Ribbon and Toolbar Options as shown in the image given above.
  5. Select your Custom Menu Bar name from the drop-down list, replacing the text (Default).
  6. Select your Shortcut Menu Bar name from the drop-down list, replacing the text (Default).
  7. Remove the check-marks from the next three options:
    • Allow Full Menus.
    • Allow Default Shortcut Menu.
    • Allow Built-in Toolbars.
  8. Click OK to save changes and come out of Access Options.

  9. One more step to go:

  10. Close the database and open it again to register the changes.

Now, that the Menu Bars and Tool Bars are in complete control of your Application they will look like the image given above.

You want to restore Access 2007 Menus and Toolbars, then open the MDB file by holding the Shift-Key, to prevent Auto-Macro from running and to get control of the database window.  Go through Steps 1 to Step 9 and reverse what you did earlier.

Share:

Sub-Report Summary Value in Main Report Calculations

Introduction.

How to bring the sub-report summary value, to the main report and use it in calculations?

But, first, we need a ready-made report for our project.

Sample database download links are given below. You may download the database version you need.  The demo database is in the Microsoft Access2007 version and can be used in later versions too.

First, take a look at the following images:

  1. Print Preview of the finished report.
  2. Original Report Preview. 
  3. Changes were made to the Report in Design View, to get the result shown in the first image above.

    Download Links.

  4. Sample Database Download Links, for Microsoft Access 2007 and 2003 Versions, are given below.


  5. Download the suitable sample database.

    In short, our task is to add the sub-report category total to the Footer Section of the Sales by Category sub-Report. Get that summary value of each category to the main report header.  Calculate the percentage of each category of product sales value on Grand-Total Sales Value (or Percentage = Category Sales Value / Total Sales value of all Categories * 100).  We can do this by adding a few text boxes on both reports and writing a few expressions in them.

    The Sub-Report Changes.

    Let us start with the Sales by Category, Sub-report first.

  1. Open the downloaded database.

  2. Open Sales by Category, Sub-report in design view.

  3. Right-click on the Report Footer bar and select Properties to display the Property Sheet.

  4. Select the Height Property and change the property value to 0.33” or 0.838 cm.

  5. Select the Text Tool from the Toolbar above and draw a text box on the Report Footer area below the Product Sale column.

  6. Write the expression =Sum([ProductSales]) in the Control Source property of the text box. Change the Name property value to SubTotal.

  7. Modify the Caption property value of the child label to read as Sub-Total.

  8. Save and close the Sales by Category Subreport.

    The Main-Report Changes.

  9. Open Sales by Category main report in Design View.

  10. Create a Text Box, on the Header Section of the Report, to the right of the report heading.

  11. While the text box is in the selected state, display the property sheet (F4).

  12. Write the expression =Sum([ProductSales]) in the Control Source property. Change the Name property value to TotalSales. Change the Caption property value, of the child label to Total Sales.

    Note: The above expression will calculate the Total Product Sales value of all Categories on the main report. The same expression in the sub-report will calculate the sales value of the current category of Product (example: Beverages). Now we have the product sales value of a particular category as SubTotal on the Sub-Report and the Total Sales values of all categories of products as TotalSales. In the next step, we can find the percentage of a particular category of product sales on the total sales value of all categories. The simple formula can be Percentage=SubTotal/TotalSales*100.  But, SubTotal is on the Sub-Report and we cannot directly address it as SubTotal, on its parent report or Main report (without specifying where it can be located in the main report having one or more child reports or sub-reports).

  13. Create a text box below the Category Header bar to the right of the Category Name heading, move the text box to the right, and position it under the Total Sales calculation control on the Report Header.

  14. Right-click on the Control Source Property of the text box and select the Build option, from the displayed list, to open the expression builder control.

    • Click on the = symbol to insert it into the expression editor window.

    • Double-click on the + symbol on the left side of the Reports option to expand and show other options.

    • Double-click on Loaded Reports.

    • Double-click on the Sales by Category main report to display the Sub-Report's name.

    • Click on Sales by Category Subreport to display its control names in the next column.

    • Find the Subtotal control in the list and double-click on it.

      The Subtotal control reference of the sub-report: [Sales by Category Subreport].[Report]![SubTotal], is inserted into the expression builder. If we know how to write this reference correctly into the Control Source property manually then we can do that without going through this route. But, once you go through this procedure a few times and study Microsoft Access’s addressing method you will know how to write them on Reports or Forms manually.

    • Click OK to come back to the Control Source property of the text box with the reference of the subtotal control and to write the rest of the expression to calculate the percentage.

    • Type /[TotalSales] at the end of the subtotal reference. Have you noticed the slash on the left side of the expression snippet?

    • Select Percent from the drop-down list of the Format property. With these settings, we don’t have to write *100 part in the expression.

    • Change the Caption of the child label to Category %. Change the Font-size of the Text box and child label of 12 points and make Font-weight Bold.


    Report Sample Print Preview

    Our Report is almost finished, but we need a little more change and that comes next after we preview the progress of our work so far

  15. Save the changes we have made in the Report and open it in Print Preview, to see the result of the changes we have done on the report so far.

    The Report should look like the image given below.

  16. Move the Report to the next page.

    The Total Sales value of the Report Header Section is not appearing on the second page.

    This will not appear on the report from the second page onwards along with the Report Heading. Report Header section values will only print on the first page of the report and Report Footer will print only on the last page of the report.  But, we want the Heading and the expression written on the Header Section to appear on every page of our Report. Whatever we insert on Page Header Section will appear on every page. We can transfer the Report Heading to Page Header Section but not the Calculation Text box. The SUM(), Count(), and other aggregate functions will not work on the Page Header Section, but we want the value from the Total Sales control to appear in the Page Header Section as well. We will make the following changes to keep the report heading and Total Sales values on all pages of the report.

    The Final Changes.

    • Open the report in design view.

    • Drag the Category Name header bar down to get enough space to the Page Header Section, to cut and paste the Report Heading there.

    • The highlight report heading and the report date controls (leave alone the Total Sales textbox), cut, and paste into the Page Header Section.

    • Select the Total Sales text box, copy and paste it on the Page Header Section, and move the position below the text control on the Report Header Section.

    • Write the expression = [TotalSales] (the Name of the total sales calculation text box on the header section) in the Control Source property (overwriting the existing expression) of the copied text box.

      This will display the value of the Header Section text box, where the Total Sales value is calculated.  

    • Select the Header Section text control along with the child label and set their Visible Property value to No, to keep it hidden when the report is Previewed or printed.

    • Save the report with the changes.

      Print Preview the Report.

    • Open the Sales by Category report in Print Preview, move the pages forward, and check the headings and category percentage values.

  17. I am sure next time you want to do something like this you can do it in a few minutes.

Share:

Editing Hyperlink Address Manually

Introduction.

Hyperlinks are everywhere on the Internet. We have them in Microsoft Access too. It can be used to open Objects (Tables, Forms, Reports, or Queries) by clicking on a  hyperlink, without writing a single line of VBA code or Macro. Hyperlinks can be created in Tables or Forms. You can create menus to open Forms, Reports, etc. You can open external files, like Word, Worksheets, .PDF, text, and others with the use of Hyperlinks in Microsoft Access.

Hyperlinks in Access can be created or edited manually by entering their link segments in proper order, in the table field, or on Form controls. Normally, editing can be done through a dialog box, displayed when right-clicking on the hyperlink control.

4 Segments of Hyperlink-Address in MS-Access.

The Hyperlink address line is divided into four segments:

  1. Display Text: the text that appears in a field or control, indicating the hidden link's purpose
  2. Address: external file's path-name
  3. Sub-Address: Internal Object Name, to open Form, Report, etc.
  4. Screen-tip: the text displays as a tooltip.

Hyperlink syntax is as follows:

Display Text#Address#Sub-Address#Tool-tip text

The Hyperlink Data type field can store a maximum of 2048 characters.

You can find more details and usage of Hyperlinks in the earlier Post: Open Forms with Hyperlinks in Listbox.

Editing Hyperlink Manually.

Let us get into the manual editing of the Hyperlink topic:

We will create a sample table with a Hyperlink field to try out the manual editing exercise.

  1. Create a table with a single field and data type Hyperlink.
  2. Save the table with a name.
  3. Open the table in datasheet view.

    If you know how to enter Hyperlink data into the field manually, you may do so, by following the hyperlink syntax shown below.

    Example: My Excel File#C:\\\My Documents\Workbook1.xls##Click

  4. After entering the sample hyperlink in the field, open the table in the datasheet view.

    You will see that the hyperlink field is active and the only text showing is the Display Text (My Excel File) part of the hyperlink information.

  5. Move the mouse pointer over the field, it changes the Mouse-Pointer to a hyperlink pointer (a Hand with an index finger).

    If you click on the field to edit the hyperlink information, it will only open the document specified in the second segment.

    If you right-click on the field the shortcut menu, which carries the hyperlink editing options in a dialog box is displayed.  You may key-in values at appropriate controls to change the hyperlink values.  You cannot type values directly into the hyperlink field.

    Simple Trick to Edit Hyperlink Manually.

    But, we can manually edit the field with a simple trick.

  6. Close the Table for now.
    • Click on Office Button.
    • Select Access Options.
    • Select Advanced.
    • Find the Behavior Entering Field options.
    • Select Go to end of the field option.
    • Click OK to close the Access Options control.
  7. Open the Table after completing the above steps.

You can see that the insertion point is positioned at the end of the field value, exposing the hyperlink information. Move the insertion point back to the location where you need to edit the field.

If you have more than one record to edit then change them and reset the Behavior Entering Field option to Select Entire Field.

TIP:

If you would like to show the full Pathname of the File as display text, then copy the second segment hyperlink value and paste it into the display text position also.


Stand-alone Label Controls Have Hyperlink Properties.

The Label control on Form can be used as a hyperlink control.  The label control has Hyperlink Address & Hyperlink Sub-Address properties, use the Caption property to set the Display Text value of the hyperlink. Set the Special Effect property value to Raised, to make it look like a Command Button.

Share:

Alphabetized Customer List

Introduction.

If you are new to Microsoft Access Report designing, then this simple Alphabetized Customer List preparation Tutorial will give you enough insight into what it takes to design a simple report.

We will need the following few steps to complete our task:

  1. Prepare Customers' source data in a SELECT Query for the report.

  2. Open a new report in Design View.

  3. Insert the SELECT Query name into the Record Source Property of the Report.

  4. Use the Data Grouping and Sorting option of the Report to organize and display A, B, C, etc., in the Group Header.

  5. Design the Report.

  6. Preview the Report.

A Sample Report.

Sample alphabetized list of customers Report Preview is given below:


Designing A Report.

Get Some Sample Data.

But, first, we need some ready-made sample data for our Report

Let us start by importing Customers Table from the Northwind sample database.

  1. Click on External Data Menu.

  2. Click on the Access Tool button to display the Import control dialog box, to specify the Source and destination of data.

  3. Click on the Browse... Button to locate the Northwind sample database, Select the file and click Open.

    The selected file pathname is inserted into the File Name control in the dialog box.

    The first option is already selected as default, to import one or more required Access Objects from the selected Access database.

  4. Click OK to open the selected Access Database and expose its Tables, Queries, Reports, etc.

  5. Click on the Tables tab, select the Customers table, and click OK to import the selected table.

    The next step is to create a SELECT Query using the Customers table as Source.

  6. Click on the Create menu and select Query Design from the Other group.

  7. Click the Close button to close the Show Table dialog box, without selecting any object from the displayed list.

  8. You will find the SQL View option on the extreme left of the Toolbar and select it to display the Query's SQL editing window.

    You will find the SQL statement SELECT; in the window.

  9. Copy the SELECT Query Code given below and paste it into the SQL window, overwriting the existing SELECT; statement.

    SELECT Left([First Name],1) AS Alpha, [First Name] & " " & [Last Name] AS CName
    FROM Customers
    ORDER BY Left([First Name],1), [First Name] & " " & [Last Name];
    

    As you can see in the above SQL string we have only two columns of data. The first column has only one character in all rows, the leftmost character taken from the First Name of the customer. The first column name of the query is given as Alpha.

    We have used MS-Access built-in string function Left() for this purpose. There are other useful functions in this category, like Right(), Mid(), and others.

    The second column of data is the name of the Customer, with the First Name, and Last Name joined together, separated with a space. The second column's name is given as CName. When you use an expression to create the query column data it is better to give a simple name to the column so that it is easy to memorize and use on the Report or Form.  If you don’t give a suitable name, then MS-Access will give names like Expr1, and Expr2 (Expression 1, Expression 2, and so on) by default.

    In the ORDER BY clause of the query, both the columns are sorted in ascending (A-Z) Order, first on the Alpha column, and then on the CName column.

  10. Save the Query with the name Customer ListQ.

  11. Open the Customer ListQ in the datasheet view and check the data.

    A sample image of what we are going to create is given below for reference:

The Design Task.

Let us design the Report.

  1. Select Report Design from the Create menu.

    An empty Report is open in Design View, with its Property Sheet. The first priority is to define our Customer ListQ Query as the Record Source of our report. If the property sheet is not displayed then click on the Property Sheet toolbar button to display it.

  2. Select the Data Tab on the Property Sheet.

  3. Click on the Record Source property and click on the drop-down list at the right end of the property.

  4. Find Customer ListQ Query (use the slider, if necessary) and select it from the drop-down list, to insert it into the Record Source property.

  5. Click on Group & Sort Toolbar button from Group & Totals Group under Design Menu, if it is not already in the selected state, to display the Group and Sort controls under the Footer of the Report.

  6. Click on the Add a Group control displayed in the Group, Sort, and Total shown below the empty report.

  7. Click on the Alpha column name displayed from the Query columns list.

    You can see the Alpha Group Header is now appearing between the Page Header and Detail Sections of the empty report.

    We must sort the customer names appearing under a particular character group (say A, B, C group) so that they will appear in proper order as well. 

    Note: We have sorted the data in the Query by defining the ORDER BY clause, but we must define Grouping and Sorting on the Report also to make them appear in proper order on the Report as well.

  8. Click on Add a Sort control and select CName from the list.

    Now, let us create the Heading of the Report, Group Heading (A, B, C, and so on), and customer names list to appear under each group.

  9. Click on the Label control to select it and draw a rectangle, wide enough to write the heading text "CUSTOMER LIST", select Bold and Italic formatting styles, and change the font size to 16.

  10. Select the TextBox control and draw a text box on the Alpha Header Section of the report.

  11. Click on the Data Tab on the Property Sheet and select Alpha from the Control Source drop-down list. Change the font style to Bold and character size to 16.

  12. Select the child label of the text box and delete it.

  13. Create another text box in the Detail Section of the Report, below the Alpha Header control.

  14. Select the CName column name from the drop-down list in the Control Source property under the Data Tab on the property sheet of the text control.

  15. Reduce the Detail Section height by dragging the Page-Footer section bar up to touch the text box's edge.

  16. Save the report with the name: Customer List.

    Print Preview the Report.

  17. Open the Customer List report in print preview and see how it looks.

If the Heading, Group heading, and customer list are not properly aligned to the left in your report, as shown in the first image on the top, try to align all the controls to the left.

Share:

Archiving Master Data

Introduction.

Over a period of time, the Master Table of our database can grow into large volumes, having thousands or millions of records. The maximum logical size of a Ms-Access Database is only 2GB.  Everyday Repairing and Compacting may not improve the performance of the database. The processing time of reports or running time of Queries will keep on increasing.

The majority of old records in the main table may not involve in daily or monthly querying or report preparation processes.  But, old records may involve in year-end processes, like history analysis, next year's business target setting, and so on.

Normally, old records of the master tables are set with a flag indicating as archived and retained in the master table itself.  Active records are filtered from the master table for reports or querying to monitor the current activities of business functions.  The filtering and sorting process may take more time, every time, due to the large volume of data in the main table.

Maintaining Data of Earlier Period.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance.  The older data is in the archive.accdb (a name for reference) can be easily made available to the Front-End database for year-end processes.

You don't need to link and keep the archived table in the Front-End database permanently. You can combine the archived data with the active master table in a Union Query, by using direct references to the archived table, rather than keeping it linked to the FE database.  The Union Query having combined data of both the tables can be used as a source for year-end processes.  You can find more details on Union Queries here.

But first, let us see, how we can safely transfer the old data from the master table (tblMaster)  to a new archive database: Archive. Here, we assume that we have a Front-End, Back-End configuration of databases.

The Prelude of our Action Plan.

We need the following steps to complete the process:

  1. Create a new Access Database: Archive in location C:\mdbs or in a location of your preference.

    Note: If you are connected to a common Network Server then create a folder there/use an existing folder, where you have access rights, and save the archive.accdb there. The database will be backed-up regularly, by the Network Administrators and will be safe for recovery if the need arises. Click on the link to learn more about Database Daily Backup.

  2. Close archive.accdb database.

  3. Open the Back-End Database.

  4. Transfer only the Structure of tblMaster into the Achive.accdb

  5. Create a SELECT Query on tblMaster with an appropriate criterion to select the old data.

  6. Open the Select Query in Datasheet View, take the total record count, and note it down.

  7. Change the SELECT query into an Append Query.

  8. Save and run the Append Query to transfer the selected data directly to the archive.accdb database into table tblMaster.

  9. Close BE database and open the Archive database.

  10. Open tblMaster in the archive database and check the count of records that matches with the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in tblMaster of the Archive.

  11. Close the Archive and open the BE database.

  12. Create a Delete Query that uses tblMaster with the same criteria you have used in the Append Query.

  13. Open the Delete Query in Datasheet View and take the count of records and ensure that it matches with the count you have taken earlier.

  14. Run the Delete Query to remove the records from the tblMaster table from the BE database.

  15. Run Repair and Compact option to reduce the database size.

  16. Close BE database and open the FE database.

    Linking Old Data to Front-End (FE) Database.

  17. Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.

Let us Execute the above-defined Plan.

Step-1 to step-3 are self-explanatory.

In step 4: Right-click on tblMaster to display a shortcut menu.

  • Highlight the Export option and select Access Database from the displayed menu.

  • Click on the Browse button and select the archive.accdb database and click Save to come back to the Export dialog box.

  • Click OK to open up the Export Options dialog box.

  • Select Definition Only option to transfer the tblMaster Table Structure into Archive.

Step-5: The select Query Code:

SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<Dateserial(1981,1,1)));

The above criteria will select all the records of 1980 and the earlier period.

Step 6 is self-explanatory.

Step 7: Open the Query, created in step 5, in the design view.

  • Click on the Append Query button on the Toolbar.

  • Select tblMaster from the Table Name drop-down control in the dialog box.

  • Select Another Database Radio Button.

  • Click on Browse… Command Button to find the archive.accdb database, select it and click OK to come back to the dialog box in Query Design View.

  • Click OK on the dialog box to change the Select Query to Append Query. The Sample append query SQL is given below for reference.

    INSERT INTO tblMaster IN 'C:\mdbs\archive.accdb'
    SELECT tblMaster.*
    FROM tblMaster
    WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));
    
  • Open Append Query in Datasheet View and check the record count with the count you have taken earlier.

  • If both counts match, then save the Append Query.

Step-8: Right-Click on the Append Query and select Open to Run the Query to extract selected data from tblMaster table and to append them in the archive.accdb tblMaster table.

  • Click Yes Command Button, on the warning message control, to reconfirm the action.

Step-9 to Step-11: Self-explanatory.

Step-12:  Sample Delete Query SQL is given below:

DELETE tblMaster.*, tblMaster.mstDate
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Step-13 to Step-16: Self-explanatory.

Step-17: Sample Union Query SQL is given below:

SELECT tblMaster.* 
FROM tblMaster
UNION ALL SELECT tblMaster.*
FROM tblMaster in 'C:\mdbs\archive.accdb';

Save the Union Query with the name tblMasterQ. Use tblMasterQ as Source Data for all year-end processes or wherever you need all the data together.  For other purposes, your database will run faster.

You can continue to transfer data when they become old, into the Archive and delete them from the BE database.  No other change is required anywhere.

Technorati Tags:
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code