Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2018, 07:48 AM   #1
darren_access
Newly Registered User
 
Join Date: Jan 2018
Posts: 57
Thanks: 3
Thanked 0 Times in 0 Posts
darren_access is on a distinguished road
Access Front End on SQL Server Back end?

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

darren_access is offline   Reply With Quote
Old 02-13-2018, 08:00 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,338
Thanks: 39
Thanked 3,346 Times in 3,241 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Access Front End on SQL Server Back end?

Quote:
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)

Quote:
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.

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
darren_access (02-13-2018)
Old 02-13-2018, 08:03 AM   #3
darren_access
Newly Registered User
 
Join Date: Jan 2018
Posts: 57
Thanks: 3
Thanked 0 Times in 0 Posts
darren_access is on a distinguished road
Re: Access Front End on SQL Server Back end?

Thank you CJ!

darren_access is offline   Reply With Quote
Old 02-13-2018, 03:25 PM   #4
SQL_Hell
SQL Server DBA
 
SQL_Hell's Avatar
 
Join Date: Dec 2003
Location: UK
Posts: 1,323
Thanks: 15
Thanked 39 Times in 37 Posts
SQL_Hell will become famous soon enough
Re: Access Front End on SQL Server Back end?

Quote:
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.

SQL_Hell is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access Front End to SQL Server Back End ChiOscar Theory and practice of database design 1 03-06-2013 11:56 AM
Access Front-End, SQL Server Back-End whdyck General 4 09-18-2012 05:48 PM
Access Front End SQL Server Back End rachelkm2 SQL Server 4 11-29-2011 07:54 AM
Linking 2003 ACCESS front end to SQL Server 2005 back end mapat SQL Server 2 04-27-2009 05:02 AM
Access Front End and SQL Server Back End Kevin_S General 12 04-25-2003 08:25 AM




All times are GMT -8. The time now is 10:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World