MS Access Database Backend on Lion Server

You can file share using SMB/CIFS which is the Windows standard file sharing protocol. And the Mac will not care what the file format of any file is sitting on it's hard drive. So I say you can have the Access file shared out from the Mac. As long as the Access file isn't depend on any other Windows services like ODBC or IIS (the web server) you should be fine.

I have not used MS Access myself (fortunately) but I believe it works much like the old, old way of achieving a multi-user database in FileMaker.

That is, it is not a true client-server database but more a peer-to-peer database and that each peer opens the same database file which is available to multiple user via file sharing. Therefore as elikness said it should just be a matter of putting the files on your Lion Server and then sharing that folder/volume using SMB sharing.

However one thing to be aware of, since MS Access is not a true client-server database I would imagine it uses record locking to manage simultaneous direct access to records in the same files. File or Record locking is something that has historically been a bit flakey when using Windows clients with a Mac file server (and probably vice versa).

While I am not proposing it as a solution to consider seriously, it is worth you knowing that FileMaker Pro is a database system that is available for both Mac and Windows and depending on how you configure things can be considered to stretch from a configuration very similar to MS Access at the low end all the way to a high-end MS SQL Server style setup at the top end. See http://www.filemaker.com for details.

There is also a a third-party tool available for migrating databases to or from FileMaker Pro and it can convert MS Access databases to FileMaker Pro see http://www.fmpromigrator.com/

You could use FileMaker Pro just on Windows machines if you wish.

John Lockwood makes a very important point concerning record locking and multiple access to the same database file. On the Mac Server software you will want to configure the SMB/CIFS share to do strict locking which will slow down the access a bit while the extra steps are taken to insure one person is accessing the file at the same time.

I've got an old Leopard 10.5 Server where I got a screenshot of the configuration for the share point:

I don't know if Lion Server makes the distinction between opportunistic locking (for Windows Clients) versus strict locking (for Mac clients talking to SMB/CIFS share points). But I think in a mixed environment it might make sense to go with strict locking.

Lion Server has a totally different implementation of SMB and as far as I recall its GUI does not provide access to these settings. However I get the impression it does still support the same /etc/smb.conf configuration file to allow doing this by hand with a text editor.

From what I've read on the Apple Discussion board I had a feeling Lion Server has a lot of stuff locked away and not visible to the Server Admin. Which follows closely the trend in apps that have evolved over time like iMovie (which has lost functions over time) and FCP X (which notably has also lost functions too). Not surprised the GUI doesn't give you easy access to the SMB/CIFS share. And as I've read on the technology news sites, the server they use for SMB sharing isn't SAMBA based anymore so any familiarity with the open source equivalents is now for naught as well. Good Luck and keep posting, I guarantee other System admins are running into similar or will be running into similar issues too.

There is a problem due to Lion not supporting byte level file locking on its implementation of SMB which is required by some older applications such as Excel 2003, Pastel accounting, Caseware and MS Access.. Upgrading the app sometimes fixes it as in Excel. If the latest release does not resolve it then the only way I have found to fix this is to disable SMB sharing in Lion and install a 3rd party SMB sharing stack. I tried Samba but could not get it to work properly even using SMBUp which takes a lot of pain out of the install. I eventually found an app from Thursby.com called Dave which works really well, unfortunately it does not integrate with OpenDir at this stage but you can create users in the local dir for use with Dave while continuing to use OD for other stuf. Ive asked their developers to consider adding LDAP support. If more people ask then they might do it. The big downside is that it is really expensive at over $100.

I've not tested Lion 10.7.3 update yet to see if it resolves the file locking problem. I plan to test this during the week.

Just before installing 10.7.3 I started experiencing major performance issues with Dave9 as the user access count increased. The response from a support call to Thursby is that this software is not designed for a server environment and only meant for use on a client. I'm glad I was still in evaluation period and had not purchased it.

Anyway, I installed 10.7.3 server update and it does fix most of the SMB sharing issues, however I still had one application (Caseware) that did not work. So it seems that 10.7.3 will probably do for most installations but in this case I was forced to bite the bullet and get my head around Samba (which I had been avoiding). I found a fantastic app that streamlines the Samba installation on Lion called SMBUp. I recommend using this as the Samba installation can be quite daunting without it.

Once installed I just had to read the Samba docs to figure out how to get the sharing to work properly. It is a bit tricky if you want some sort of security. Currently I'm using a single local UID for sharing but plan to configure the LDAP integration soon which will allow OpenDir users to connect. I've developed new respect for Samba as it really has some powerful features for integration into a MS Windows network.

Testing with Samba cleared up the performance issues as well as the SMB access issues. You can still install 10.7.3 update for the other enhancements, but just disable SMB and uses Samba if required. For future clients I will start with 10.7.3 SMB sharing and only use Samba if I need advanced MS network integration or have sharing issues with some old client software.

I'll post an update on the configs for local and LDAP config soon if anyone shows an interest.

TRA2010 thank you for your in depth explanation.

I am having a similar problem with Autodesk 3DS Max files, if I try and open them off the Lion Server from a Windows PC I get a 'File Opening Error'.

I was considering trying the SMBup, but have a couple of concerns. The first is that they instruct you to uncheck 'File Sharing' in the system preferences. I can't do this as we also use AFP, do you know if it will still work if you just uncheck the SMB sharing in the oprions part of the 'File Sharing' system prefence panel?

Also, how easy is it to return the SMB back to the original state if it turns out that it doesn't solve our problem?

Many thanks in advance.

You can just disable SMB sharing under system prefs and leave AFP active.

I've switch back to SMB for testing even with Samba installed, you just need to stop Samba and then enable SMB in system prefs. It's no problem to reverse this. I've also successfully uninstalled Samba with no issues, you might just need to reboot.

Please indicate if any of these messages solve your problem.