Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ Web Based Version Of An Access Report

Posted by: muse43 Oct 3 2018, 04:09 PM

I hope this is not too general but I'm looking for some advice regarding the best path forward...

I often get asked to migrate an Access database to an online platform. Sometimes it's just the backend that's migrated in which case things are quite straight forward. Forms too can generally be pretty well replicated on a web page.

However, where I am running into trouble (at least in thinking about it as I have yet to try this) is to recreate reports on a website. In particular, grouping in Access is an extremely useful feature that I don't know how I'd replicate if I were trying to display the same data on the web. I often have very complicated reports and am wondering what other people do in these cases?

I assume there are a million ways to go about this including buying a number of programs that might help... I don't necessarily need a step by step guide or an exact software recommendation but would just like to know if there are simple ways of doing this that I haven't considered yet. Or should I be shopping for software that can do this. If so, any recommendations are of course still welcome.

I'm basically just stumped in terms of how to start even thinking about this problem... right now all I can think is trying to convince the user to keep all the reporting in Access (with a cloud based backend).

Any thoughts on this would be much apprecaited... thanks!!!

Posted by: AlbertKallal Oct 4 2018, 12:31 AM

Well, remember, just like developing for the desktop, and if you are using FoxPro for your reports, then me suggesting to use Access likely is not useful advice to you.

And when you develop for the web, then this is the SAME issue.

In other words, what set of tools and software you are using, have adopted, and have learned means you going to be limited to those software tools you choose.

So if your web hosting is say Linux, and you picked the LAMP stack (Linux, Apache, MySQL, and PHP), then me suggesting say some .net tools will not work, don’t run, and you using a 100% different set of tools?

So just like the desktop, the SAME approach and advice will apply to the web hosting and set of development tools you picked.

If you using say LAMP, then I can’t suggest the report view object, can I?

So without any information as to what set of tools you spent all your time investing in, and learned, then it really a shot in the dark.

In my case, I don’t use the LAMP stack, but use the stack.

For the super solution is of course SQL reporting services. They are fantastic, and what is really nice is when a report is displayed, you can click on a button to grab a local copy of the report in word, Excel, or PDF.

MOST amazing is the report when pulled to your local PC in those 3 formats is rendered PERFECT (and I mean perfect!!! – So pulling into word, or Excel can occur without you having to write any extra code. This is by far and away the best reporting solution. Of course this assumes your web hosting is running SQL server reporting services. The option to pull and save the report as PDF is also great, since then any printing options are eliminated. And the printer "add-in" if you so choose will give your really nice printing options - even from a browser.

Such hosting sites are cheap – say $8 per month. And if available, then SQL reporting services can be added for about another $4-$7 per month. So if you using the stack, this is certainly my first choice.

If the web provider does not have SQL reporting services, then you can setup your own server, and have it “connect” to the database hosted by your web site – but this assumes external connections are allowed – often they are not.

If your web hosting does not have SQL server reporting services, then the next choice is to use the “report viewer” control. This is “ok”, and has some nice options, and has the added advantage of running on any compatible hosting plan you have. So it has grouping and is not great, but decent for a lot of reports one would want.

So I can only give you advice for if you are using the stack for your development tools.

Another increasing common choice is that web hosting sites offer crystal reports for the web. I not used, or tested this option, but it also quite common as a choice.

If you using a different web server, say Linux, then of course my suggesting in regards to is VERY much the same as telling you to install access on a Mac computer (that will not work).

So what software tools, and what software you install on your web server is going to be limited to the platform you are using.

I can’t suggest to write code on your web site unless of course you using (I currently use because the code I write is very much the same as VBA code that I spend so much time writing in Access). So I went with, because I am familiar with Microsoft programing languages – especially

So your choices of software that to adopt or use or install on your web hosting system is really not any different than your choices on your desktop computer.

Software ONLY designed for your particular web site and what operating system it uses is your ONLY option.

You can’t out of the blue install Mac software on a windows computer, anymore then installing Linux and apache compatible software on say an hosted site.

So you have to share what development stack and set of tools you adopted and what code systems you are using for your web site – since ONLY software designed for your particular operating system that the web site uses will work for you.

So we need more information as to what kind of software system and platform you are using for your current web site.

So my suggestions to use SSRS, or say the reportvier will be of no use to you if you’re programming tools and web hosting is Linux based.

So your software choices are limited to what operating system your current web hosting is using.

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Posted by: projecttoday Oct 4 2018, 06:28 AM

muse, can you clarify if by report you mean a printer-friendly report or an online display (or both)?

Posted by: JonSmith Oct 4 2018, 06:31 AM

Albert, I see alot of posts from you these days that I find super hard to read because of the extra line break between each line rather than having paragraphs.
If this is a personal choice of yours then no problem, each to their own and I'll say no more on the matter.
If its to get around issues with line breaks here on UA disappearing and extra ones needing to be inserted, as quite a few others have experienced, I discovered the fix for that a few months ago and would be happy to share it with you so you can do normal paragraphs again!

@muse43 (sorry to be abit off topic, I cannot read Alberts response but hopefully it has the answers you need!)

Posted by: cheekybuddha Oct 4 2018, 09:46 AM

I actually find it easier to read than a dense paragraph of text!!

Now, deciphering what Albert is actually saying ...

(only kidding wink.gif ohyeah.gif )


Posted by: muse43 Oct 4 2018, 01:47 PM

Albert, thanks so much for taking the time to share all that very helpful information!

I have the disadvantage of not knowing a lot about the different languages out there but the advantage that I'm at the point where I can still choose the best path forward based on current technology and what I already DO know... this post is helping me with that decision.

I am hobby developer who has dabbled in HTML going back to the early 90's... BUT I am by no means a "real" programmer... at least not yet. I mean, I've built a couple little sites but still have lots to learn.

More recently though I've been making good progress. I got really into MS Access and VBA. That's where it all started again for me. That led me to want to learn different ways of getting Access applications online including fully rebuilding web based front ends that would replace Access entirely. To that end, I went and learned CSS, Javascript, PHP and more SQL and HTML (both of which I was already familiar). I build my online databases on Azure using their cloud based SQL Server (and a website).

So I think I'm generally a Microsoft guy... and that's probably where I want to keep going. I build applications for Offices/businesses generally for Admin type purposes so I think sticking to boring old Microsoft stuff does the job for me. Also, as I said, I'm not a "real" programmer so things like Linux to me are alien (except for all the people I know who have raved about it to me over the years).

I'm thinking my next step in web development is to take a look at visual studio. Right now I do all my coding without any "helper" programs - I just use Sublime text for everything. From the little I've seen though it seems Visual Studio could be helpful for someone like me...

So while that may not tell you what I'm currently using (since I'm not really using any technologies other than those languages) maybe it helps you offer some advice as to where I should take my learning next? I really like the sound of what you are saying about and its ability to work very seamlessly with MS Office products - which I use a lot of.

I guess I'm just so used to being able to put a report together in Access that I can't even really imagine other ways of doing it or what type of options there are - or even if there are any - although now you've made clear there are.

The only thing I've done that comes close (and maybe this is actually good enough - not sure) is I've used PHP to do something similar to Access's grouping by using a Query where the grouped field is just included (and repeating throughout the dataset) and then I use a loop in PHP to check if that field has changed. If it has I spit out another instance of the header of my table and then keep going... this works but seems pretty limited still... I just figured there's got to be a million other ways to do this... so many in fact that I felt overwhelmed with my choices. So far, this has already been a great help though just to put some perspective on the issue...

As for what I want these reports to do... ultimately I'll have applications where I'll need to actually get them ready for printing so I think that's where I need to go. My PHP method I described above does an OK job (along with some CSS to get my margins and sizes right) for display and even to some degree for printing but again, still seems quite limited (and cumbersome).

Again, thanks so much for your insight guys! Really helpful...

Posted by: AlbertKallal Oct 5 2018, 01:26 AM

Ok, good stuff – we doing ok here!

My only point was that “often” we see people asking about what tools to use, but I just wanted to point out that if your web site don’t support say SQL server, then you not be able to write SQL server code.

And if the web site don’t support say .net, then you can’t write your code in c# or say

And if your web server don’t support PHP, then you can’t write your code in PHP.

For some reason, the above issue is often overlooked. So it was just a bit of a warning flag when you asked for some tool advice, but LEFT OUT what kind of web server you are using.

I mean, this would be like someone asking for advice on how to use Access. They might come to UA and ask questions and get GREAT answers (because UA has great answers and great people!!!).

However, after all is said and done, we THEN find out the poster asking questions has a Mac computer, and can’t use Access!!

So I just want to nail down the important concept that a web server is a just like a desktop computer. It either running windows, Mac, or Linux. And what OS and software is available for that web server is going to dictate your choice of tools.

So keep the above in mind – you can’t just out of the blue choose “xxx” as some programming language unless the web site computer supports that programing language.

I had suggested that SQL reporting is a real “Cadillac” solution, but the built in web reporting controls in Visual Studio is quite nice and don’t need reporting services.

So a client render version of the report viewer can be used. It is free, and will work on any web server (assuming compatible system).

I went and learned CSS, Javascript, PHP and more SQL and HTML (both of which I was already familiar). I build my online databases on Azure using their cloud based SQL Server (and a website).

Well, that means you are already miles ahead of me. I was not willing to spend the time to learn all those new fancy web programing systems like you. That is a great start!!!

I am just an Access developer that can drag + drop “things” onto a form. And then I can write some VB code attached to those things. So I not near as far as you in this regards!

Web vs Access?

Let’s have some fun!!

Let’s say in Access we want to place a button on a form, and when we click on the button we display 1 to 10 in a text box.

This takes what, 3 minutes of our time to make.

We get this form:

So in above, when I click on button, 1 to 10 was displayed.

And the code behind the button is this:

        Dim i As Integer
        Dim str As String

        For i = 1 To 10
            str = str & i & vbCrLf
        Next i

        Me.Text1 = str

Ok, now, I don’t know all those fancy web systems and fancy things like PHP.

However, I do know VBA, and it really much the same as

So, let’s now fire up Visual Studio, and build the above as web application.

So I create blank web form.

I drag + drop a button.

Drag + drop a text box.

Quite much done.

At this point everything I done so far is really what I would do like in Access.

Now, let’s add the code to display the 1 to 10. Well, let’s just cut + paste our VBA code from Access into the Visual Studio code editor.

The code is thus this:

        Dim i As Integer
        Dim str As String

        For i = 1 To 10
            str = str & i & vbCrLf
        Next i

        Me.Text1.Text = str

And here is the result:

So you can see this is really not at all different of a process then Access.

Ok, let’s try a report.

Assume we book holiday tours to Cities and Hotels.

I want a report and system to display the City, and then the list of hotels along with passenger totals for my tour company.

Assuming I have the nice query already built?

Well, we would launch the Access report wizard. Toss in some grouping, and move things around.

The result is like this:

Now, we could add some controls to the top (say a combo box to filter or select the hotel. So we would create a form, drop in a combo box, and then drag + drop in the report. (I really should do this for this demo, but, next time).

Ok, to make the same in

Well, again I just create a blank web page, and then drag in the report view control.

Follow the wizards.

The result is this:

So, I did not need to write all that looping code, and I did not even have to build up code for the totals at the bottom.

And the web report version actually took less time than access, and it even gave me the +/- to expand groups.

Eg this:

I should probably “expand” this example in Access and add a combo box to the top (let user select hotel).

And then do the same in the web.

They both would just have a bit of VBA or code.

Anyway, I hope the above gives you some ideas here.

So I really don’t believe much in “hand” coding things today.

I did not in fact write ANY code for the access or the web example reports here. (so the grouping and the "grand" total was done for me (in both examples).

To be fair, in Visual Studio, you can and will often drop down into the actual “markup” code, but I tend to avoid this as much as possible.

I can only suggest that be it for the desktop, or the web, you really do want some great tools – because life is too short, and I would never have time to post on UA if I hand coded everything I do!!!

Last but not least?
While the above really was easy in, there is a learning curve - don’t want to sugar coat this, but this was very easy for me to do this in

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Posted by: muse43 Oct 18 2018, 09:44 AM

Albert, this was INCREDIBLY helpful, thank you!!

You have helped me chart my course... time to start using some software to help code.. I'm sold!