Planning on switching to an SQL Backend

[akc42]

I am currently looking after an Access Application for a client that has security built into a new .mdw file and is split across front end/back-end. The front end currently has about 70MB of forms,code,reports and queries, the backend has about 100MB of data. The number of users fluctuate between 4 and 10.

The client is running using Access 2003 in Windows XP - I use Access 2007 in Windows 7 to do initial work on my laptop.

I would now like to explore the use of SQL-Server - and as far as I can work out - the Express Edition should be adequate, but I have little experience of it so far. I have however set it SQL-Server 2008 R2 (Express Edition) on my laptop.

Firstly - I can connect to the server on my laptop and create databases with the SQL Server Management Studio bundled with it. It says it is using the Windows Authentication to connect

However any attempt to connect from access is rejected with it failing to connect. I am not sure
a) Why - am I missing something like and odbc driver or similar
b) What permissions should I be using - and how to set them up so I can control connection.
The error message says [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

I have tried this both selecting "Use Windows NT integrated Security" and "Use a specific user name and password" - entering my logon (to Windows 7) username and password.


Secondly - I am thinking (since that appears to be the recommended way) of creating an ADP as the approach to do this. It appears from the what I have been able to read that ultimately all the data - including forms and reports will be loaded from SQL Server, so does this mean that the previous benefits of shipping the front end from its copy on the server to all the client PCs when they boot up at start of day will no longer be necessary. Do I just deploy the .adp file from a network share on the server?

Lastly - I can find nothing about security and how it might be related to what I have in the .mdw file. My current .mdw file has a user entry for everyone, the standard Admins and Users groups - with Admin having all rights and Users None, and about 4 or 5 specific groups for the client staff. All access is managed through these groups. There are a few reports and forms (and their associated queries) which we want to limit - so only a specific group is allowed to access them. There is also a general "staff" group which all current users are members of - which is given access to all (and all new) reports, forms and queries - design access is restricted - to prevent them altering things they shouldn't. Is an .mdw file associated with an ADP - or do I have to control security another way?

 

[HiTechCoach]

ADP's have been depreciated and are not longer support after Access 2003.

Microsoft has actually been discouraging the use of ADPs for years now. They recommend using an MDB/MDE or ACCDB/ACCDE.

The built in User Level Security (ULS) has also been depreciated. It is only supported with an JET (.MDB/.MDE) database. The new 2007/2010 ACE (.accdb/,.accdb) format does not support ULS/workgorup files.

IMHO, If you build databases with ULS or create ADPs you are using features that have reach their end of life which is not a wise investment.

 

[HiTechCoach]

If you are really considering switching to an SQL server back end then I would build your front end using Access 2010 with SQL Server 2008 R2. Access 2010 has improvement for working with an SQL Server.

TIP: You want to make sure the Access version is newer than the SQL Server version, Example: Access 2003 works great with SQL Server 2000 but does not handle SQL 2005's new features. Just like Access 2007 works better with SQL Server 2005 than the newer SQL Server 2008.

About Security:
I would use Active Directory for your security.

 

[akc42]

Thanks for the tips - given that this is early days of the experiment, I am not constrained to do things one way or the other, so I am not wedded to an ADP - it just seemed from my other reading around the net to be what was recommended. How do I make the split between front end and back end if I use the other formats?

I am not yet sure how practical it is to get the client to upgrade versions - and whilst I am sure that it would be better to do so, I would like to explore working with what I have got first. It is not clear from what you are saying - my failure to get Access 2007 talking to SQLServer 2008 R2 - is this a limitation of the versions or just me doing it wrong?

Finally, to your comment about security. I don't know enough about Active Directory to know what you are saying, but I assume this is just a relationship between the account that the user logs in with and Access - but how to I provide that linkage? As far as I know, at the moment Access requires the use of the workgroup file (or just defaults to system.mdw if you don't provide one), and only provides facilities to set permissions based on the users/groups found within it. I have only used .mdb format files though. Can you be more specific about what you mean and how to achieve it.

 

[HiTechCoach]

I am really curious why you think they should migrate to a SQL server back end?

It is not clear from what you are saying - my failure to get Access 2007 talking to SQLServer 2008 R2 - is this a limitation of the versions or just me doing it wrong?

Probably some of both.

If you are using Access 2007 then you want to use a version of SQL server that is not newer the Access 2007. You should probably go with SQL Server 2005.

If you really want to test and learn about SQL server then I would urge you to run the SQL Server on separate machine. This way you can use it across a network connection like in a normal production environment.

Finally, to your comment about security. I don't know enough about Active Directory to know what you are saying,

Is your client using a Domain with a PDC?

 

[akc42]

I am really curious why you think they should migrate to a SQL server back end?

Three reasons really - One - some worries that the current application is loading the network up pretty severely - and it also means one of our remote offices only choice is to come in as a remote client. Two - I am currently worried about manually needed to wait until all users are offline and then having to compact and repair the backend database on a regular basis. Three - everyone who I've spoken to who does this more regularly than I do has said they would recommend it (I know - I like to understand the why more than the what, and those that can't explain the reasoning don't normally get listened to - but in this case a lot of diverse people have said it would provide a more stable environment for what is a mission critical system).

Probably some of both.

If you are using Access 2007 then you want to use a version of SQL server that is not newer the Access 2007. You should probably go with SQL Server 2005.

If you really want to test and learn about SQL server then I would urge you to run the SQL Server on separate machine. This way you can use it across a network connection like in a normal production environment.

I have two working environments - at home I fundamentally have a linux setup with multiple machines (including my desktop) running it. The only windows machine is my laptop - so I am somewhat limited.

At the clients office, that is precisely what I plan to do. Although I am not responsible for, or know much about the infrastructure.

Is your client using a Domain with a PDC?

I believe so - although the setup is not correct and will need to be changed - but as I said above not my area of expertise or responsibility.