By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,257 Members | 925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,257 IT Pros & Developers. It's quick & easy.

Can't access temp-table in backend-db

P: n/a
I have worked on this problem some hours now (read many-many...), and I
can't solve it:

In vba-code I create a table with Connection.Execute, and add some data to
it. This table is saved in the backend-database. After generating this
table, I want to open a report based on a query with data from this
temporary table. In the report's OnOpen event I set the report's record
source to the query pointing to the temporary table.

If I run this code manually in two steps, that is : 1. Generate the table
and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly after each
other in code. Access then says it can't find the temporary table.

Somehow the table isn't viewable for the query thats in the recordsource of
the report just after it has been created.

And of course: If I create the temporary table in the frontend-db, then it
all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a select-query as
recordsource for the report, something like this "Select .... From .... In
'" & BackDb & "' where ...."

Any help can bring me further :-)

Jon Ole Hedne
Norway
Nov 12 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
I had the same problem, the only way I was able to work around it was to
compact the database in code, but that led to problems when other people
were using the BE. Creating the temp table permanently on the FE was the
easiest solution.

"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:wp******************@news4.e.nsc.no...
I have worked on this problem some hours now (read many-many...), and I
can't solve it:

In vba-code I create a table with Connection.Execute, and add some data to
it. This table is saved in the backend-database. After generating this
table, I want to open a report based on a query with data from this
temporary table. In the report's OnOpen event I set the report's record
source to the query pointing to the temporary table.

If I run this code manually in two steps, that is : 1. Generate the table
and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly after each
other in code. Access then says it can't find the temporary table.

Somehow the table isn't viewable for the query thats in the recordsource of the report just after it has been created.

And of course: If I create the temporary table in the frontend-db, then it
all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a select-query as
recordsource for the report, something like this "Select .... From .... In
'" & BackDb & "' where ...."

Any help can bring me further :-)

Jon Ole Hedne
Norway

Nov 12 '05 #2

P: n/a
Well, I hope someone have a solution. The temp-tables I have to create, are
tables for each user in a multiuser application. Each user will need an
individual table to view the report. And I will possibly get problems when
the frontend-db is growing and growing in size...

But maybe choosing the option 'Compress at close' will work? Have you tried
that in a multiuser environment? I've tested it at home with running
multiple users at my Citrix-server. It looks as it works fine, that means
all users can close the database without problems and the last user
compresses the mdb-file when logging of. But I haven't tested it in 'real
life' :-)

Jon Ole
"None" <No**@none.com> wrote in message
news:NI********************@dls.net...
I had the same problem, the only way I was able to work around it was to
compact the database in code, but that led to problems when other people
were using the BE. Creating the temp table permanently on the FE was the
easiest solution.

"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:wp******************@news4.e.nsc.no...
I have worked on this problem some hours now (read many-many...), and I
can't solve it:

In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the backend-database. After generating this
table, I want to open a report based on a query with data from this
temporary table. In the report's OnOpen event I set the report's record
source to the query pointing to the temporary table.

If I run this code manually in two steps, that is : 1. Generate the table and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly after each
other in code. Access then says it can't find the temporary table.

Somehow the table isn't viewable for the query thats in the recordsource

of
the report just after it has been created.

And of course: If I create the temporary table in the frontend-db, then it all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a select-query as
recordsource for the report, something like this "Select .... From .... In '" & BackDb & "' where ...."

Any help can bring me further :-)

Jon Ole Hedne
Norway


Nov 12 '05 #3

P: n/a
TC
If you need a seperate temp table for each user in a multiuser environment,
you would put it in the >front< end, surely, not the back end? In the back
end, it will be shared by everyone. In the front end, everyone will have
their own copy, because everyone should have their own copy of the front end
itself.

I can't see the compact approach solving your problem. If it is a multiuser
database, there will often be several users on at the same time, so the
compact could not be done in the right timeframe.

HTH,
TC
"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:Lk******************@news2.e.nsc.no...
Well, I hope someone have a solution. The temp-tables I have to create, are tables for each user in a multiuser application. Each user will need an
individual table to view the report. And I will possibly get problems when
the frontend-db is growing and growing in size...

But maybe choosing the option 'Compress at close' will work? Have you tried that in a multiuser environment? I've tested it at home with running
multiple users at my Citrix-server. It looks as it works fine, that means
all users can close the database without problems and the last user
compresses the mdb-file when logging of. But I haven't tested it in 'real
life' :-)

