Access Front End on SQL Server Back end?

[darren_access]

Morning colleagues,

I have built a couple of modules in Access 2016 using the split db method.
A couple of questions now come to mind as more functionality is being asked for...

1. The client would like to log all activity
2. They're going to need more transactional type functionality as multiple users begin adding sales orders into the system. I foresee unwanted overwrites and other issues arising.
3. system stability will become an issue as the company grows

It's been a few year since I used SQL Server 2008 and Management Studio, but I believe it could address these issues.

1. They don't want to go to web application yet. Is it possible to run the same Access front end against the sql back end?

2. Alternatively is it possible to create a web browser front end that access the sql backend on mapped network drive? I have avoided using much VBA.

3. Does SQL server provide comprehensive logging out of the box, or will I still need to create application logs to capture inserts, updates and deletes?

Client has no budget for full SQL Server or sharepoint as yet and Cloud is being considered but longer term

 

[CJ_London]

They don't want to go to web application yet. Is it possible to run the same Access front end against the sql back end?

yes - look on the ribbon there is an upsizing wizard. Note to take best advantage of sql server features, you will no doubt want to move some processes to the server itself and also make use of things like stored procedures (you use vba for the equivalent in access)

Alternatively is it possible to create a web browser front end that access the sql backend on mapped network drive? I have avoided using much VBA.

yes - but a steep learning curve if you have not done it before. in access much is done for you, so you have been able to avoid vba, in a web development, you do everything yourself. You cannot convert access to a web app so will need to start from scratch, allow 20-30 times longer for development.

Does SQL server provide comprehensive logging out of the box, or will I still need to create application logs to capture inserts, updates and deletes?

you do everything yourself, so nothing out of the box

if you want to try sql server FOC, try sql server express. It has most but not all the features of sql server. If going to cloud, consider sql azure, access front end will work with it

 

[darren_access]

Thank you CJ!

 

[SQL_Hell]

Does SQL server provide comprehensive logging out of the box, or will I still need to create application logs to capture inserts, updates and deletes?

Nothing that you can just turn on, but here are the options for SQL server:

1. SQL server audit
2. SQL Server change data capture
3. SQL Server extended events
4. Tracing
5. Triggers
6. Custom auditing via stored procedure / application code

(at least these are the ones I can think of right now).

When deciding the correct approach it all depends on how complicated your application is, how much data changes and how many users there are.

It is probably worth considering whether you are using windows authentication or whether all users are logging on as 'admin', it will be impossible to audit data changes if everyone is using the same account.

Also be aware that moving to sql server linked tables will inevitably lead to some code changes and different ways of dealing with record sources for forms and reports.