Censorship by Access-Programmers Forum

[Frothingslosh]

And believe it or not, sometimes I wish I COULD forget stuff more easily.

Then I remember that whole thing about being careful what you wish for.

 

[GinaWhipp]

Just read your tag lines and was thinking, shouldn't this say...

If this were a real emergency, you would NOT have been informed, as we would have fled screaming in terror.

 

[Frothingslosh]

LOL Probably. I just put it in as I actually told it to someone once.

 

[The_Doc_Man]

And believe it or not, sometimes I wish I COULD forget stuff more easily.

Whereas I have the worst of both worlds in remembering threads. Like the most recent one about "System Resources Exceeded" where I knew for an absolute fact that we had explored this and I had participated, but I was damned beyond redemption to remember the topic and the forum's search didn't help because the word "Resources" wasn't in the title. So it nagged me for the better part of an hour until I remembered that Ridders (Colin) had participated. Since he's relatively new compared to some of the ... I was going to say old-timers but let's make that "mature members." So a search on HIS posts was easier.

 

[isladogs]

Whereas I have the worst of both worlds in remembering threads. Like the most recent one about "System Resources Exceeded" where I knew for an absolute fact that we had explored this and I had participated, but I was damned beyond redemption to remember the topic and the forum's search didn't help because the word "Resources" wasn't in the title. So it nagged me for the better part of an hour until I remembered that Ridders (Colin) had participated. Since he's relatively new compared to some of the ... I was going to say old-timers but let's make that "mature members." So a search on HIS posts was easier.

Always glad to help an old timer like yourself Doc .... although I think I am roughly the same level of 'maturity' as you.

Speaking of which, I usually use advanced search and then 'search entire posts'.
If you know you've participated also enter your own username or look for the results marked with a blue arrow which indicate your participation.
In this case an advanced search for system resources listed the thread by user cedartree

 

[isladogs]

And believe it or not, sometimes I wish I COULD forget stuff more easily.

Then I remember that whole thing about being careful what you wish for.

Of course if your wish came true, you wouldn't remember what you had wished

 

[Frothingslosh]

"That's what you wished for the first time," the genie said before vanishing.

 

[The_Doc_Man]

Speaking of which, I usually use advanced search and then 'search entire posts'.
If you know you've participated also enter your own username or look for the results marked with a blue arrow which indicate your participation.

In the search I referred to, the problem was that I DID use advanced search, DID search for my username, DID search "entire posts", and DID have a couple of keywords.

What I got was eleven pages of titles, but since I still didn't remember the original question and the keyword wasn't in the title, I became somewhat daunted by the task of stepping through all eleven pages. That's when I remembered that Ridders had contributed and so the same search but using HIS name yielded a shorter list.

I eventually found the thread I wanted and posted a link to it. It just took me a while because the keywords I used were used too often in other threads over many years, and (not to brag) I'm listed as one of the site's top ten contributors, something over 11K posts in total, so a LOT of posts showed up.

 

[The_Doc_Man]

Back to NFK's rant,

And you touched on the 2nd part of my situation: M$ Office already resides on every computer I touch at work and it would take an act of God to get another COTS sofetware installed for a few reasons, first and foremost the fact that it is already bought and paid for.

NFK, you may be right about Access shortcomings, or you might be wrong. I tend to disagree with a lot of your posts as being a matter of interpretation, not raw and pure fact. I.e. your pieces are more often Op-Ed than News.

However, NG has stated a basic raw fact - MARKET PENETRATION - as a reason why your "move away from Access" rant falls on deaf ears. We take the practical view that while something better might indeed exist, the bottom line (literally, for corporations) includes that with a full Office suite, they have already paid for the product and will not be throwing their investment down the drain. It just won't happen because it makes ZERO POINT ZERO business sense to do it.

 

[Vassago]

You would think so, but my company discourages Access use whenever it comes up. I'm mostly developing in SQL Server and SSRS these days.

I still don't see smaller business moving away from Office though.

 

[NauticalGent]

You would think so, but my company discourages Access use whenever it comes up. I'm mostly developing in SQL Server and SSRS these days.

Agreed. The IT department I am forced to use actually said to me “Access? NOBODY uses Access anymore!”

Not taking the bait, I calmly asked them “So what is everybody using then?”
“Sharepoint!” he replied...

Me: “Perfect, so you guys have a Sharepoint developer on staff who can work with me to replace my archaic application...?”

“Well, no...”

I do have access to SQL and SSRS however, in fact I am using SQL as a BE now that Access and Sharepoint do not play nice together anymore.

Are you saying SSRS can be use to make interactive forms and other application objects?

 

[Vassago]

Agreed. The IT department I am forced to use actually said to me “Access? NOBODY uses Access anymore!”

Not taking the bait, I calmly asked them “So what is everybody using then?”
“Sharepoint!” he replied...

Me: “Perfect, so you guys have a Sharepoint developer on staff who can work with me to replace my archaic application...?”

“Well, no...”