Jon Ole
"None" <No**@none.com> wrote in message
news:NI********************@dls.net...
I had the same problem, the only way I was able to work around it was to
compact the database in code, but that led to problems when other people
were using the BE. Creating the temp table permanently on the FE was the
easiest solution.

"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:wp******************@news4.e.nsc.no...
I have worked on this problem some hours now (read many-many...), and I can't solve it:

In vba-code I create a table with Connection.Execute, and add some data
to
it. This table is saved in the backend-database. After generating this
table, I want to open a report based on a query with data from this
temporary table. In the report's OnOpen event I set the report's
record source to the query pointing to the temporary table.

If I run this code manually in two steps, that is : 1. Generate the table and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly after each other in code. Access then says it can't find the temporary table.

Somehow the table isn't viewable for the query thats in the recordsource of
the report just after it has been created.

And of course: If I create the temporary table in the frontend-db,
then
it all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a select-query
as recordsource for the report, something like this "Select .... From
..... In '" & BackDb & "' where ...."

Any help can bring me further :-)

Jon Ole Hedne
Norway



Nov 12 '05 #4

P: n/a
Everyone share both the backend and the frontend-db, - we are running a
terminalserver-solution, so they do not work with localy files. And they
can't see the tables they are not allowed to see, because it's a
runtime-installation only.

I don't need to compact the files so frequently, I'm satisfied if they
compact i.e. when it's only one user connected to the files.

Jon Ole
"TC" <a@b.c.d> wrote in message news:1070167350.487302@teuthos...
If you need a seperate temp table for each user in a multiuser environment, you would put it in the >front< end, surely, not the back end? In the back
end, it will be shared by everyone. In the front end, everyone will have
their own copy, because everyone should have their own copy of the front end itself.

I can't see the compact approach solving your problem. If it is a multiuser database, there will often be several users on at the same time, so the
compact could not be done in the right timeframe.

HTH,
TC
"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:Lk******************@news2.e.nsc.no...
Well, I hope someone have a solution. The temp-tables I have to create, are
tables for each user in a multiuser application. Each user will need an
individual table to view the report. And I will possibly get problems when
the frontend-db is growing and growing in size...

But maybe choosing the option 'Compress at close' will work? Have you

tried
that in a multiuser environment? I've tested it at home with running
multiple users at my Citrix-server. It looks as it works fine, that means all users can close the database without problems and the last user
compresses the mdb-file when logging of. But I haven't tested it in 'real life' :-)

Jon Ole
"None" <No**@none.com> wrote in message
news:NI********************@dls.net...
I had the same problem, the only way I was able to work around it was to compact the database in code, but that led to problems when other people were using the BE. Creating the temp table permanently on the FE was the easiest solution.

"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:wp******************@news4.e.nsc.no...
> I have worked on this problem some hours now (read many-many...), and I > can't solve it:
>
> In vba-code I create a table with Connection.Execute, and add some data
to
> it. This table is saved in the backend-database. After generating

this > table, I want to open a report based on a query with data from this
> temporary table. In the report's OnOpen event I set the report's

record > source to the query pointing to the temporary table.
>
> If I run this code manually in two steps, that is : 1. Generate the

table
> and 2. Open the report, all works fine.
>
> The problem comes up when I run these two procedures directly after each > other in code. Access then says it can't find the temporary table.
>
> Somehow the table isn't viewable for the query thats in the recordsource of
> the report just after it has been created.
>
> And of course: If I create the temporary table in the frontend-db, then
it
> all works fine. But I don't want it to be there!!
>
> I'm using ADO to put data in the temporary table, and a select-query

as > recordsource for the report, something like this "Select .... From

....
In
> '" & BackDb & "' where ...."
>
> Any help can bring me further :-)
>
> Jon Ole Hedne
> Norway
>
>



Nov 12 '05 #5

P: n/a
"Jon Ole Hedne" <jo*****@online.no> wrote in
news:wp******************@news4.e.nsc.no:
I have worked on this problem some hours now (read many-many...), and I
can't solve it:

In vba-code I create a table with Connection.Execute, and add some data
to it. This table is saved in the backend-database. After generating
this table, I want to open a report based on a query with data from this
temporary table. In the report's OnOpen event I set the report's record
source to the query pointing to the temporary table.

If I run this code manually in two steps, that is : 1. Generate the
table and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly after each
other in code. Access then says it can't find the temporary table.

