In Access I build reports using temporary tables in the front end. But when
putting my Access on SharePoint I can't do that. Any tips for how to compile
report data using SharePoint? Thanks!

"Neil" wrote in message news:jnfkf1$oln$1 (AT) dont-email (DOT) me...


I can quite much say from experience that applications that use temp tables
really break down and work poor with on-line. It not a practial option right
now.

One of my suggestions and less then ideal workarounds is to publish the web
application, but built + use the reports in the Access client.

You can the most certainly send data out to some local (linked) tables on
the users. So in this case, the munching + crunching of data occurs local.

So, the suggesting for such types of reporting is to use the Access client.


--

Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoSpam_kallal (AT) msn (DOT) com

I know in SQL Server you can create temp tables. No such option in
SharePoint?

I appreciate the suggestion. Unfortunately, not doable in this case. My
client (for whom I'm building the database) uses Access, and I have the
reports in Access. No problem. But their client, for whom they're processing
the data, want to be able to view the data online. Hence the SharePoint
component.

In other words, the whole reason we're using SharePoint is for the client to
be able to query their data online in a browser.

One option I thought of, if true temp tables aren't available, is to have
reporting tables, same as the local Access tables, but use a unique ID for
each report. Then, instead of the report needing exclusive access to the
table, it would only need the records with that unique ID. Then, when the
report is done, the records with that ID can be deleted.

Of course, that would cause serious table bloating over time. But I assume
that there would be some kind of compacting done on the server side as part
of routine maintenance?

That's the only solution I can think of. Surely there must be a method that
people use to build reports with.....

Thanks!


"Albert D. Kallal" <PleaseNOSpamkallal (AT) msn (DOT) com> wrote

On Sat, 28 Apr 2012 00:10:37 -0500, "Neil"
<neil.ginsberg+newsgroup (AT) gmail (DOT) com> wrote:

Not for me either. Thanks for your post! It saved me from doing
further research on rewriting our software for Sharepoint, etc...

Depending on why/how you employ the temporary tables, you might be able to
build them in an Access database app in the user's machine, run and create a
PDF from the report, and upload the PDF to Sharepoint, or to a Sharepoint
file/list.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Neil" <neil.ginsberg+newsgroup (AT) gmail (DOT) com> wrote

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

If I recall correctly, you were just looking to use SharePoint as a back end
to an existing Access app. That's completely different to what I'm
discussing here. Here I'm talking about converting both the front end report
and the back end data to SharePoint. If you're just looking to use
SharePoint as a back end to your existing VBA app, then you don't have to
convert your reports, and you can still use temporary report tables in your
front end. So, an entirely different situation than what's being discussed
here.

Neil

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

Thanks, Larry. No, these are going to be run from a web page. So the data
has to be compiled in SharePoint.

Neil

On Apr 27, 9:24*pm, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
Are your temp tables finite in count and are the field names the same
for a specific report? If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

"Patrick Finucane" <patrickfinucanetx (AT) gmail (DOT) com> wrote

On Apr 27, 9:24 pm, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
Are your temp tables finite in count and are the field names the same
for a specific report? If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

===========================

Yes, I could do that. I was thinking of doing something along those lines.
Was thinking, though, of using a table with an autonumber field, adding a
record to it, and then using the ID value to link the tables. Is that
basically what you're saying, but to assign the ID value to tempvar and use
it that way?

Also, at what point would I clear the temp data? With my temp tables I'm
just clearing the temp data before the report is run. But with multiple
users using the same table, obviously I wouldn't be able to delete the
entire table. So when would I delete the data associate with the tempvars!ID
value?

Thanks!

On Apr 30, 9:00*pm, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
Yes. I was thinking initialize a Tempvars!Vars variable is set for a
current "ID" to symbolize the records processed. Then when you
complete run delete queries that delete the data for that set just
processed. I don't have experience with Sharepoint yet. I understand
your limitations tho. It's clunky but should work.