Microsoft Access Linked Table - Can I use an .accdr file as a backend

Question

Can anyone confirm whether linking to tables in a Microsoft Access .accdr files is possible?

I have a split MS-ACCESS application with the frontend in a .accdr file and the tables in an .accdb file backend on a server. For reasons that I explain below, I need the frontend to connect to a table in a second backend file (also on the server) that has an .accdr extension.

I found no documentation that explicitly states that .accdr files are not allowed as as a backend database, however, the linked table manager does not seem to allow it, and the DoCmd.DatabaseTransfer method throws an error.

My workaround is to temporarily rename the .accdr server file as an .accdb, link to the tables in it to make the data changes, then rename it back to .accdr. But if I don't HAVE to do this, I would prefer to connect directly.

Reason: To make sure that each client gets the most recently updated copy of the frontend .accdr, I created a batch script that first copies the frontend from the server to their local machine before launch. It's a short time penalty (the frontend is about 8 MB) each time they load the application, but the batch script makes sure that the client is ALWAYS up to date. (If performance became an issue I would of course have the batch script verify MD5 sums before downloading, but it hasn't come up yet.)

I don't have many clients connecting at the same time (only about 2 or 3 concurrent connections), but they can connect from any of thousands of computers on campus, so I found this the easiest way to make sure that no matter where they connect from, they will get a fresh copy of the FrontEnd.

Unfortunately, I made a few tables local to the frontend because they allow individual users to set parameters locally during a session. Now, of course, I have a case where an individual user needs to update these local tables so that all the other users will get this same data. (Previously, I had just been making these changes manually upon request, but this user quite reasonably needs to be able to do it herself.)

So, the local frontend .accdr file needs to connect to the server copy of the same frontend .accdr file to make a few data adjustments in a table. That's the reason. I know it's weird.

Thanks, Damon

Answer 1

Turns out it is possible to use .accdr as a backend. I think i was specifying the wrong connection or something. I used this code snippet with the strConnect = path_to_mydb.accdr:

Public Sub ConnectOutput(dbsTemp As Database, _
strTable As String, strConnect As String, _
strSourceTable As String)

Dim tdfLinked As TableDef

' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set tdfLinked = dbsTemp.CreateTableDef(strTable)

tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

End Sub

Answer 2

You could easily add the tables that are local to your front-end to the backend. Just copy/paste them from one to the other, delete them from the front-end and link them to the backend version. Takes all of 5 minutes, no VBA code necessary.

Regarding the accdr as a backend question, then yes, accdr is meant to be a open by a the Runtime version of Access. Opening an .accdr file with the standard version of Access simulates the Runtime mode. The only difference between the standard version and the free Runtime version is that you can't access the design tools and the standard ribbon from a Runtime application (you need to create your own).

So renaming a accdb file into accdr only tells access to open the database in Runtime mode. It doesn't change the database itself at all.
So you can use an accdr file as a back-end without problem.

Pro tip: if you want your setup to scale a bit (easily up to 50 concurrent users) and have better performance, open a database to a dummy table from the front-end to the back-end. This will keep the connection open while the front-end is running and keep the lock file open on the database, resulting in better -and more reliable- performance.