Monday, August 1, 2016

Access_Crosstab_Query_to_TSQL


Converting an Access Crosstab query into SQL Server (T-SQL) is more difficult than you might think.  Take the following crosstab:

----------------------------------------------------------------------------------------
------  Access Crosstab Query   -----------------
----------------------------------------------------------------------------------------

/*
TRANSFORM Sum(Line_Days) AS SumOfLine_Days
SELECT Factory, Line, Sum(Line_Days) AS [Total Of Line_Days]
FROM Factory_Line_temp
GROUP BY Factory, Line
PIVOT DateID;

*/
 


In order do this in SQL Server, you need to build the SQL statement dynamically, adding the crosstab headers by reading them from the query, then building them in code. 



----------------------------------------------------------------------------------------
------ T-SQL Equivalent -----------------
----------------------------------------------------------------------------------------
DECLARE  @ColumnOrder  AS  TABLE(ColumnName 
                                 varchar(8)
                                 NOT NULL
                                 PRIMARY KEY)


DECLARE  @strSQL  AS NVARCHAR(4000)
INSERT INTO @ColumnOrder SELECT DISTINCT DateID FROM Factory_Line_temp


DECLARE  @XTabColumnNames  AS NVARCHAR(MAX)
DECLARE         @XTabColumn      AS varchar(20)
SET @XTabColumn = (SELECT MIN(ColumnName) FROM  @ColumnOrder)


SET @XTabColumnNames = N''


-- Create the xTab columns


WHILE (@XTabColumn IS NOT NULL)
  BEGIN
    SET @XTabColumnNames = @XTabColumnNames + N',' +
      QUOTENAME(CAST(@XTabColumn AS NVARCHAR(10)))
   SET @XTabColumn = (SELECT MIN(ColumnName)
                          FROM   @ColumnOrder
                          WHERE  ColumnName > @XTabColumn)
  END


SET @XTabColumnNames = SUBSTRING(@XTabColumnNames,2,LEN(@XTabColumnNames))


PRINT @XTabColumnNames


SET @strSQL = N'SELECT * FROM (
              SELECT Factory, Line, DateID, Line_Days
              FROM Factory_Line_temp) as header
              pivot (sum(Line_Days)
                    FOR DateID IN(' + @XTabColumnNames + N'))  AS Pvt '


PRINT @strSQL 


-- Execute strSQL sql

EXEC sp_executesql @strSQL




Monday, July 25, 2016

Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server?


Access SQL




  • UPDATE Books INNER JOIN UpdateList
       ON Books.ISBN = UpdateList.ISBN
       SET Books.Price = [Updatelist].[price]