Somehow the table isn't viewable for the query thats in the recordsource
of the report just after it has been created.

And of course: If I create the temporary table in the frontend-db, then
it all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a select-query as
recordsource for the report, something like this "Select .... From ....
In '" & BackDb & "' where ...."

Any help can bring me further :-)

Jon Ole Hedne
Norway


IMO this is likely to be an entirely unusual and ill-advised procedure.
Yes, I'm sure there are special circumstances; there always are. But those
special circumstances arise from poor database design or poor programming
decisions.

Regardless,
1. Have you refreshed the tabledefs in the backend?
2. Have you specfied the path to the backend explicitly?
3. Are you wrapping the creation of the temp table in a Transaction?
Access may never see such a table (although VBA may be able to) until the
Transaction is committed.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #6

P: n/a
"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:Hq******************@news4.e.nsc.no...
Everyone share both the backend and the frontend-db, - we are running a
terminalserver-solution, so they do not work with localy files. And they
can't see the tables they are not allowed to see, because it's a
runtime-installation only.


The reasons for all users having their own copy of the front end don't go away
just because you are using Terminal Server. Each Terminal Server user should
have a profile folder that is only for them on the server and that is where each
user should have their own copy of the front end file.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #7

P: n/a
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
IMO this is likely to be an entirely unusual and ill-advised procedure.
Yes, I'm sure there are special circumstances; there always are. But those
special circumstances arise from poor database design or poor programming
decisions.

Regardless,
1. Have you refreshed the tabledefs in the backend?
2. Have you specfied the path to the backend explicitly?
3. Are you wrapping the creation of the temp table in a Transaction?
Access may never see such a table (although VBA may be able to) until the
Transaction is committed.


You are probably right - I do not like it when my design forces the creation
of temporary tables. I'll working on it. And: No, I had not refreshed the
tabledefs, but it seems like it doesn't help. And I don't use transaction.
And yes, the path is correct. I think it's an updating-issue, and I'll see
if I can use some update/refresh method with ADO.

Jon Ole
Nov 12 '05 #8

P: n/a
This might be a solution. But I don't know how I should give all users their
own local front-end, and how to ensure they have the latest version of the
file. Is it a simple installation-procedure for this?

Jon Ole
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bq*************@ID-98015.news.uni-berlin.de...
"Jon Ole Hedne" <jo*****@online.no> wrote in message
news:Hq******************@news4.e.nsc.no...
Everyone share both the backend and the frontend-db, - we are running a
terminalserver-solution, so they do not work with localy files. And they
can't see the tables they are not allowed to see, because it's a
runtime-installation only.
The reasons for all users having their own copy of the front end don't go

away just because you are using Terminal Server. Each Terminal Server user should have a profile folder that is only for them on the server and that is where each user should have their own copy of the front end file.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #9

P: n/a
On Sun, 30 Nov 2003 18:49:10 +0100, "Jon Ole Hedne"
<jo*****@online.no> wrote:

Did you really get Lyle's point about refreshing the tabledefs? This
code has ALWAYS worked for me:

CreateSomeTable("NewTable") 'some proc to create the given table.
debug.print currentdb.tabledefs("NewTable").Name '<- this fails
currentdb.tabledefs.refresh
debug.print currentdb.tabledefs("NewTable").Name '<- now it works

-Tom.
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
IMO this is likely to be an entirely unusual and ill-advised procedure.
Yes, I'm sure there are special circumstances; there always are. But those
special circumstances arise from poor database design or poor programming
decisions.

Regardless,
1. Have you refreshed the tabledefs in the backend?
2. Have you specfied the path to the backend explicitly?
3. Are you wrapping the creation of the temp table in a Transaction?
Access may never see such a table (although VBA may be able to) until the
Transaction is committed.


You are probably right - I do not like it when my design forces the creation
of temporary tables. I'll working on it. And: No, I had not refreshed the
tabledefs, but it seems like it doesn't help. And I don't use transaction.
And yes, the path is correct. I think it's an updating-issue, and I'll see
if I can use some update/refresh method with ADO.

Jon Ole


Nov 12 '05 #10

P: n/a
jo*****@online.no (Jon Ole Hedne) wrote in
<wp******************@news4.e.nsc.no>:
I have worked on this problem some hours now (read many-many...),
and I can't solve it:

