Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    18

    Unanswered: Front End Help - Access?

    Hi,

    I'm looking for a simple front end to use for SQL Server Express (2012) that integrates tightly with Microsoft Office 2013 Professional.

    On that basis, I'm thinking of using Access. I should explain, my technical knowledge on databases and front ends etc is limited, so please bare with me. I should also mention that I know the basics of VBA and SQL, not much more, before you nail me with brilliantly technical responses
    The SQL Express instance will be hosted on a central server. Users connect to this server via RDP, but can also connect to it as a mapped network drive; in both cases this is done from remote locations via private IP WAN links (minimum of ~1.5Mbps, low latency).

    My first question:
    Whats the best location to host the front end?
    The options I can see are for the user interface to be installed on the server (so users connect via RDP to the server, and then use the front end from within the RDP session), or it is installed/executed locally on their PC and the front end connects to the SQL instance that is running remotely. Is one preferable to the other for some reason?

    Second Question:
    What is the accepted 'best practice' for using Access as a front end to SQL Server?
    Can I just use Access' linked tables wizard via an ODBC driver?
    What about ADO, DAO ADP.....I was a bit lost by some of the arguments that I read from people about these things...?

    Third Question:
    What functionality does Access allow to an SQL backend?
    Is it just a basic CRUD type of functionality?
    Can you use all the features of the SQL server backend (i.e. for more complex queries) when using an Access front end, is this done via pass-through queries or stored procedures, or both?
    Can users create tables within the SQL instance from the Access front end, or does that need to be done via more complex software (SSMS), or perhaps I could link Form buttons to pass through SQL code that creates tables, changes field types etc?
    Would I lose the FGAC of SQL Server? (i.e. If using Access, it only supports basic user access control like table locking or something when using this arrangement...)
    Would Access support both materialised views and conventional views that would allow the underlying table(s) of the query to be edited via forms that look at the query results?

    Fourth Question:
    What are the pitfalls, traps and tricks for an Access - SQL Express system?
    By that I mean is it a case of, 'it would work fine as long as [insert technical clause 1 through N here]'
    What are the things I would need to avoid?
    (For instance allowing queries with no selection criteria that allow heaps of data to be passed from the back end to the front end via low bandwidth links, due to insufficient specificity in the selection query etc...)

    Really, I'm just looking to provide some simple forms and reporting tools to users who are familiar with the MS Office environment. They're not DB user's, they don't know SQL, and they certainly aren't about to rush me to uptake SSMS or something like that...
    Last edited by Simple_One; 10-11-13 at 01:24.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since your questions are 90% Microsoft Access and only 10% Microsoft SQL Server, I've moved your thread to the Microsoft Access forum where it ought to get more appropriate answers.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Simple_One View Post
    Whats the best location to host the front end?
    Each client receives a copy of the front-end.
    Quote Originally Posted by Simple_One View Post
    Second Question:
    What is the accepted 'best practice' for using Access as a front end to SQL Server?
    Can I just use Access' linked tables wizard via an ODBC driver?
    That's the easiest but not the most performant.
    Quote Originally Posted by Simple_One View Post
    What about ADO, DAO ADP.....I was a bit lost by some of the arguments that I read from people about these things...?
    These solutions require more programing on both sides (SQL Server and Access) but they are more efficient than using attached tables
    Quote Originally Posted by Simple_One View Post
    Third Question:
    What functionality does Access allow to an SQL backend?
    Primarily (i.e. without programing): linked (attached) tables only.
    Quote Originally Posted by Simple_One View Post
    Is it just a basic CRUD type of functionality?
    Can you use all the features of the SQL server backend (i.e. for more complex queries) when using an Access front end, is this done via pass-through queries or stored procedures, or both?
    Using SQL pass-through queries, you can do (almost) everything you want. Stored procedures are more efficient, though.
    Quote Originally Posted by Simple_One View Post
    Can users create tables within the SQL instance from the Access front end, or does that need to be done via more complex software (SSMS), or perhaps I could link Form buttons to pass through SQL code that creates tables, changes field types etc?
    Yes, see above.
    Quote Originally Posted by Simple_One View Post
    Would I lose the FGAC of SQL Server? (i.e. If using Access, it only supports basic user access control like table locking or something when using this arrangement...)
    Would Access support both materialised views and conventional views that would allow the underlying table(s) of the query to be edited via forms that look at the query results?
    Yes, the only requirement being that there is an Identity column for each table in the data set.
    Quote Originally Posted by Simple_One View Post
    Fourth Question:
    What are the pitfalls, traps and tricks for an Access - SQL Express system?
    Attached tables are easy to use, Pass-Through queries requqire more programing. For me, the first trap was trying to work with a linked table having a computed column.
    Quote Originally Posted by Simple_One View Post
    By that I mean is it a case of, 'it would work fine as long as [insert technical clause 1 through N here]'
    What are the things I would need to avoid?
    Use stored procedures as often as you can, i.e. make the SQL Server perform as many tasks as possible
    Have a nice day!

  4. #4
    Join Date
    Sep 2013
    Posts
    18
    Thanks very much for the feedback

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •