Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Writing Excel Data directly into Access Table

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 cells data directly into Access Table, by running 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 column.
  6. Open a repeating loop to write the excel data one row at a time, from second row onwards.
  7. Repeat the writing action till all the rows are transferred to the Access Table.
  8. Close the table, database and quit MS-Access Application.

The Excel VBA Code is Run by clicking a Command Button on Excel Sheet. 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 is given below:

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
        ![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
    End With
    Set objAcc = Nothing

Exit Sub

MsgBox Err & " : " & Err.Description, , "Button1_Click()"
Resume Button1_Click_Exit
End Sub

Courtesy: The non-functional raw VBA Code was presented by a User in www.mrexcel.com/forum/microsoft-access, modified by me to make it functional and was originally submitted there.

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


Legacy Custom Menus Toolbars in Access2007

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. 

Your Custom Menus and Tool Bars are not gone 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 appearing 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.

Do the following to get that done:

  1. Click on the Office Button (Top left corner).
  2. Select Access Options.
  3. Select Current Database.
  4. Move the scrollbar down to find the Ribbon and Toolbar Options as shown on 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, your 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 .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.


Sub-Report Summary Value in Main Report Calculations

Using Sub-Report Summary Value in Main Report Calculations

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

But, first we need a ready made report for our project, without the calculations part on it.

Sample database download links are given below. You may download the database version you need.  Microsoft Access2007 version database can be used for later versions of Access too.

First, take a look at the following images:

  1. Print Preview of the finished report.
  2. Original Report Preview. 
  3. Changes made on the Report in Design View, to get the result shown on the first image above..
  4. Sample Database Download Links, for Microsoft Access 2003 and 2007 Versions, are given below.
  5. Download the sample database.

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

    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 ProductSale 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 child label to read as Sub-Total.
  8. Save and close the Sales by Category Subreport.
  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 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 on the sub-report will calculate sales value of 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’s sales on total sales 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 it's 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 below the Total Sales calculation control on the Report Header.
  14. Right-click on the Control Source Property of the text box and select 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 at the left side of the Reports option to expand and show other options.
    • Double-click on Loaded Reports.
    • Double-click on Sales by Category main report to display the Sub-Report's name.
    • Click on Sales by Category Subreport to display it's 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 in to 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] to the end of the subtotal reference. Have you noticed the slash at the left side of the expression snippet?
    • Select Percent from the drop-down list of the Format property. With this 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 to 12 points and make Font-weight Bold.

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

  15. Save the changes we have made on 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 on the Report Header Section is not appearing in the second page.

    This will not appear on the report from 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 on the Page Header Section as well. We will do the following changes to keep the report heading and Total Sales values on all pages of the report.

    • Open the report in design view.
    • Drag the Category Name header bar down to get enough space on the Page Header Section, to cut and paste the Report Heading there.
    • High-light report heading and the report date controls (leave alone the Total Sales textbox), cut and paste them into the Page Header Section.
    • Select the Total Sales text box, copy and paste it on the Page Header Section,  move and position it 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 (over-writing the existing expression) of the copied text box.

      This will display the value from 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.
    • Open 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 few minutes.



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...


Blog Archive

Recent Posts