In vba-code I create a table with Connection.Execute, and add some
data to it. This table is saved in the backend-database. After
generating this table, I want to open a report based on a query
with data from this temporary table. In the report's OnOpen event
I set the report's record source to the query pointing to the
temporary table.

If I run this code manually in two steps, that is : 1. Generate
the table and 2. Open the report, all works fine.

The problem comes up when I run these two procedures directly
after each other in code. Access then says it can't find the
temporary table.

Somehow the table isn't viewable for the query thats in the
recordsource of the report just after it has been created.

And of course: If I create the temporary table in the frontend-db,
then it all works fine. But I don't want it to be there!!

I'm using ADO to put data in the temporary table, and a
select-query as recordsource for the report, something like this
"Select .... From .... In '" & BackDb & "' where ...."

Any help can bring me further :-)


Maybe the report is opening before the code that creates the table
in the back end has finished creating and populating it.

If I'm creating a temp table that applies only to a particular
report, I'd initiate the populating of that temp table in the
OnOpen event of the report, not in the calling code context.

Also, I see no reason why you'd be using ADO for an operation that
is pretty clearly all Jet. While ADO is very useful for working
with data stored in database server back ends, it's really pretty
much useless for working with Jet data -- with Jet, DAO is the
native access method and likely to be substantially faster in a
number of operations.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #11

P: n/a
to*****@no.spam.cox.net (Tom van Stiphout) wrote in
<vq********************************@4ax.com>:
On Sun, 30 Nov 2003 18:49:10 +0100, "Jon Ole Hedne"
<jo*****@online.no> wrote:

Did you really get Lyle's point about refreshing the tabledefs?
This code has ALWAYS worked for me:

CreateSomeTable("NewTable") 'some proc to create the given table.
debug.print currentdb.tabledefs("NewTable").Name '<- this fails
currentdb.tabledefs.refresh
debug.print currentdb.tabledefs("NewTable").Name '<- now it works


If you've got a link to the temp table in your front end, there's
absolutely no need to refresh your tabledefs in the front end,
because the link hasn't been changed.

I think the problem here is asynchronous execution: the line of
code that opens the report is executing before the previous line
(that creates and populates the temp table) has finished executing.
This problem can be avoided by putting the call to the code that
writes the table in the report's OnOpen event, since the report
won't display/print until after the code in the OnOpen event has
finished executing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #12

P: n/a
jo*****@online.no (Jon Ole Hedne) wrote in
<Hq******************@news4.e.nsc.no>:
Everyone share both the backend and the frontend-db, - we are
running a terminalserver-solution, so they do not work with localy
files. And they can't see the tables they are not allowed to see,
because it's a runtime-installation only.

I don't need to compact the files so frequently, I'm satisfied if
they compact i.e. when it's only one user connected to the files.


Others have told you that this is simply bad practice, all round.

Temp tables should never be stored in the back end or the front
end. You should have a temp database where you store all temp
tables. Some folks in these parts also create this temp database on
the fly, each time they use it. I don't do that, I just re-use the
same one and never worry about compacting it at all (if it does
grow large enough that it needs to be compacted, it's easier to
copy an empty, compacted copy over top of it than it is to compact
the file).

As to your questions about how to manage distributing individual
front ends (and, for that matter, temp databases) to each user
running on WTS, Tony Toews's website has all the information you
need on how to do it.

Your current setup, with everyone opening the same front end, is
guaranteed to lead to grief of one sort or the other, eventually.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

P: n/a
dX********@bway.net.invalid (David W. Fenton) wrote:
Temp tables should never be stored in the back end or the front
end. You should have a temp database where you store all temp
tables. Some folks in these parts also create this temp database on
the fly, each time they use it. I don't do that, I just re-use the
same one and never worry about compacting it at all (if it does
grow large enough that it needs to be compacted, it's easier to
copy an empty, compacted copy over top of it than it is to compact
the file).
Yup, we've had this discussion. <smile>
As to your questions about how to manage distributing individual
front ends (and, for that matter, temp databases) to each user
running on WTS, Tony Toews's website has all the information you
need on how to do it.
1) For more info on the errors or the Auto FE Updater utillity see the free Auto FE
Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep
the FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now supports creating
a directory named after the user on a server. Given a choice put the FE on the
Citrix server to reduce network traffic and to avoid having to load objects over the
network which can be somewhat sluggish.

2) See the TempTables.MDB page at my website which illustrates how to use a temporary
MDB in your app. http://www.granite.ab.ca/access/temptables.htm
Your current setup, with everyone opening the same front end, is
guaranteed to lead to grief of one sort or the other, eventually.


