Results 1 to 11 of 11
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Trying to understand SQL Server and Access integration

    Hi all,

    this might be a broad topic, but I feel like I need some insights.

    I want to create a small company database using MS Access as a front end and SQL Server Express as a back end. I know my way around Access and VBA quite well, and I just read this huge book about SQL Server (https://www.ebay.com/itm/284288727407), so I think I have a good foundation for my project.
    I was also able to connect Access to the server using an ODBC connection, I can run pass-through queries, link tables and views and so on.

    What I'm looking for in this topic is this: WHICH SQL SERVER OBJECTS ARE BEST FOR WHAT TASKS?

    The application will have all the common functionalities:
    • Continuous forms (order list, customer list, invoice list, employee list...), including calculated valus like order totals... Read only
    • Single forms with Subforms (order details, customer details...), including calculated values... Read only
    • Data entry forms - basically all data will be inserted and manipulated via these forms (for example when I'm on a read only order details form, I press the EDIT ORDER button and the data entry form appears, or when I am on the order list form and I press the NEW ORDER button)


    And there are so many ways to access this data with SQL Server... There are:
    • Linked tables/views
    • Pass-through queries
    • Stored Procedures / Functions
    • I also wanna use triggers and so on


    So for read only forms, do I use linked views? Or a pass-through query that runs the T-SQL statement I want?
    For data entry forms, do I use linked tables? Will I be able to work with subforms like that?
    What about concurrency? Do I run transactions when I press SAVE ORDER / CANCEL ORDER buttons? Or do I let Access handle this?
    Is it a good practice to create stored procedures for all tasks and then execute them via a pass-through query? (because of the lack of formatting of pass-through queries)
    Should I not use Access queries at all?


    Like I said, I know this is not an easy question, and there's not a single correct answer, but I am just looking for some kind of a concept as of I should approach things. And your personal experience maybe.

    Thanks a lot for explanations :-)



    Best regards,
    Tomas

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have all sql tables. it keeps the data safest.
    some local access table for minor data fixes.

    the easiest is DSN, make odbc connection, then load your tables. same as using internal access tables.

    I only have 1 pass thru query do to its lots of calculations. Otherwise internal access queries work fine.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would recommend using DSN-less connection strings. Possibly slightly more work initially but less in the long run.
    The main point with regard to handling the data is to ensure as much data manipulation as possible is done at server level.
    This will minimise network traffic and ensure optimal performance.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Quote Originally Posted by isladogs View Post
    I would recommend using DSN-less connection strings. Possibly slightly more work initially but less in the long run.
    The main point with regard to handling the data is to ensure as much data manipulation as possible is done at server level.
    This will minimise network traffic and ensure optimal performance.
    Thanks, how would I go about that?

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The DSN-less approach is basically the same as that used by the linked table manager
    It avoids the need for any separate ODBC connections

    I save all the linked table connection details in two local tables
    a) list of tables and the BE database for each
    b) connection details for each BE database

    I then use code to loop through each of the tables in turn and make the connections
    This only takes a few seconds even in a very large database with several hundred linked tables in more than one BE database

    As a starting point, have a look at these links
    Using DSN-Less Connections (accessmvp.com)
    DSN-Less Database Connections - Blue Claw Database Developer Resource (blueclawdb.com)

    As for optimising queries by doing as much processing as possible at server level, whole books have been written on the topic.
    Suggest you Google this.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Very interesting, it seems that I will do it like this.

    However I have a few questions:
    • The code should run only once or every time the front end is started?
    • What should I set as data sources for forms/reports?
    • How do I manipulate data? Do I only use DML (T-SQL) statements in the code?


    Thanks a lot.

    EDIT: I suppose that if I use Active Directory authentication, then I will use use trusted connection and be done with it?

  7. #7
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    I leave the access topics to the people that are far better with these and only advise on the SQL server side:
    * security: don't forget to setup SQL security correctly and disable the sa account. If you have several people using the app with different security profiles: best create a schema for each user group and set up security per schema
    * backups: as you are using SQL express you can't create a SQL job for that. Easiest way is using Powershell for your backup and database checks (https://sqlbackupandftp.com/blog/pow...r-database-faq)
    * database checks: minimum run a DBCC checkdb on a regular base
    * data manipulation: if you use linked tables you can perform all data operations as they were in native access tables
    * indexes: on a SQL database using the correct indexes is very important for the performance. Don't index everything: don't forget that indexes speed up search actions but there is a penalty when updating data: not only the records in the tables get an update, but also the indexes. Don't index to little fields, or the server will perform a full table scan on every search. It's very helpful to read the SQL query plans to decide on the index strategy. Use included columns for order by needs
    * data sources for forms: pull in as little data are possible. You can open the form using a data source as select a,b,c... z from tblY where 1 = 0, so no data are pulled in on Opening the form. After opening the form let the user search the data they want to see. Try to avoid the lazy select * , but specify the columns
    * data sources for reports: create views on SQL server. Tip: for summary reports you can create indexed views with the summary data.
    * for data manipulation: avoid using loops, there is a term for it: RBAR (Row By Agonizing Row). SQL server is batch orientated. https://www.red-gate.com/simple-talk...agonizing-row/
    * If you can avoid them, don't use triggers. They are row based and can cause a lot of problems in the front end. Example: when a trigger on table A changes data somewhere, you can get the following message after update: someone else altered the the data... .

  8. #8
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Thank you for the tips!

    I have a follow up if I may:
    * data manipulation: if you use linked tables you can perform all data operations as they were in native access tables
    --- isn't that what we are trying to avoid with DSN-less connection?

    * data sources for forms: pull in as little data are possible. You can open the form using a data source as select a,b,c... z from tblY where 1 = 0, so no data are pulled in on Opening the form. After opening the form let the user search the data they want to see. Try to avoid the lazy select * , but specify the columns
    --- Sorry but what do you mean by "let the user search the data"? I don't think I get it
    --- Can I simply run VBA code upon opening the form and populate it using a pass through query and T-SQL statement SELECT columns FROM..... ?
    --- And also, when I for example add new data and press SAVE, run another pass through query and T-SQL statement INSERT INTO..... ?

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    * if you connect the tables on startup through a VBA routine, you still end up with a set of linked tables.
    * in stead of linking the tables you can create a connection for each action and use a pass-through query, but be careful: making a connection is slow, plus on the SQL side you can create a new open process for each action . Last week I warned a developer who had more than 1000 SPID's running, I would boil him in hot oil when he didn't stop doing that. Luckily for him the price of oil has risen to much to do so.
    * with let the user search I mean : design a search form so the user only loads those data they actually want to work with. So the form starts with no data, and the user gets the data they want to work with through a search screen. You can use the TOP (100) expression to limit the number of data they ask for.

  10. #10
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250
    Ah okay, so when I do the DSN-less approach on startup and connect to the server using a VBA routine, I will get linked tables if I understand correctly? And I can reference them from forms, reports, queries... All calculations being done on the server side.

    As for the form search, I think it might be not so efficient, because this is a small database (hundreds of rows to display in continuous forms at maximum - unfiltered, maybe thousands, but never more...). If the forms load this kind of data directly from the SQL Server, I can't really see that it would affect performance much.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You don't need to relink each time you start. Once linked, the links will persist. Only relink again if you either change the BE connection string or if you alter any of the table designs e.g. add new fields.
    Even then you may be able to refresh the links using the linked table manager

    Agree with Noella's advice regarding SQL Server.
    Some additional points
    1. Indexing should be done on fields that will be searched or filtered regularly
    2. Avoid having to make/break connections each time a form/report/query is opened as that will reduce performance. Create a persistent connection by having a hidden form loaded at startup and keep it open whilst your database is running. The hidden form should be linked to a very small SQL table. Ideally one field and one record. It can be dummy data. The idea is that the connection remains open
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access KNIME integration
    By KNime in forum Access
    Replies: 1
    Last Post: 04-12-2017, 06:31 AM
  2. Access Infopath Outlook Integration
    By vaish in forum Access
    Replies: 1
    Last Post: 11-16-2013, 07:15 AM
  3. access integration help
    By vinwin06 in forum SharePoint
    Replies: 1
    Last Post: 06-19-2013, 04:29 PM
  4. Access 2007 - Integration with Excel
    By cat713 in forum Import/Export Data
    Replies: 3
    Last Post: 07-27-2012, 01:32 PM
  5. Access integration with word and outlook
    By Abacus1234 in forum Access
    Replies: 0
    Last Post: 11-10-2011, 10:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums