Locked Access Database

[dgrady1113]

I have an Access database with a number of users connecting to it from within Citrix. Occasionally, the database locks up and you can only open it in a "read only" mode. I tried deleting the .laccdb file to unlock it. But so far the only way to delete it is by restarting the Citrix server. I can only get our tech guys to restart the server at night. We lose a whole day of work when it happens. Does anyone have a better solution?

 

[Joe4]

You should DEFINITELY split your database into a front-end and back-end, and each user should have their copy of the front-end database. Access has a utility that will do it for you.

Here are some discussion on how/why you want to split your database:
http://support.microsoft.com/kb/304932 http://www.allenbrowne.com/ser-01.html

Here is a good discussion on splitting an Access database in a Citrix Environment: http://thedailyreviewer.com/office/view/using-a-split-database-in-a-citrix-session-102429485

 

[boblarson]

I agree with Joe on this. By the way, we use Citrix as well and so we have our frontend master in a location on the file server and the shortcut on Citrix actually isn't for opening the actual file, it runs a batch file which then copies the frontend from the master location into the user's temporary directory on the Citrix Server that they are connected to and then opens that up. It works well in that if somehow the connection drops and corrupts the frontend, all they have to do is start the database again because the frontend copies over every time.

See this for the code inside our cmd file:

@echo Please wait while the version of the database you requested is updated.
@echo off
MD E:\BKY_temp\%username%
copy R:\FE\Current\ITProject.mdb E:\BKY_temp\%username% /y
Start /max "E:\Microsoft Office\OFFICE11\MSACCESS.EXE" "E:\BKY_temp\%username%\ITProject.mdb" 
exit

 

[dgrady1113]

Thanks JOE4 and BOBLARSON - I will look into these ideas. One additional aspect I need to consider. The Access Database has linked tables coming from an Oracle database. If I leave them in the back end, will the relevant queries, forms and email merge operations run in the front end?
Thanks again.

 

[boblarson]

As long as the users have the correct permissions to be able to access the Oracle database and the network path is open on Citrix, then there should be no problem with that.

 

[dgrady1113]

I was making great progress on splitting the database until I tried to link from the FE to tables in the BE. Remember I have linked tables in the BE coming from an Oracle DB. When I tried to link to those linked tables in the BE from the Import routine in the FE, none of the Oracle linked tables show up in the list of tables. How do I link to the linked tables in the FE?

 

[boblarson]

Not sure which version you are on but it essentially starts from the Import area, but you need to select ODBC linking and you create a DSN for that ODBC database (in this case Oracle using the Oracle driver). So, which version of Access are you using and which version of Oracle is it you are trying to link to?

 

[dgrady1113]

Wow, you are quick in responding, Bob. I am using MS Office 2007 on the Citrix Server. I think what you are telling me is that the FE database needs to link directly to the source Oracle DB just like my original DB does and not to the BE database. So I will have both links to the BE tables and links to the Oracle DB in the FE database. When I copy the FE to multiple users, will the links stay connected as long as I don't move the BE or Oracle DBs? The multiple users will have separate directories with their own FE versions on the Citrix server. I like your CMD file that automates the copy process.
By the way, our IT folks will be upgrading to MS Office 2010 soon - any caveats for the conversion or will everything work?

 

[boblarson]

Yes, the frontend needs to be linked to the Oracle backend AND the Access backend. And yes, the links will stay after linking and then just copying the frontend file. Make sure the links are set before making the copies for each user.

The backend Access database should NOT be linked to the Oracle database, only the frontend.

As for 2010, everything should be fine, although if you are using any ADO at all, then it can be affected by the SP1 for either Windows 7 SP1 or Office 2010 SP1 as the registry identifiers (GUIDs) have changed for the ActiveX Data Objects when having one or both of those service packs. But as long as all users are on SP1 - no problem.