I do have access to SQL and SSRS however, in fact I am using SQL as a BE now that Access and Sharepoint do not play nice together anymore.

Are you saying SSRS can be use to make interactive forms and other application objects?

Unfortunately and fortunately not. It's an incredible tool for reporting though. Although it would be nice to have more basic features, such as a scheduler with the flexibility of SQL jobs since that's what it uses for snapshots anyway. Your options are very limited there. :banghead:

 

[AccessBlaster]

Perfect, so you guys have a Sharepoint developer on staff who can work with me to replace my archaic application...?”

Isn't that an oxymoron, using one archaic program to fix another archaic program.

 

[The_Doc_Man]

Vassago, I know exactly why the company discourages it. Too many designs are poorly implemented so that the network load brings too much stuff across the net. I managed to get by because our first couple of versions were on a branch of the network that was DOG slow, so I had to optimize the crud out of the queries by indexing and by judicious use of extreme table-shortening methods. So when we FINALLY got it to a local segment, it blazed and didn't represent a load.

Been there, done that, wore that T-shirt.

 

[Mark_]

Unfortunately and fortunately not. It's an incredible tool for reporting though. Although it would be nice to have more basic features, such as a scheduler with the flexibility of SQL jobs since that's what it uses for snapshots anyway. Your options are very limited there. :banghead:

A couple decades ago (Scary when I have to write that out) I wrote a report scheduling and printing system that used Crystal Reports for the report generation. Maybe you could do something similar in ACCESS? Have a little app that sit there, reads in a table for new entries, and kicks off jobs based on what was requested?

I'm not familiar with SSRS, but I'm hoping there is some way you can get it to play with ACCESS.

 

[Minty]

I have a set of routines that fire out HTML table reports in emails directly from SQL server.

It's great for basic reporting, but if you want to send a stock list of 100 + rows it gets very difficult to justify not trying to send an Excel spreadsheet. Then you need much more complex set of tools and permissions.

I thought you could schedule a "subscription" to a SSRS report, that would email it out?
Or does that only work within your domain? (I did look at this ages ago, but have slept since...)

 

[Vassago]

I have a set of routines that fire out HTML table reports in emails directly from SQL server.

It's great for basic reporting, but if you want to send a stock list of 100 + rows it gets very difficult to justify not trying to send an Excel spreadsheet. Then you need much more complex set of tools and permissions.

I thought you could schedule a "subscription" to a SSRS report, that would email it out?
Or does that only work within your domain? (I did look at this ages ago, but have slept since...)

You can schedule subscriptions from within SSRS, but the scheduling is not as flexible as the SQL jobs those subscriptions and snapshots use.

 

[Galaxiom]

It's great for basic reporting, but if you want to send a stock list of 100 + rows it gets very difficult to justify not trying to send an Excel spreadsheet.

Excel has so many presentation facilities that it is hard to go past. Gradient fills in Conditional Formatting have been a big hit with my users as they can be used to make what is effectively a horizontal bar graph behind the data right there in the column. Very colourful and easy to read. Users are generally comfortable with Excel.

My regular reports are Excel Macro Enabled Templates with data connections to Stored Procedures or Views on the server. The templates are stored in a central location so the users can generate reports whenever they want.

A user form allows the report to be customised, primarily using a combobox with preconfigured date ranges feeding parameters to the Stored Procedure in the data connection. The combo defaults to the date range most appropriate to the report such as "Last Month". Basically users double click the template then hit save and there is the new current report like magic.

Pivots based on the data are automatically refreshed after changes to the selections. The connection properties are configured to replace all data and preserve the formatting.

Templates can be copied and formatted however users want to produce new templates. A button is provided to generate new tabs and pivots based on the data. They just have to configure the pivot.

During Save the code detects whether the template or the derived document is being edited and behaves accordingly, warning if the template is being edited or offering a file name based on the template name and the selected date range. Derived documents drop the data connections and associated macros, keeping a snapshot of the loaded data.

Columns added to the View or Stored Procedure are automatically included in the data without altering the Excel documents. This particular aspect has bewildered some users who had assumed that I would have to replace the template file when they requested extra columns.

 

[Minty]

That's a really neat approach, my problem with using templates linked to the server is that I'd need to then copy the whole spreadsheet over to a unlinked version for distribution to an end customer, I don't want connection strings etc. stored in the spreadsheet. I also automate the emailing externally of regular reporting, which is much easier if the files are stored locally to SQL. Which is good / bad, as I wouldn't give access to the server storage locations to anyone, but that means an admin (me) having to set it all up, and adjust things as required. (On reflection I have to do all this anyway, so what am I bitching about)

In the end I guess that's no biggy, but it would be sooooo much nicer if SQL could export to Excel directly without having to enable security holes. M$ will argue that they have a million ways of doing this, but none of them are as straightforward as OutputTo or TransferSpreadsheet ... #Must learn more#

 

[Mark_]

For reporting via excel, I always export to a "Data" sheet. The other sheets that they look at just reference ranges in "Data". Means I avoid issues of looking at the back end but still being able to dynamically update the data as needed.