Ayup. Although some people seem to run for quite a while without any problems
sharing an A2000 or newer MDB while others run into troubles in the first few days
after converting from A97.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #14

P: n/a
I think so. Here is my code and some debug.print output:

Snu_Svar_Tabell ' The code that generates the temp-table
Dim db As Database
Set db = OpenDatabase(DataFil)
Debug.Print DataFil > C:\Documents and
Settings\joh\Desktop\KSV_12\data\ksv_data.mdb
db.TableDefs.Refresh
Debug.Print db.TableDefs(TempSvar).Name > JOH_TempSvar
db.Close

On Error GoTo err:
DoCmd.OpenReport "Eval_rapp", acViewPreview

err:
Debug.Print err.Description > The Microsoft Jet database engine cannot find
the input table or query 'JOH_TempSvar'......

Private Sub Report_Open(Cancel As Integer)
DoCmd.ShowToolbar "KSI_Rapporter", acToolbarYes
Dim SQL As String
SQL =The sql...
Debug.Print SQL
SELECT Koder_Omraader.Tekst AS Område, JOH_TempSvar.Spmnr, Kode_Spm_1.Spm_tekst AS Overordnet, Kode_Spm.Spm_tekst,
JOH_TempSvar.Feltnavn,
IIf(IsNull(JOH_TempSvar!Verdi)>=True,JOH_TempSvar! Annet,kode_svar!Svar) AS
Svar, Evalueringer.Regdato, Grunnoppl.DistriktNavn, Grunnoppl.Saksbehandler,
Grunnoppl.MartheID FROM (Evalueringer LEFT JOIN Grunnoppl ONEvalueringer.MartheID = Grunnoppl.MartheID) RIGHT JOIN (Kode_Spm AS Kode_Spm_1 RIGHT JOIN (Omraader RIGHT JOIN (Koder_Omraader RIGHT JOIN
(Kode_Svar RIGHT JOIN (Kode_Spm RIGHT >JOIN JOH_TempSvar ON
(Kode_Spm.Omraade = JOH_TempSvar.Omraade) AND (Kode_Spm.ID =
JOH_TempSvar.Spmnr)) ON (Kode_Svar.OmraadeID = JOH_TempSvar.Omraade) AND
(Kode_Svar.Spmnr = >JOH_TempSvar.Spmnr) AND (Kode_Svar.Verdi =
JOH_TempSvar.Verdi)) ON Koder_Omraader.ID = JOH_TempSvar.Omraade) ON
Omraader.ID = JOH_TempSvar.Omraade_ID) ON (Kode_Spm_1.ID =Kode_Spm.OverOrdnetSpm) AND (Kode_Spm_1.Omraade = Kode_Spm.Omraade)) ON Evalueringer.ID = Omraader.Evaluering_ID IN 'C:\Documents and
Settings\joh\Desktop\KSV_12\data\ksv_data.mdb'ORDER BY JOH_TempSvar.Omraade, JOH_TempSvar.Feltnavn; Me.RecordSource = SQL

End Sub

Jon Ole

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vq********************************@4ax.com... On Sun, 30 Nov 2003 18:49:10 +0100, "Jon Ole Hedne"
<jo*****@online.no> wrote:

Did you really get Lyle's point about refreshing the tabledefs? This
code has ALWAYS worked for me:

CreateSomeTable("NewTable") 'some proc to create the given table.
debug.print currentdb.tabledefs("NewTable").Name '<- this fails
currentdb.tabledefs.refresh
debug.print currentdb.tabledefs("NewTable").Name '<- now it works

-Tom.
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
IMO this is likely to be an entirely unusual and ill-advised procedure.
Yes, I'm sure there are special circumstances; there always are. But those special circumstances arise from poor database design or poor programming decisions.

Regardless,
1. Have you refreshed the tabledefs in the backend?
2. Have you specfied the path to the backend explicitly?
3. Are you wrapping the creation of the temp table in a Transaction?
Access may never see such a table (although VBA may be able to) until the Transaction is committed.


You are probably right - I do not like it when my design forces the creationof temporary tables. I'll working on it. And: No, I had not refreshed the
tabledefs, but it seems like it doesn't help. And I don't use transaction.And yes, the path is correct. I think it's an updating-issue, and I'll seeif I can use some update/refresh method with ADO.

Jon Ole

