Solved

Importing remote reports to client frontend

Posted on 2014-09-29
9
200 Views
Last Modified: 2014-10-14
A frontend A2013 application is distributed to clients.
It connects to tables and views at a remote SQL Server. No problem.

However, each client may have one or more reports with a custom layout. These should be imported to the frontend.
This is quite easy if you have a local accdb containing the reports. But would there be another method where you wouldn't need to distribute a local accdb with the reports?

You could download a file, text or accdb, and import the reports from this, but I would prefer a method where a local file is not needed. Any ideas?

/gustav
0
Comment
Question by:Gustav Brock
  • 4
  • 4
9 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40350440
You can use the SaveAsText and LoadFromText methods.  Here's sample code with hard coded paths and object names.  You would need to make it generic.

Public Sub ExportReport()
Dim strPath As String
    strPath = "C:\Data\Work\SafetyMarking\Report_"
    Application.SaveAsText acReport, "rptInventoryRunningSum", strPath & "rptInventoryRunningSum" & ".txt"
End Sub

Public Sub ImportReport()
Dim strPath As String
    strPath = "C:\Data\Work\SafetyMarking\Report_"
    Application.Application.LoadFromText acReport, "rptInventoryRunningSum", strPath & "rptInventoryRunningSum" & ".txt"
End Sub

Open in new window


Due to the problems of keeping the FE updated, I never allow users to create their own objects in production applications.  If they need to do custom reporting, I either give the queries they can export to Excel and manipulate or I have them create their own database linked to non-updateable views (SQL Server) or for Access, I create non-updateable queries and hide the linked tables so all they see are my queries.  The queries join to a 1 row table to make a Cartesian Product so they are not updateable.
0
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 total points
ID: 40350465
Why not just include the reports in the front-end db? You could have logic to decide, based on user name, which report to open. If you don't have a user name you could create a sign on form to collect it. If you need help distributing a front-end database file to your clients, see our free Application Starter utility: http://www.peterssoftware.com/aps.htm
0
 
LVL 49

Author Comment

by:Gustav Brock
ID: 40350680
Pat, the users will not be able to create the reports themselves. We will create them but need to distribute them later.

However, the frontend may also be updated. If so, it will not contain the custom reports, and when arriving at the client, it has to be a simple task to import the custom reports.

Peter, we wish to have a single frontend to distribute - actually for the clients to download at wish. I thought of copying all custom reports in a "hidden" way to the frontend, but they may count to hundreds, so I left that idea.
Thanks for the link to the Starter utility. I'm lazy so it may come in handy.

By second or third thought I thought of linking directly(!) to a master of an accdb database containing all reports and then import those selected. I know - across a WAN and all that - but clients typically have 20-100 Mb/s lines, and this would be reading only, so it might work out. Any comments on that?
We will have a local license file, so I could write to that for later reference which reports should be imported when an updated frontend arrives.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40350951
To implement my suggestion, you would create a form that browses to a folder and then imports each report it finds there using the LoadFromText method.  Of course the file names have to be the same as the report names or you'll have to come up with some way of coming up with appropriate names.  The folder can be on a thumb drive or a network drive.  You could probably even use an FTP site to distribute the reports.

I always distribute complete FE's.  For the products that are sold to the public, I have to use my security scheme to determine which objects are licensed and which are not.  If that is your situation, you might do something similar.  Keep a table of all reports with a licensed flag that gets set on the installation of a license key.  Then your menu forms would only display the reports that are licensed.

I also distribute the app as an .accdr which prevents me from importing objects so I don't really have any option but to include everything and use the license key to sort it out.  Managing multiple FE's at my end is way too complicated.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Author Comment

by:Gustav Brock
ID: 40351013
Licensing is not so tough for this application. Access is granted corporate wide (SMBs only) and access to a central database can be controlled.

I like the idea of running an accdr file but if this prevents the import of a report object (by code), we can't use it as it stands. Handling individual frontend is neither an option for us.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40351087
Usually applications that are sold to the public are not distributed with source code.  If you need to stick with the import option, you have to distribute source code because you can only do the import/export from an .accdb.  This is a case where ULS is sorely missed.  It certainly wasn't the best security and it was very confusing but it sort of worked.
0
 
LVL 49

Author Comment

by:Gustav Brock
ID: 40351701
> you can only do the import/export from an .accdb

You are right. I forgot that for a moment.
That excludes an accde except if we choose to handle individual frontends which would be troublesome, though not impossible; clients are only counted by the hundreds.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40352192
As I mentioned earlier, I found the best solution for me was to include ALL objects in the FE and then use the license to expose what the client bought.  Then I distributed a new FE when I released an update or fix.  The only other solution that might work is to have a separate database that is distributed as an .accdb that contains ONLY reports.  You could then update that database but you'll also run into trouble if your client only has the runtime installed.  Even though the database is an .accdb, I'm pretty sure the code won't work when the runtime engine is running the app so check first before you commit yourself to this path.  The whole idea behind the runtime is to not allow design time access to ANYTHING.
0
 
LVL 49

Author Closing Comment

by:Gustav Brock
ID: 40380550
Thanks for the ideas. At least I know there isn't some unknown trick for this situation.

It seems that only a fraction of clients will have custom reports. Thus we may end up using an automated system where clients "order" a frontend for download, then the FE is generated including custom reports if any.

/gustav
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now