locked
Storing Access Reports in SQL Server RRS feed

  • Question

  • Hi,

     

    We are using Access 2003 as a front end and SQL Server 2008 as a back-end.  We also have more than 50 reports which most of them run daily. 

    The current task is to store those reports on-the-fly (as they are printed) in Access to some repository place.  So that it can be retrieved and re-printed if needed.

    I've never done anything similar to this, so my question is general really.  Is this feasible?  And if yes in what format would be best to store them in SQL tables? 

     

    Thank you

    Wednesday, July 13, 2011 4:30 PM

Answers

  • You'd actually be better off creating a VB script that prints a copy of the report as a pdf (or whatever format you want to use for archiving purposes) and saving it outside the database itself on a server. From there you can store the file path location to the file in the database.
    Jason Long
    • Proposed as answer by Naomi N Wednesday, July 13, 2011 5:15 PM
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 6:25 PM
    Wednesday, July 13, 2011 5:11 PM
  • Alex,

    This is possible. The best way is to save your reports in excel format. Following is the VBA function to get report output in excel format:

     

    Function OTXLOut(sql As String)

    CurrentDb.QueryDefs("Bolvanka").sql = sql
    DoCmd.OutputTo acOutputQuery, "Bolvanka", acFormatXLS, "C:\Test.xls"

    End Function


    You can call this function and get output in excel format. Hope this helps.

    Reference: http://www.zmey.1977.ru/Access_To_Excel.htm

     


    Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 6:25 PM
    Wednesday, July 13, 2011 5:46 PM
  • Yes, you have the right idea in terms of how to store the files.

    So... Lets say you have a folder out in a network share that you want to use for storing all of these generated reports... Let's call it Customer_Reports and it's located on the the "DocServ" server.

    The file path would be \\DocServ\Customer_Reports\MyFileName_20110713

    Now you have 2 options... If you're going to use the same folder for ALL reports then there is no need to store the full file path with every record in the database. It can just be assumed and you only need to store the actual report name.

     

    As for the table structure that will allow to actually store that info... Well depends on how they are associated with the rest of your data.

    So... If all of these reports are based on a one customer at a time and you'd like to be able to associate a given report to a specific customer you could use something like this...

    CREATE TABLE CustomerReports (
    	ReportID INT IDENTITY(1,1) NOT NULL Primary Key)
    	CustomerID INT NOT NULL, 
    	ReportTypeID INT NOT NULL, --Refers to the report template
    	ReportDate DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP, --Date & time the report was executed
    	ReportFileName VarChar(50) NOT NULL) --The actual name of file being stored
    

    This would allow you to have a 1 to many relationship between [Customers] and [CustomerReports].

    If, on the other hand the reports are more generalized and only a portion of the reports will pertain to specific customers, you could use something like this...

    CREATE TABLE AllReports (
    	ReportID INT IDENTITY(1,1) NOT NULL Primary Key)
    	ReportTypeID INT NOT NULL, --Refers to the report template
    	ReportDate DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP, --Date & time the report was executed
    	ReportFileName VarChar(50) NOT NULL) --The actual name of file being stored
    

    and then use a separate table to tie those customer specific accounts back to customers...

    CREATE TABLE CustomerReports (
    	ReportID INT NOT NULL,
    	CustomerID INT NOT NULL,
    	Primary Key(ReportID, CustomerID))
    



    Jason Long
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 10:58 PM
    Wednesday, July 13, 2011 10:05 PM

