Question Help with distributing database in Access 2007

[apf500]

Hi, have searched extensively but can't seem to find an answer. For a few years I have been running a school marking system which I created in Access 2003. The system worked well as I could keep a master DB on the server in school and let staff go away with a replicated copy. I put in suitable macros to ensure people would be forced to sync regularly but it allowed people the freedom to take their marking home without the need to connect to a central SQL server or other such device. This was very important as I essentially have no budget to set up something like this.
This month I have renovated my DB in Access 2007 to streamline and improve it. However I now find myself in a catch 22. One of the key reasons for updating was to add pictures to each student for easy recognition when marking etc however if I keep the file format as mdb (and therefore continue being able to replicate) the files quickly become ridiculously big; yet if I convert to ACC2007 I gain attachments and lose replication. I have explored DBPix as an option but then all staff would need to purhcase their own copy and I certainly can't see school funding this! I am really feeling like Microsoft has left small scale developers in the lurch here, I simply don't have the infrastructure to create anything more complex (or more fit for purpose) than replication. Any help or suggestions would be greatly appreciated.

Alex

 

[dfenton]

One of the key reasons for updating was to add pictures to each student for easy recognition when marking etc however if I keep the file format as mdb (and therefore continue being able to replicate) the files quickly become ridiculously big; yet if I convert to ACC2007 I gain attachments and lose replication. I have explored DBPix as an option but then all staff would need to purhcase their own copy and I certainly can't see school funding this! I am really feeling like Microsoft has left small scale developers in the lurch here, I simply don't have the infrastructure to create anything more complex (or more fit for purpose) than replication.

You're absolutely right that MS has made a really bad decision. Their "answer" is that you're supposed to use Sharepoint for this, but that doesn't even begin to come close to the functionality and data integrity of Jet replication. And it requires a server.

First off, it's not clear if your application is split. It definitely needs to be, even though you certainly need replication for the back-end data tables.

Secondly, you can use and ACCDB as front end to an MDB back end. This doesn't help you with your attachments problem, but it can help with other UI-related issues that are better supported in ACCDBs than in MDBs.

The key question to me is whether or not people are often changing the photos. It doesn't seem to me like the kind of data that's going to be changed often, nor is it going to be changed incrementally. Perhaps photos are updated only at the beginning of the school year. In any event, I would suggest you consider a non-replicated solution for the photos, i.e., putting them in a separate back-end ACCDB in order to get the benefits of the new attachment fields, and then update them manually. This means you'll need a date field that tells you when the picture was changed, and you'll replace the old pictures on the server with the new ones from the users, and then replace the local copy of the attachment records with the records on the server that are newer.

Two other alternatives occur to me:

1. use a separate replicated MDB file for the pictures. This will bloat, but it will be a separate MDB. This is what I did back in 1998 for embedded OLE documents that needed to be shared between multiple locations. We synched the main data file 3X a day, but the OLE docs database only once a day. It worked fairly well and didn't cause any real issues.

2. ask yourself if the users really need the pictures when they are out of the office. Is it worth all this extra work to provide that functionality? If not, then you don't need to provide them with the pictures at all, and just keep those in a separate ACCDB on the server.

I hope you convey your dissatisfaction back to Microsoft.

 

[apf500]

Thanks the second db was a really good workaround. I added the photos table as a linked table in Access (non replicated) the only problem now is that I cannot use any of the queries which access the photo database when away from school - access simply locks them out because it cannot find the photo database - this has rendered large amounts of my forms and queries unusable outside school. Is there any way just to get access to ignore the problem?

 

[dfenton]

Thanks the second db was a really good workaround. I added the photos table as a linked table in Access (non replicated) the only problem now is that I cannot use any of the queries which access the photo database when away from school - access simply locks them out because it cannot find the photo database - this has rendered large amounts of my forms and queries unusable outside school. Is there any way just to get access to ignore the problem?

You'll have to revise your app to check and behave differently when the table is not there. The easiest way to do that is to do this in your startup routine:

  If Len(Dir(Mid(CurrentDB.TableDefs("AttachmentsTable").Connect, 11)))=0 Then
     [do whatever you need to do to set things up to ork]
  End If

Perhaps the absolutely easiest way would be to have an empty copy of your attachments table in the front end and swap the names of the two tables based on the code above.