Report Tables In SharePoint

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@dont-email.me...

>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!


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@msn.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@msn.com> wrote in message
news:kXJmr.14976$DB1.11149@newsfe03.iad...
> "Neil" wrote in message news:jnfkf1$oln$1@dont-email.me...
>
>>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!
>
>
> 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@msn.com

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

>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.

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

> 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@msn.com> wrote in message
>news:kXJmr.14976$DB1.11149@newsfe03.iad...
>> "Neil" wrote in message news:jnfkf1$oln$1@dont-email.me...
>>
>>>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!
>>
>>
>> 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@msn.com
>

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@gmail.com> wrote in message
news:jnfkf1$oln$1@dont-email.me...
> 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!
>

"PW" <emailaddyinsig@ifIremember.com> wrote in message
news:a59pp7pk3g4i3p15ccnksoe56frpg5tume@4ax.com...
> On Sat, 28 Apr 2012 00:10:37 -0500, "Neil"
> <neil.ginsberg+newsgroup@gmail.com> wrote:
>
>>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.
>
> Not for me either. Thanks for your post! It saved me from doing
> further research on rewriting our software for Sharepoint, etc...

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@gmail.com> wrote in message
news:a03q2iFo22U1@mid.individual.net...
> 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.

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...@gmail.com> wrote:
> 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!

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@gmail.com> wrote in message
news:919dbcde-cad0-4feb-b508-8476e981423b@t16g2000yqt.googlegroups.com...
On Apr 27, 9:24 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
> 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!

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...@gmail.com> wrote:
> "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
>
> news:919dbcde-cad0-4feb-b508-8476e981423b@t16g2000yqt.googlegroups.com...
> On Apr 27, 9:24 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
>
> > 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!
>
> 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!

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.

"Patrick Finucane" <patrickfinucanetx@gmail.com> wrote in message
news:78b2833e-4898-4052-baa7-e78a16164125@v1g2000yqm.googlegroups.com...
On Apr 30, 9:00 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
> "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
>
> news:919dbcde-cad0-4feb-b508-8476e981423b@t16g2000yqt.googlegroups.com...
> On Apr 27, 9:24 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
>
> > 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!
>
> 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!

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.

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

Problem is that the report (actually a form) will be based on the temp data.
So if I delete it after the form is opened, won't I run into problems?

I suppose I could put the date into the table that gives me the autonumber
ID for each report, and then, when the form is opened, delete all temp data
from previous days. Thus, the cleanup would occur for past days, but not the
current day.

On May 1, 11:09*am, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
> "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
>
> news:78b2833e-4898-4052-baa7-e78a16164125@v1g2000yqm.googlegroups.com...
> On Apr 30, 9:00 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
>
>
>
>
>
> > "Patrick Finucane" <patrickfinucan...@gmail.com> wrote in message
>
> >news:919dbcde-cad0-4feb-b508-8476e981423b@t16g2000yqt.googlegroups.com....
> > On Apr 27, 9:24 pm, "Neil" <neil.ginsberg+newsgr...@gmail.com> wrote:
>
> > > 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!
>
> > 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, addinga
> > 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!
>
> 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.
>
> ==================================
>
> Problem is that the report (actually a form) will be based on the temp data.
> So if I delete it after the form is opened, won't I run into problems?
>
> I suppose I could put the date into the table that gives me the autonumber
> ID for each report, and then, when the form is opened, delete all temp data
> from previous days. Thus, the cleanup would occur for past days, but not the
> current day.- Hide quoted text -
>
> - Show quoted text -

That works for me. But then, I don't have your problem . Good
luck.