SQL Server
  • UPDATE Books
        SET Books.Price = [Updatelist].[price]
       
        FROM Books INNER JOIN UpdateList
           ON Books.ISBN = UpdateList.ISBN




    Tuesday, July 19, 2016

    Access InStr() function to SQL Server equivalent


    What is the SQL Server equivalent to Access InStr() function?

    TargetField: String expression being searched
    SearchValue: String expression being sought
    StartPosition: Starting position for each search (optional)
    • InStr(TargetField, SearchValue)
    • InStr(StartPosition, TargetField, SearchValue)
    SQL Server
    • CHARINDEX(SearchValue, TargetField)
    • CHARINDEX(SearchValue, TargetField, StartPosition )

    Wednesday, July 13, 2016

    Access IIf() function to SQL Server equivalent

    What is the SQL Server equivalent to Access IIf() function?


    Access SQL
    • IIf([ResultValue]>=1, [Result Value], Null)


    SQL Server
    •  CASE WHEN [Result Value]>=1
    •    THEN [Result Value]
    •    ELSE NULL 
    •  END 

    Thursday, July 7, 2016

    Access NZ() function to SQL Server equivalent


    What is the SQL Server equivalent to Access NZ() function?


    Access SQL
    • NZ([Event Timestamp])


    SQL Server Equivalent
    • COALESCE([Event Timestamp],0)
    • ISNULL([Event Timestamp],0)
      • do not confuse this with the Access isnull() function.

    Tuesday, July 5, 2016

    Access CDate() function to SQL Server equivalent


    What is the SQL Server equivalent to Access CDate() function?


    Access SQL
    • CDate([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as datetime)
    • convert(datetime, [Event Timestamp])

    Friday, July 1, 2016

    Access DateValue() function to SQL Server equivalent

    What is the SQL Server equivalent to Access DateValue() function?


    Access SQL
    • DateValue([Event Timestamp])


    SQL Server Equivalent
    • cast([Event Timestamp] as date)
    • convert(date, [Event Timestamp])

    Tuesday, May 31, 2016

    Automating Microsoft Word and Excel from Access


    The Microsoft Office Suite has a number of tools for communicating between its products, the most powerful of which is Office Automation through VBA (Visual Basic for Applications). By exposing the underlying object model for each of its applications, Microsoft made it possible to exercise programmatic control not only within a particular application, but between applications.

    In my last article, Easy Excel Charting from Access, I showed how to create Excel chart from Access. The charting capabilities of Access using MS Graph are fairly primitive, so by sending the data to Excel, I can have a much richer set of charting tools. However, what I never covered is what to do with charts once they've been created.

    It would be useful to paste these charts into an Access report, but that's not possible. However, it is possible to programmatically create a Word document, write data from your database to it, and then paste the appropriate chart into the document, making Word a very powerful and flexible reporting tool for Access.

    Creating the Template

    First thing to do is create a Word template. Word templates are Word documents saved with a DOTX extension. When you open a template, Word creates a new document based on the template. So the template itself is never modified. This makes it ideal for my purposes here. I'll create a template with the basic structure of my document and static text. As an additional benefit, an end user can modify the static parts of the document without requiring program modification.

    There is no single right way to create the template, but perhaps the easiest is to first create a document that looks like the finished report. Something like Figure 1.

    image_thumb3
    Figure 1: Create a document that will look like the final report.

    Most of the information on this report, including the chart, will be filled in from information stored in the database. In order to create places for the information to be inserted, I need to create bookmarks. Bookmarks are placeholders within your Word document.

    To create a bookmark, I place my cursor where I want the bookmark to go and click on Insert>Bookmark. A dialog box like Figure 2 will appear. I type in the name for my bookmark (in this case Condition) and click Add. I now have a bookmark. By default, bookmarks are invisible, so in order to see them, I click to Tools>Options on the menu bar and click the Bookmarks checkbox. The bookmark will show up as a gray I-bar. See figure 3.

    image_thumb6
    Figure 2: Bookmark dialog showing the newly created Condition bookmark.

    Place the cursor at the spots where the other bookmarks should be created and repeat the operation. When done, it will look like figure 3.

    image_thumb7
    Figure 3: Document with bookmarks created.

    All that is left to do is erase the information following the bookmarks and save the document as a Word template.

    Word always wants to store templates in the default Templates folder in Program Files. Depending on how Office was installed, this folder could be different places on different machines. Therefore, I prefer to store the template in the same folder as the Access database or perhaps in a subfolder. This has the advantage of allowing the program to always know where the template is because as I will show later, I can programmatically determine the path to the application. So after I select Word Template (*.dot) in the File Type dropdown box, I browse to the correct folder to save the template. Figure 4 shows the completed template.

    Once the template is created, I can re-hide the bookmarks. They don't need to be visible to work.

    image_thumb10
    Figure 4: Completed template.

    Automation Program Overview

    Next, I create the Access subroutine (called ExcelWordAutomation), which automates the process of creating the Word documents. The overall process goes something like this:

    1. Open the Word template to create the document.
    2. Open the Excel spreadsheet that has pre-created charts for insertion.
    3. Open a recordset with data for reports.
    4. Insert data from the first record into the first report.
    5. Copy the appropriate chart in Excel and past into the Word document.
    6. Save the Word document with an appropriate name.
    7. Loop to step 4 and repeat until the recordset is finished.
    8. Close Excel, Word, and all object variables.

    Setting a Reference to Word and Excel

    Next, I switch to Access to create the export routine. But before I do that, I need to set a reference to Excel in Access. A reference is a call to an external code library, in this case the Excel Object Model. This will allow me to manipulate Excel objects from within Access.

    To set a reference, I open the Visual Basic Editor. Then I go to Tools > References. In the dialog box, scroll down to Microsoft Word 15.0 Object Library and Microsoft Excel 15.0 Object Library (your version numbers may vary). Click the checkbox next to it and click OK. Figure 1 shows what the References Dialog looks like.

    image_thumb1 
    Figure 1: The References Dialog showing the Word and Excel Object Library reference.

    So let's look at the program details. As always, I start the routine with a call to an Error Handler to trap a number of common errors. I'll explain the specific errors later.

    Sub ExcelWordAutomation()
    On Error GoTo HandleError

    Then I need to declare a number of variables. In order to create and manipulate objects in Word or Excel, I need some object variables. The power of Office Automation is that Word or Excel object variables inherit the capabilities of the parent program. Ordinarily, I’d define them here, but for clarity, I’m going to define them throughout the code where they’re needed.

    Next, I need some Access object variables.

    Dim db As DAO.Database
    Dim rsReportData As DAO.Recordset
    Dim rsExclusions As DAO.Recordset

    And lastly, I need some scalar variables.

    Dim strsql As String
    Dim strFile As String
    Dim conPath As String
    Dim X As Long
    Dim wdGoToBookmark As Integer

    As I mentioned earlier, I need to find the folder where the database resides. This is where the template is stored and also where I'll save the completed documents. Naturally, if I wanted the files stored elsewhere (say a specific directory), I could code that here, too. Fortunately, I can easily find the full path and file name of the database by using the Name property of the database object.

    To do this, I'll instantiate a database variable with the CurrentDb function. Then with judicious use of the Mid, Len, and Dir functions, I can return the default folder, that is, the folder where the database resides.

    Set db = CurrentDb
    conPath = CurrentProject.Path

    Since I have a single workbook which contains all the charts I need, each on a separate sheet, I'll open that first. In order to do that, I will instantiate first an Excel application object and then a workbook object.

    Dim objXLBook As Excel.Workbook
    Set objXLBook = objXLApp.Workbooks.Open(conPath & "\MySpreadsheetChart.xlsx")

    In order to be able to watch the process of copying the charts, I'm going to make both Excel and the workbook visible. Technically, this is not necessary. The code will work just as well if Excel remains invisible, but it's interesting to watch the process.

    objXLApp.Visible = True
    objXLBook.Windows(1).Visible = True

    Now, I need to open a recordset containing the data needed for the reports. This information is stored in a table called ReportData (see Figure 5).

    Set rsReportData = db.OpenRecordset("ReportData", dbOpenSnapshot)

    Figure 5: Table which stores the data to be inserted in each report.

    Since each record contains the information for a single report, I'll step through the records one at a time, creating a Word document for each. A Do…While loop will work perfectly for this.

    Do While Not rsReportData.EOF

    Just as I did with Excel, I have to create a Word object. With Word, however, I only need to create the application object. I'll also open a new document based on the MyWordTemplate.dot template.

    Dim objWord As New Word.Application
    Set objWord = CreateObject("Word.Application")
    objWord.Documents.Add conPath & "\MyWordTemplate.dotx"

    And, again, I'll make them both visible. Like the Excel objects, these lines are optional.

    objWord.Visible = True
    objWord.Windows(1).Visible = True

    Now that my document is created, I need to find the appropriate bookmarks and insert information from my recordset. Here, I use the "With…End With" construct to avoid having to repeat the objWord.ActiveDocument.Bookmarks object reference for each bookmark.

    With objWord.ActiveDocument.Bookmarks
        .Item("Condition").Range.Text = rsReportData!Condition
        .Item("Medication").Range.Text = rsReportData!Medication
        .Item("Timeframe").Range.Text = rsReportData!TimeFrame
        .Item("Numerator").Range.Text = rsReportData!NumeratorDef
        .Item("Denominator").Range.Text = rsReportData!DenominatorDef
        .Item("Target").Range.Text = rsReportData!Target
    End With

    'find and write exclusion data
    strsql = "SELECT ReportID, Exclusion " & _
        "FROM ExclusionData " & _
        "WHERE ReportID=" & rsReportData!ReportID
      
    Set rsExclusions = db.OpenRecordset(strsql)
        Do While Not rsExclusions.EOF
        With objWord.ActiveDocument.Bookmarks
            .Item("exclusions").Range.Text = rsExclusions!Exclusion & vbCrLf
            rsExclusions.MoveNext
        End With
    Loop
    rsExclusions.Close

    To paste the chart into the document, I'm going to use the Windows Clipboard. So I have to switch to my already open Excel workbook, find the sheet matching the medication value in my recordset and copy it to the clipboard.

    objXLBook.Sheets(rsReportData!Medication.Value).Select
    objXLBook.ActiveSheet.ChartObjects("Chart 1").Activate
    objXLBook.ActiveChart.ChartArea.Select
    objXLBook.ActiveChart.ChartArea.Copy

    Then I return to my Word document, move my cursor to the "Chart" bookmark, and paste the chart into the document. In this case, setting the Word object to visible is mandatory. This method of pasting from the clipboard requires the object to be activated, and in order to be activated, it must be visible.

    objWord.Activate
    wdGoToBookmark = -1
    objWord.Selection.Goto What:=wdGoToBookmark, Name:="Chart"
    objWord.Selection.Paste

    That's all I need in the document, so I'll save the document in the same directory as the template, naming it after the medication value in my recordset.

    objWord.ActiveDocument.SaveAs (conPath & "\" & rsReportData!Medication & ".docx")
    objWord.Quit

    And then, I return to process the next record, looping until the recordset until done.

        rsReportData.MoveNext
    Loop
       

    After I've processed all the records, I'm done with the Excel workbook, so I'll close it without saving and close Excel.

    objXLBook.Close SaveChanges:=False
    objXLApp.Workbooks.Close
    objXLApp.Quit

    Then I add a message box that identifies when the process is done.

    MsgBox "Done!" & vbCrLf & vbCrLf & _
        "Look in this directory" & vbCrLf & conPath & vbCrLf & _
        "for your documents."

    Lastly, I complete the error trapping. After the ProcDone label, I'll destroy the object variables I've created. I do that here so if there is an error and the routine terminates, no Word, Excel, or Access object will be left in memory.

    ProcDone:
    ' clean up our object variables
    Set objXLBook = Nothing
    Set objXLApp = Nothing
    Set objWord = Nothing
    Set rsReportData = Nothing
    Set rsExclusions = Nothing
    Set db = Nothing
       
    ExitHere:
        Exit Sub

    There are three main errors that must be handled. Error 432 occurs if the Excel spreadsheet is not found. Since no other objects are open, I just want it to end the routine without doing anything else. Error 5151 occurs if the Word Template does not exist. In that case, I want the routine to close the Open Excel object and end the program. Error 4152 can happen in a variety of circumstances, all of which come down to an error in the file or path. Since both Word and Excel objects are open at the time of the save, I want both objects to close without saving. The last error, 9, can happen when a record has been added to the table without a corresponding spreadsheet in the workbook. This error is handled just like 4152, but displays a different error message.

    All other errors will be handled by the Case Else and will result in the error description and number being displayed and the program ending. It's always a good idea to add a generic error handler as users can always find ways to create errors that the developer can't anticipate. Since I don't know what the error might be, I don't know what objects might be open, so I don't attempt to close them.

    HandleError:
        'display appropriate error message
        Select Case Err.Number
            Case 5151 'Word template not found
                'Close stranded applications
                objXLBook.Close SaveChanges:=False
                objXLApp.Quit
                MsgBox "Word template not found"
            Case 432 'Excel spreadsheet not found
                MsgBox "Excel spreadsheet not found"
            Case 5152 'Invalid file name
                'Close stranded applications
                objXLBook.Close SaveChanges:=False
                objXLApp.Quit
                objWord.ActiveDocument.Close SaveChanges:=False
                objWord.Quit
                MsgBox "This file or folder does not exist"
            Case Else
                MsgBox Err.Description, vbExclamation, _
                 "Error " & Err.Number
                 Set objXLBook = Nothing
    Set objXLApp = Nothing
    Set objWord = Nothing
    Set rsReportData = Nothing
    Set rsExclusions = Nothing
    Set db = Nothing
       
        End Select
        Resume ProcDone
    End Sub

    Running the Code

    Now the code is complete and ready to run. All that's necessary is to call the routine from a button or some other Event Procedure, like so:

    Private Sub cmdRun_Click()
      Call ExcelWordAutomation
    End Sub

    On completion, your folder will have one report document for each record in the ReportData table.

    Conclusion

    Office Automation is a powerful tool for integrating separate Office applications. By using a single macro language for all of the Office applications and exposing the object models of each, Microsoft made it possible to build custom applications that would be difficult any other way. The possibilities are limited only by the imagination of the developer.

    You can download a sample database illustrating all the code here, AutomatingWordFromAccess .



    .

    Wednesday, May 25, 2016

    Easy Excel Charting from Access


    One of the strengths of the Microsoft Office suite is the ability for its component parts to communicate between themselves. It is particularly useful to communicate between Access and Excel because while Access is superior at storing data, Excel is superior at manipulating it. For example, I am often asked if it's possible to send data from Access to formatted cells in Excel and create a chart based on it.

    This problem can be solved by extensive use of Office Automation, but many people find this prospect daunting. Office Automation through VBA (Visual Basic for Applications) is an extremely powerful but complicated method. I discussed this method in my post: How do I export Access data to Excel - Part 3.

    But there are other methods, like the Access TransferSpreadsheet method, that are easier to use, but far more limited. How do I export Access data to Excel - Part 2

    A Middle Ground

    However, it's also possible to solve with a combination of built-in features of both Access and Excel, that is, Excel templates, a tiny bit of Office Automation, and the Access TransferSpreadsheet method. This middle ground uses the strengths of both, and is both easy and flexible.

    The TransferSpreadsheet method allows me to export a query or table from Access to Excel. If the workbook does not exist, it will create one. If the workbook does exist, it will create a new sheet in the workbook named after the table or query. But if both the workbook and sheet already exist, Access will over-write the data in the sheet. This is the critical feature.

    Another feature I'll make use of is Excel's ability to link cells from one sheet to another. This means I can link a chart on one worksheet to another worksheet that holds the data. If I use the TransferSpreadsheet method to export a query that overwrites the data in the data worksheet, my chart will be updated automatically.

    Lastly, I will use an Excel template to create a new Excel workbook with pre-formatted cells and charts. An Excel template is a special kind of workbook with a .xltx extension. When you open a template, it automatically creates a new workbook with a .xlsx extension, leaving the template untouched.

    These features, used in combination with a small amount of Office Automation, give me all the tools I need to accomplish the task.

    Overview

    The overall process goes something like this:

    1. Create an empty Excel template (.xltx)
    2. Create the Export code to do the following:
      1. Open the Excel template
      2. Save the resultant workbook
      3. Export my data to the workbook with the TransferSpreadsheet method.
    3. Run the export program to create a new workbook with the exported data.
    4. Create a new worksheet in this workbook with whatever data formatting and charts I need, linked to the data worksheet.
    5. Save this workbook as template, over-writing the previous.
    6. Clear the data from the exported worksheet.

    Creating the Template

    First thing to do is create an Excel template.

    You might think I could simply create a data worksheet manually and name the tab after my exported query. Unfortunately, it's not that easy. That's because the TransferSpreadsheet method looks for a named range to export to, not a worksheet name. So if I create a worksheet named after my query, say Drug1, when I export the query, it will create a new worksheet called Drug1(1) instead of exporting to the worksheet I want.

    The easiest way around this is to use the Access TransferSpreadsheet to export my query to a blank workbook. This will create the data worksheet automatically for me with the proper named range. Then I'll re-save it as a template so the export routine will find the correct worksheet for the next time.

    So to start, I create a blank workbook and save it as a template. To do that, choose File > SaveAs and in the file type box choose Template(*.xltx). Now, Excel will try to save this in the default templates folder. I prefer to store this template with the database, so I then browse to the folder where the database exists and save it there.

    Setting a Reference to Excel

    Next, I switch to Access to create the export routine. But before I do that, I need to set a reference to Excel in Access. A reference is a call to an external code library, in this case the Excel Object Model. This will allow me to manipulate Excel objects from within Access.

    To set a reference, I open the Visual Basic Editor. Then I go to Tools > References. In the dialog box, scroll down to Microsoft Excel 15.0 Object Library (your version number may vary). Click the checkbox next to it and click OK. Figure 1 shows what the References Dialog looks like.

    image 
    Figure 1: The References Dialog showing the Excel Object Library reference.

    Export Program

    Next, I'll create the Access subroutine (called ExportSpreadsheet), which exports the data to Excel. I start the routine with a call to an Error Handler. I'll explain why later.

    Sub ExportSpreadsheet()
    On Error GoTo HandleError

    Next, I'll declare some variables.

    Dim objXLApp As Object
    Set objXLApp = CreateObject("Excel.Application")
    Dim objXLBook As Excel.Workbook
    Dim strFile As String
    Dim strPath As String

    I also need to find the folder where the database resides. This is where the template is stored and also where I'll save the completed workbook. Naturally, if I wanted the files stored elsewhere (say a specific directory), I could code that here, too.

    strFile = CurrentProject.Name
    strPath = CurrentProject.Path

    Now, I want to delete the existing workbook if it exists. I do this keep the SaveAs dialog box from asking me if I want to over-write the existing file. This is most useful if I am creating multiple workbooks.

    Kill strPath & "\MySpreadsheet.xlsx"

    Next, I need to create a workbook from the template. To do that, I have to use a tiny bit of Office Automation. I have to open an Excel Application object and an Excel Workbook object.

    Set objXLApp = New Excel.Application
    Set objXLBook = objXLApp.Workbooks.Open(strPath & _
           "\MyTemplate2010.xltx")

    Next, I save the workbook object as a workbook and close it.

    objXLBook.SaveAs (strPath & "\MySpreadsheet.xlsx")
    objXLBook.Close

    Which leaves a file called "MySpreadsheet.xlsx" in the same directory as my Access database. Next I use the TransferSpreadsheet method to export two queries to the workbook. Because I am specifying the same workbook, each query will create a separate worksheet in the workbook.

    DoCmd.TransferSpreadsheet acExport, , "qryDrug1", strPath & _
          "\MySpreadsheet.xlsx", True
    DoCmd.TransferSpreadsheet acExport, , "qryDrug3", strPath & _
          "\MySpreadsheet.xlsx", True

    Sometimes, the chart data is not refreshed in the resulting workbook, so I'll open the workbook and save it again. This takes care of the refresh problem.

    Set objXLBook = objXLApp.Workbooks.Open(strPath & _
         "\MySpreadsheet.xlsx")
    objXLBook.Save
    objXLBook.Close

    Then I add a message box that identifies when the process is done.

    MsgBox "Done!" & vbCrLf & vbCrLf & _
         "Look in the directory" & vbCrLf & vbCrLf & _
         "where the application resides for ""MySpreadsheet.xlsx"""

    Lastly, I complete the error trapping. After the ProcDone label, I'll destroy the object variables I've created. I do that here so if there is an error and the routine terminates, an Excel object won't be left in memory.

    ProcDone:
    Set objXLBook = Nothing
    Set objXLApp = Nothing
    ExitHere:
    Exit Sub

    There are two main errors that must be handled. Error 1004 occurs if the Template does not exist. In that case, I just want the routine to end without doing anything else. The other error, 53, happens when the MySpreadsheet.xlsx file (that I'm trying to delete with the KILL command) does not exist. In that case, I just want the routine to continue.

    HandleError:
    Select Case Err.Number
    Case 1004 'a template does not exist
         MsgBox "There is no template for this chart."
         Resume ProcDone
    Case 53 'Excel file cannot be found to delete
         Resume Next
    Case Else
         MsgBox Err.Description, vbExclamation, _
              "Error " & Err.Number
         Resume ProcDone
    End Select
    End Sub

    In this example, I am going to export two queries (Drug1 and Drug3) to my spreadsheet and create two separate charts based on them. Because of that, I'm going to hard code the query names into the routine. But to utilize the real power of this process, you could store a list of queries to be exported in a table, then create a loop that would march through the table, exporting each query in turn. In this way, you could create literally hundreds of charts in just a few minutes. If your process requires dozens or hundreds of charts created every month, this could be quite handy.

    Running the Code the first time

    So far, all I've got is an empty template and my Export routine. The next step is to run the Export code for the first time. When that happens, my previously empty spreadsheet has two worksheets: qryDrug1 and qryDrug3.

    Figure 2 shows the resulting workbook.

    image
    Figure 2: Workbook created by the first run of the ExportSpreadsheet subroutine.

    Create worksheet with formatted data and chart.

    Now I have to manually create two new worksheets, which I'll name Drug1 and Drug3. These will hold my formatted data and charts. (Since the process is the same for both charts, I'll just concentrate on Drug1, but you should realize that you can create as many charts as you want up to the 255 sheet limit of Excel.)

    So next, I need to link the cells containing the data in sheet qryDrug1 into my new Drug1 sheet. To do that, I open the Drug1 sheet and select cell A1, hit the equal key (=), click on the qryDrug1 tab to go to that sheet, click cell A1 in that sheet, and finally click the green check mark on the Formula bar. The resulting formula looks like this: =qryDrug1!A1. Next, select cell A1 and click the Copy button, select the range A1:C13 and click Paste. Figure 3 shows the resulting worksheet.

    image
    Figure 3: Worksheet of cells linked to the data on qryDrug1.

    Now I can format this data. For simplicity, I'll just apply an auto format, then I'll format the cells in column B as Percent with no decimal places. Figure 4 shows the results of the formatting.

    But I'm not done with this sheet. I also want to create a chart on this data. So I'll use the Chart Wizard to create a bar chart comparing the drug prescription rate for each physician. Figure 4 also shows the resulting chart.

    image
    Figure 4: Formatted data and chart based on the data linked on the worksheet.

    Notice that while the chart is based on the formatted data, the actual data resides on the qryDrug1 worksheet. This is important because the Drug1 information in the qryDrug1 worksheet is not formatted as percent. If I based the chart on the qryDrug1 worksheet, the Y axis of my chart would not automatically be formatted as percent either. By linking the data and formatting it, I can control the format of the chart.

    Repeat these steps for the Drug3 worksheet.

    Save Workbook as Template

    I'm almost done now. All that's left is to save my finished spreadsheet as a template, overwriting my existing MyTemplate.xltx template. Again, Excel will try to save the template in the default Templates folder, so I have to browse to the application folder to save it over the existing template.

    Lastly, I need to delete the data in the data worksheets: qryDrug1 and qryDrug3. It's very important to just delete the data and NOT the worksheets themselves. To do so will put #REF in each linked cell and all the linking will be lost. But just clearing the data from the exported data worksheet will leave the links intact. Figure 5 shows the cleared worksheet.

    image
    Figure 5: Formatted data and chart with data deleted from the qryDrug1 worksheet.

    Re-Run the Program

    That's all there is too it. Running the program again will open the new template, save it as a workbook, export my queries to the existing worksheets, which will display in the linked cells and chart. Figure 6 shows the final result.

    image
    Figure 6: Results of running the export to the completed template.

    Conclusion

    Microsoft Access has some powerful tools for communicating with Excel. Some of these tools, like the Transfer Spreadsheet method, are very easy to use but limited. Others, like Office Automation, are flexible but complicated. But as I have shown, by using the strengths of both products, you can accomplish this in a way that that is both easy and flexible.

    To download a working sample database, follow this link: ExportToExcelCharts.mdb (intermediate)

    Monday, May 23, 2016

    How do I export Access data to Excel - Part 1

    There are many ways to export data from an Access database to an Excel spreadsheet.  These include:
    1. Manual Methods (This post) 
      • Cut and Paste
      • Manual Export
      • Saved Exports
    2. TransferSpreadsheet Methods
      • Macros
      • VBA
    3. Office Automation Methods
      • Do…While
      • CopyFromRecordSet
    Linked Excel Sheets Not Updateable
    One surprising way that you CANNOT export Access data to Excel is by linking the spreadsheet into Access. Linked Excel spreadsheets are Read-Only in Access. Why? 
    The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.
    In this part, I’ll discuss manual methods and leave TransferSpreadsheet and Office Automation for later.
    Cut and Paste
    I remember a time when I couldn’t take for granted that everyone knew what cut and paste was or how to do it.  That’s no longer the case, but I thought I should mention it for completeness.
    Sometimes the quickest and easiest way to get Access data into excel is just open the table or query in Datasheet View
    image
    and paste it into a blank workbook.
    image
    There are limitations, of course.  You can copy whole rows
    image
    image
    or whole columns
    image
    image
    But not a combination.  You can work around this, however, by creating a query that limits the rows and columns to just those you want. 
    Manual Export
    You can also export Access data to Excel without opening a workbook by using Access’s built in export facility.  With this, you can export tables or saved queries to either a new or existing Excel workbook.  There a several ways to do this.
    On the Ribbon, there is the External Data tab where you can export in many different formats. Select the query or table you want to export in the Navigation Pane, and select Excel on the Export section of the External Data tab.
    image
    Or, you can Right Click on the object for context sensitive menu
    image
    After that, it’s just a matter of giving a file location and naming the file.
    image
    By default, the name will be the same as your table or query.  You can, of course, change either at this point.
    New Vs. Existing Excel Workbook
    You can export to either a new workbook or an existing workbook.There are several possibilities:
    1. New Workbook – XLSX file does not exist
      • If the file name (TheDataQuery.xlsx) does not exist in that folder, a new workbook will be created with that name and a tab named for the object (TheDataQuery).
      • If you change the filename in the  and that new name also does not exist, the file will be created under the new name also with a tab named for the object.
    2. Existing Workbook
      • If the workbook exists, AND it already has a tab named for the object, the data in the tab will be over-written.This will become important later when I discuss the TransferSpreadsheet Method. (Note: if the new dataset is narrower or shorter than the data already on the tab, only the cells in the shape of the new data will be over-written.) 
      • If the workbook exists and it DOES NOT have an existing tab named for your object, a new tab will be created, leaving all existing tabs alone.
    Saved Exports
    After clicking OK in the Excel – Export Spreadsheet dialog, you will be given one last option: Save Export Steps. If you check the Save export steps box, the export will be saved.
    image
    Running Saved Exports
    Lastly, to run the saved export, just click the Saved Exports button on the External Data ribbon tab.image
    This has some limited utility, but no flexibility. It will export the same object to the same file in the same location. On the plus side, you don’t have to go through the whole Export Wizard again.
    Next time in How do I export Access data to Excel - Part 2, I’ll discuss more flexible export automation techniques.

    How do I export Access data to Excel - Part 2


    There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

    1. Manual Methods
      • Cut and Paste
      • Manual Export
      • Saved Exports
    2. TransferSpreadsheet Methods (this article) 
      • Macros
      • VBA
    3. Office Automation Methods
      • Do…While
      • CopyFromRecordSet

    In Part 1, I discussed various manual methods.  This time I’ll look at the TransferSpreadsheet method, which allows you to have more control over automating exports.

    Import Export Spreadsheet – Macro Action

    Macros in Microsoft Access offer a quick easy way to automate some processes.  Macros are created and maintained in a graphical user interface, You don’t have to do any coding or know a programming language.

    To create a new macro, go to the Create tab on the ribbon and choose Macro.

    The Macro Editor will look like this

    image

    To add a new macro action, click the drop down, Add New Action.  However, you’ll note that the ImportExportSpreadsheet action is not in the list.

    image image

    This is because when Action Catalog is selected on the ribbon, you only get actions the Microsoft considers “safe”. In this case, that means that using any of these actions don’t require the database to be in a Trusted Location. Unfortunately, ImportExportSpreadsheet is not one of those actions, so you’ll need to select Show All Actions, instead.

    image image

    Now you can select ImportExportSpreadsheet.  You’ll get a number of options to fill out:

    image

    1. Transfer Type: Obviously, we want Export, but the same action can be used to Import or Link data.
    2. Spreadsheet Type: Excel Workbook exports to your installed version of Excel. If you need to export it in a different spreadsheet format, you can change it here.
    3. Table Name: The name of the Table or Query you want to export.
    4. File Name: The path and file name of the spreadsheet. The filename will have a direct effect on how the export behaves.
      • If the file does not exist, a new spreadsheet named for the table will be created and the data will be placed on a tab also named for the table (i.e. TheDataQuery)
      • If the file does exist, AND named tab also exists, the data in the tab will be over-written.
      • If the file does exist BUT the named tab does not exist, a new tab named for the table will be created. Existing tabs will be left alone.
    5. Has Field Names: If Yes, it will put the field names in row 1 of the spreadsheet.

    Run the macro using the Run button on the Design tab.

    image

    It will create a workbook named Data_Spreadsheet.xlsx with a datatab called TheDataQuery.

    Why Bother?

    There’s no real value to creating a macro for a one-time export.  However, a macro will allow you to export multiple times or multiple “tables” or both. So to export a second “table”, I simply add another macro action.

    image

    The file name also determine how multiple exports are handled.

    • If the filenames are the same for multiple exports, the will be exported to the same workbook.
    • If the filenames are different, they will be exported to different workbooks.

    image

    Transfer Spreadsheet – VBA Method

    The Macro method is useful if you always export the same tables or queries to the same locations.  It’s also very easy to set up.  However, since all the table names and path/file names are hard coded, to change anything, you have to modify the application.  This is less than ideal if you want to the application to be used by non-developers.

    A better way is to store the names of the queries/tables in a tables and use a bit of VBA to repeat the export process for each value in the table.

    Converting Macros to VBA

    Fortunately, you don’t have to start from scratch.  You can convert an existing macro to a VBA procedure, which will give you the basic layout.  To do this,open the macro in Design View and click: Convert Macros to Visual Basic

    image

    The procedure will appear in a Module named for the macro and will look something like this:

    '------------------------------------------------------------
    ' Macro2
    '------------------------------------------------------------
    Function Macro2()
    On Error GoTo Macro2_Err

        DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery", _
            "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
            True, ""
        DoCmd.TransferSpreadsheet acExport, 10, "TheDataQuery2", _
            "C:\Users\roger_000\Documents\Access\Data_Spreadsheet.xlsx", _
            True, ""

    Macro2_Exit:
        Exit Function

    Macro2_Err:
        MsgBox Error$
        Resume Macro2_Exit

    End Function
    '------------------------------------------------------------

    If I had named my macro something relevant, like “Export_Data”, the procedure would be named for that.

    Creating an Export table.

    Next, I will create a table called MyExport which will hold the text values of the table and filenames I want exported.

    image

    Then modify the code as follows.  I’ve added comments in the code to explain the modifications

    '------------------------------------------------------------
    ' Export_Data
    '------------------------------------------------------------
    Sub Export_Data()
    On Error GoTo Export_Data_Err

    'add object and scalar variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim TheTable As String
    Dim TheFile As String

    'open the Export Table (MyExport) as a recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MyExport")

    'loop through recordset
    Do While Not rs.EOF

        'set scalar variables
        TheTable = rs!Export_Table
        TheFile = rs!Export_Filename
       
        'export the table using the variables
        DoCmd.TransferSpreadsheet acExport, 10, TheTable, _
            TheFile, True, ""
           
        'move to the next record
        rs.MoveNext
    Loop

    Export_Data_Exit:
        Exit Sub

    Export_Data_Err:
        MsgBox Error$
        Resume Export_Data_Exit

    End Sub
    '------------------------------------------------------------
    Note: I prefer Sub procedures rather than functions in this case, so I modified it shown in the highlighting.

    Now Just Run It

    Next I just run the code (usually by way of a button on a form), and the indicated tables/queries will be exported.  In this case to the same workbook.  I can export them to different workbooks by simply changing the filenames in the MyExport table.  This can be useful if you want each query to go to a different user’s folder.

    Download A Sample

    You can find the companion sample here: ExportToExcel_TransferSpreadsheet

    Taking It Further

    You can take the automated process even further by exporting the data for a formatted sheet or chart using an Excel template. You can download samples with complete explanation here:

    • ExportToExcel.mdb ( intermediate )
      This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a formatted spreadsheet. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the formatted spreadsheet.
    • ExportToExcelCharts.mdb ( intermediate )
      Sample with Documentation. This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a Chart. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new tab into the chart.

    Next up, in Part 3, I’ll show how to automate exports even further using Office Automation.

    How do I export Access data to Excel - Part 3

    There are many ways to export data from an Access database to an Excel spreadsheet.  These include:

    In Part 1, I discussed various manual methods. In Part 2, I discussed TranferSpreadsheet. This time I’ll look at Office Automation, which gives you maximum control over automated exports.

      Office Automation

      Office automation allows a developer to open and manipulate other MS Office applications in VBA code.  It allows the Access developer to use the specialized functions of those other programs, functions that would be hard to create natively.  I’ve used automation to create documents in Word, charts in Excel, and presentations in PowerPoint.  Sometimes all three from the same Access application.

      Here are a few examples:

    • AutomatingWordFromAccess.mdb ( advanced)
    • ExportToExcelCharts.mdb ( intermediate )
    • AutomatingPowerpoint.mdb ( intermediate )

      In this case, I want to export data from an Access database into specific formatted cells in an Excel spreadsheet. To do this, I need to use the Data Access Object model built into all Office apps.

      Excel Object Library (VBA)

      Through VBA, a developer can open and manipulate other Office applications. This is not limited to MS Office applications, but that’s another discussion. Each Office application has its own Object Model, each with its own Collections, Methods, and Properties. Once I reference that model in Access, I can use VBA to use that application’s Collections, Methods and Properties. Since I want to automate Excel, I need to use the Excel Object Library.

      Setting References

      In order to use an external object model, I need to first set a Reference to it. To do that, I need to open the Visual Basic Editor (VBE), which can be opened in Access on the Database Tools tab and choosing Visual Basic.

      image

      Once the VBE is open, choose Tools > References

      image

      The Reference List will look like this:

      image

      The first three checked references will automatically be there in a new Access database.  The fourth line WON’T.  You need to scroll down the list (to the “M”s) to choose Excel.  Check the box and click OK. Reopen the References, and it should look something like the above.

      Depending on your Office version, the references may be named differently, there may be additional references, or they may be in a different order. It’s not important most of the time.  The important thing is to find and check the Excel reference.

      Data Access Objects (DAO)

      DAO is the object model used to open and manipulate databases in VBA.  DAO is not part of Access. Technically, it’s the object model for the Jet database engine, and it can be referenced in any Office application to manipulate data.  However, since Access uses the Jet database engine as its default, a reference is set to it whenever an Access database is created. Since I need to take data from an Access database and send it to an Excel worksheet, I need both object models.

      Two Methods

      There are two ways of using Office Automation that I want to discuss:

      1. Using a Do…While loop to PUSH data from Access to Excel
      2. Using the CopyFromRecordset to PULL data into Excel from Access

      Regardless of which method I use, there is a basic framework of creating and instantiating objects I need to build.  This framework looks like this:

      'First I need to define some variables. Ordinarily, I would define all my variables here, but in this case, I’m going to define them as I go for clarity.
      Dim RowVal As Integer
      Dim ColVal As Integer
      Dim conPath As String

      '  Find the path where the database resides
          conPath = CurrentProject.Path

      ‘  Define and Open Database Object
          Dim db As DAO.Database
        Set db = CurrentDb


      '  Define and Open Recordset Object based on a query in my database called “TheDataQuery”. I could, of course be any saved query.
          Dim rs As DAO.Recordset
          Set rs = db.OpenRecordset("TheDataQuery")

      '  Define and Open Excel as an Excel Object. This is just opening Excel itself without a workbook.
          Dim objXLApp As Excel.Application
          Set objXLApp = Excel.Application


      ' Create and Open an Excel "Workbook" object. The workbook (that is Excel file) must exist in the place given in the .Open statement.
              Dim objXLBook As Excel.Workbook
          Set objXLBook = objXLApp.Workbooks.Open _
              (conPath & "\Export_ForNext.xlsx")

      ' Create and Open a "Worksheet" object. The tab, in this case named TheDataSheet, must also already exist.
          Dim objResultsSheet As Excel.Worksheet
          Set objResultsSheet = objXLBook.Worksheets("TheDataSheet")


      ' Create a "Range" object.  This is a named range in Excel which defines a certain group of cells. The actual values in the Range argument depends on the particular spreadsheet, but it should be at least as wide, and longer than the anticipated recordset.
              Dim rng As Excel.Range
          Set rng = objResultsSheet.Range("A2:F45")

      ' ****EXPORT CODE GOES HERE ****

      ' Save and close spreadsheet
              objXLBook.Save
          objXLBook.Close
             

          ' Lastly, I’ll destroy all the objects I created.
          Set db = Nothing
          Set rs = Nothing
          Set objResultsSheet = Nothing
          Set objXLBook = Nothing
          Set objXLApp = Nothing

      So far, I haven’t actually done anything useful. I’ve only created and destroyed a bunch of objects. The real work happens in the EXPORT CODE GOES HERE section. The code will vary depending on the method you use and the whether I want to over-write the data or append the data to the end of the existing data.

      Do…While Method

      I’ll start with the Do…While method.  This method “pushes” the data from Access into Excel. It does this by opening and walking through a recordset in Access and setting specified cells in the Excel sheet.

      Overwrite Data
      (Enter the following into the  ****EXPORT CODE GOES HERE ****)

      '  **************************************
      '  Set beginning row and column numbers
          RowVal = 2
          ColVal = 1

      '  Delete any existing data
          rng.ClearContents

      '  Write data from Access query to Spreadsheet
          Do While Not rs.EOF
              objResultsSheet.Range(Cells(RowVal, ColVal), _
                  Cells(RowVal, ColVal)) = rs!phys_id
              objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
                  Cells(RowVal, ColVal + 1)) = rs!spec_code
              objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
                  Cells(RowVal, ColVal + 2)) = rs!Export_Date
              objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
                  Cells(RowVal, ColVal + 3)) = rs!Drug1
              objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
                  Cells(RowVal, ColVal + 4)) = rs!Drug2
              objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
                  Cells(RowVal, ColVal + 5)) = rs!Drug3
              RowVal = RowVal + 1
              rs.MoveNext
          Loop
      '  **************************************

      Append Data
      (Enter the following into the  ****EXPORT CODE GOES HERE ****)

      '  **************************************
      '  Set beginning row and column numbers
         RowVal = 2
        ColVal = 1

      ' Find the last row of data in the Excel sheet
        Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
              RowVal = RowVal + 1

          Loop

      ' Write data from Access query to Spreadsheet
          Do While Not rs.EOF
              objResultsSheet.Range(Cells(RowVal, ColVal), _
                  Cells(RowVal, ColVal)) = rs!phys_id
              objResultsSheet.Range(Cells(RowVal, ColVal + 1), _
                  Cells(RowVal, ColVal + 1)) = rs!spec_code
              objResultsSheet.Range(Cells(RowVal, ColVal + 2), _
                  Cells(RowVal, ColVal + 2)) = rs!Export_Date
              objResultsSheet.Range(Cells(RowVal, ColVal + 3), _
                  Cells(RowVal, ColVal + 3)) = rs!Drug1
              objResultsSheet.Range(Cells(RowVal, ColVal + 4), _
                  Cells(RowVal, ColVal + 4)) = rs!Drug2
              objResultsSheet.Range(Cells(RowVal, ColVal + 5), _
                  Cells(RowVal, ColVal + 5)) = rs!Drug3
              RowVal = RowVal + 1
              rs.MoveNext
         Loop
      '  **************************************

      CopyFromRecordSet Method

        The CopyFromRecordSet method is not an Access function.  It’s actually a method built into Excel. However, because I’m using Office Automation, I can use this Excel method to pull data from Access to Excel.  Using this method, you don’t need to worry about row position or column position.

        Recall that above, I defined a recordset object based on the query “TheDataQuery”,
            Set rs = db.OpenRecordset("TheDataQuery")
        and I also created a range object
            Set rng = objResultsSheet.Range("A2:F45")

        The CopyFromRecordSet method allows me to drop the defined recordset (rs) into the defined range (rng).  It requires an argument for maximum rows and columns (45 and 10 below). I usually just define an area bigger than I could possibly need.

        Overwrite Data
        (Enter the following into the  ****EXPORT CODE GOES HERE ****)

        '  **************************************
        '  Set beginning row and column numbers
            RowVal = 45
            ColVal = 10

        ' Delete any existing data
            rng.ClearContents
           
        ' Write data from Recordset to Spreadsheet range
            Call rng.CopyFromRecordset(rs, RowVal, ColVal)
        '  **************************************


        Append Data
        (Enter the following into the  ****EXPORT CODE GOES HERE ****)

        '  **************************************
        '  Set beginning row and column numbers
            RowVal = 1
            ColVal = 6

           
        ' Find the last row of data
            Do While Not objResultsSheet.Cells(RowVal, ColVal) = Empty
                RowVal = RowVal + 1
            Loop

               
        ' Create a "Range" object
            Set rng = objResultsSheet.Range(Cells(RowVal, 1), Cells(RowVal, 1))
           
        ' Write data from Recordset to Spreadsheet range
            Call rng.CopyFromRecordset(rs, RowVal, ColVal)
        '  **************************************

        You can find this code all pulled together in a sample database from my website:

        Download Sample:

        ExportToExcel_OfficeAutomation