Nov 12 '05 #15

P: n/a
Thank you all very very much! I have got a lot more knowledge and
information now - I'll look at the links to help on TS-setup and eventually
try to put all of the code in the reports onopen-event. It looks like the
best solution is to put the temporary tables in a separate temp-file.

I have another installation where several users share the same
frontend-file, and it has worked very fine and stable for about a year now.
But it doesn't use temporary tables...

Again - thank you all for all this help :-)

Jon Ole
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:i8********************************@4ax.com...
dX********@bway.net.invalid (David W. Fenton) wrote:
Temp tables should never be stored in the back end or the front
end. You should have a temp database where you store all temp
tables. Some folks in these parts also create this temp database on
the fly, each time they use it. I don't do that, I just re-use the
same one and never worry about compacting it at all (if it does
grow large enough that it needs to be compacted, it's easier to
copy an empty, compacted copy over top of it than it is to compact
the file).
Yup, we've had this discussion. <smile>
As to your questions about how to manage distributing individual
front ends (and, for that matter, temp databases) to each user
running on WTS, Tony Toews's website has all the information you
need on how to do it.


1) For more info on the errors or the Auto FE Updater utillity see the

free Auto FE Updater utility at http://www.granite.ab.ca/access/autofe.htm at my website to keep the FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now supports creating a directory named after the user on a server. Given a choice put the FE on the Citrix server to reduce network traffic and to avoid having to load objects over the network which can be somewhat sluggish.

2) See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. http://www.granite.ab.ca/access/temptables.htm
Your current setup, with everyone opening the same front end, is
guaranteed to lead to grief of one sort or the other, eventually.
Ayup. Although some people seem to run for quite a while without any

problems sharing an A2000 or newer MDB while others run into troubles in the first few days after converting from A97.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #16

P: n/a
TC
I'm surprised that:
debug.print currentdb.tabledefs("NewTable").Name fails, since executing the currentdb function automatically refreshes all
collections, & VBA would presumeably evaluate currentdb >before< it looked
for the collection member.

It might be interesting to see whether repeating that line, would work the
second time. (I don't have Access here to check).

Are you sure that you weren't using: debug.print db.tabledefs("NewTable").Name where db was a cached reference? (in which case, the collections would not
be refreshed by that line)

TC
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vq********************************@4ax.com... On Sun, 30 Nov 2003 18:49:10 +0100, "Jon Ole Hedne"
<jo*****@online.no> wrote:

Did you really get Lyle's point about refreshing the tabledefs? This
code has ALWAYS worked for me:

CreateSomeTable("NewTable") 'some proc to create the given table.
debug.print currentdb.tabledefs("NewTable").Name '<- this fails
currentdb.tabledefs.refresh
debug.print currentdb.tabledefs("NewTable").Name '<- now it works


Nov 12 '05 #17

P: n/a
Yes, it works! Repeating the line debug.print db.tabledefs("NewTable").Name
solved my problem and :

On Error Resume Next
Dim db As Database
Set db = OpenDatabase(DataFil)
db.TableDefs.Refresh
db.TableDefs.Refresh
Debug.Print db.TableDefs(TempSvar).Name > The name comes out

I also did put the create-table-statement in the OnOpen-event in the report.
Now the report opens!

The next step will be to create this table in a temp-db. Thanks a lot!!

Jon Ole

"TC" <a@b.c.d> wrote in message news:1070254178.777698@teuthos...
I'm surprised that:
> debug.print currentdb.tabledefs("NewTable").Name

fails, since executing the currentdb function automatically refreshes all
collections, & VBA would presumeably evaluate currentdb >before< it looked
for the collection member.

It might be interesting to see whether repeating that line, would work the
second time. (I don't have Access here to check).

Are you sure that you weren't using:
> debug.print db.tabledefs("NewTable").Name

where db was a cached reference? (in which case, the collections would not
be refreshed by that line)

TC
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vq********************************@4ax.com...
On Sun, 30 Nov 2003 18:49:10 +0100, "Jon Ole Hedne"
<jo*****@online.no> wrote:

Did you really get Lyle's point about refreshing the tabledefs? This
code has ALWAYS worked for me:

CreateSomeTable("NewTable") 'some proc to create the given table.
debug.print currentdb.tabledefs("NewTable").Name '<- this fails
currentdb.tabledefs.refresh
debug.print currentdb.tabledefs("NewTable").Name '<- now it works


Nov 12 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.