All replies

  • You'd actually be better off creating a VB script that prints a copy of the report as a pdf (or whatever format you want to use for archiving purposes) and saving it outside the database itself on a server. From there you can store the file path location to the file in the database.
    Jason Long
    • Proposed as answer by Naomi N Wednesday, July 13, 2011 5:15 PM
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 6:25 PM
    Wednesday, July 13, 2011 5:11 PM
  • Alex,

    This is possible. The best way is to save your reports in excel format. Following is the VBA function to get report output in excel format:

     

    Function OTXLOut(sql As String)

    CurrentDb.QueryDefs("Bolvanka").sql = sql
    DoCmd.OutputTo acOutputQuery, "Bolvanka", acFormatXLS, "C:\Test.xls"

    End Function


    You can call this function and get output in excel format. Hope this helps.

    Reference: http://www.zmey.1977.ru/Access_To_Excel.htm

     


    Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 6:25 PM
    Wednesday, July 13, 2011 5:46 PM
  • Thanks for your replies.  I have functionality already in our Access which either outputs the reports to pdf or excel formats.  What I am not sure is the best way of storing them for later usage. 

    Jason, you've mentioned about storing pdf reports in some folder.  And then storing into SQL Server only the path of those files.  Is that correct? 

    If this is correct then my followig question would be: If for example we have customer's accounts.  And over time there were several reports produced, say one for changin their contact, one for congratulating them on something.  Then when a user wants to re-print one of the reports, should the user be displayed with a list of all reports saved so far OR say, creating subfolders for report types?

     

    I just can't think of the best solution knowing that there several ways of doing this.

     

    Thanks again

    Wednesday, July 13, 2011 6:24 PM
  • Actually having thought through saving pathes in a database and files in some repository folder, it not a bad idea at all.  This will eliminate looping and searching the required file in those folders.
    Wednesday, July 13, 2011 6:29 PM
  • Yes, you have the right idea in terms of how to store the files.

    So... Lets say you have a folder out in a network share that you want to use for storing all of these generated reports... Let's call it Customer_Reports and it's located on the the "DocServ" server.

    The file path would be \\DocServ\Customer_Reports\MyFileName_20110713

    Now you have 2 options... If you're going to use the same folder for ALL reports then there is no need to store the full file path with every record in the database. It can just be assumed and you only need to store the actual report name.

     

    As for the table structure that will allow to actually store that info... Well depends on how they are associated with the rest of your data.

    So... If all of these reports are based on a one customer at a time and you'd like to be able to associate a given report to a specific customer you could use something like this...

    CREATE TABLE CustomerReports (
    	ReportID INT IDENTITY(1,1) NOT NULL Primary Key)
    	CustomerID INT NOT NULL, 
    	ReportTypeID INT NOT NULL, --Refers to the report template
    	ReportDate DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP, --Date & time the report was executed
    	ReportFileName VarChar(50) NOT NULL) --The actual name of file being stored
    

    This would allow you to have a 1 to many relationship between [Customers] and [CustomerReports].

    If, on the other hand the reports are more generalized and only a portion of the reports will pertain to specific customers, you could use something like this...

    CREATE TABLE AllReports (
    	ReportID INT IDENTITY(1,1) NOT NULL Primary Key)
    	ReportTypeID INT NOT NULL, --Refers to the report template
    	ReportDate DateTime NOT NULL DEFAULT CURRENT_TIMESTAMP, --Date & time the report was executed
    	ReportFileName VarChar(50) NOT NULL) --The actual name of file being stored
    

    and then use a separate table to tie those customer specific accounts back to customers...

    CREATE TABLE CustomerReports (
    	ReportID INT NOT NULL,
    	CustomerID INT NOT NULL,
    	Primary Key(ReportID, CustomerID))
    



    Jason Long
    • Marked as answer by Alex Teslin Wednesday, July 13, 2011 10:58 PM
    Wednesday, July 13, 2011 10:05 PM
  • Wow, Jason, thank you so much for a very detailed explanation and an example.  I didn't expect this, thank you.

     

    I think all the mysteries are solved now and just leaves me to implement the whole idea.

     

    Thanks again, much appreciated.

    Wednesday, July 13, 2011 10:58 PM
  • No problem & best of luck!
    Jason Long
    Wednesday, July 13, 2011 11:06 PM