Temp tables, make table query, remote desktop

[Rachael2211]

Hi All and thanks for taking a look at my problem!

Situation: MS server 2008, access 2010 frontend and 2003 backend on this. Users logon onto server through a remote desktop connection where they can open the .accdr loaded there, so all users have there own rdp logon but are using the same .accdr

Problem: One area of the program creates a temp table (using a make table query) when the form is opened to display some complex data, it's only display, no manipulation. The make table query is based on a union query pulling info from all over. The problem occurs when another user tries to enter this same area and the program tries to run the make table. The error message states that the tempo table is locked by another user - I get all that and why the error is occurring - so - my thought was to test if the temp table was in use and bypass the make table.

Here's what I've tried:
Public Function IsTableOpen(strName As String) As Boolean
IsTableOpen = SysCmd(acSysCmdGetObjectState, acTable, strName)
End Function

'on open proc of form
If IsTableOpen("BusBud1Temp") Then
MsgBox "Table in Use"
Else
DoCmd.OpenQuery "BusBud1Qry_MT"
End If

and...
Dim db As DAO.Database
Dim rs2 As DAO.Recordset
Set db = CurrentDb()

Set rs2 = db.OpenRecordset("BusBud1Temp", dbOpenTable, dbDenyWrite + dbDenyRead)
If Err.Number > 0 Then
MsgBox "Table in Use"
Else
rs2.Close
Set rs2 = Nothing
DoCmd.OpenQuery "BusBud1Qry_MT"
End If

So I think the above fails because each user is sharing the frontend file ie they both have the .accdr open in their own remote desktop session.

I did think of putting the temp files in the backend but not sure if this is good practice as I don't want them to bloat it, it's already quite large.

Any ideas or suggestions will be most welcome, thank you.

Kind regards, Rachael

 

[missinglinq]

...So I think the above fails because each user is sharing the frontend file ie they both have the .accdr open in their own remote desktop session...

See, Rachael, you solved the problem all on your own! Having multiple users sharing a single Front End, on a network drive, is the surefire way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior!

Being in forced retirement, I spend 8-10 hours a day here and on half-a-dozen other Access forums/newsgroups, and over the past ten years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing the kind of problem you're having, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once the problem rears its ugly head, it becomes chronic, which is to say it occurs over and over and over again!

You need to install a copy of the Front End on each user's Hard Drive.

Linq ;0)>

 

[Galaxiom]

Temporary data does not belong in the FE or the BE. On the rare occasions that I use temporary tables I put them in a disposable database in the user's Temp folder in their profile.

Also as linq says. give each user a separate copy of the FE. In the rdp environment you would copy this to their profile.

 

[Galaxiom]

BTW I doubt that you need the temporary table. Why not just bind the form directly to the Union query?

 

[Rachael2211]

Righto! thank you both kindly - we've been using the shared file setup for over 5 years without any headaches but...I don't want any either so...off with an email to our server guy!! thanks for your responses Missinglinq and Galaxiom (also take on board you're suggestion with a temp folder, cheers!)

 

[Rachael2211]

Galaxiom - did have it like that but found it tooooo slow when navigating around so found it better for the user to suffer through one instance of the union (in the make table on open) rather than everytime they navigated around (its a tree control displaying various parts/levels of an operations budget)