Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157

    Best practice while using Access in tandem with MS SQL 2019

    Hi all,



    I am planning on building a brand new system for a small company that I work at. I am currently thinking about the best possible implementation. I definitely want to use Microsoft SQL Server 2019 Express.

    I came across multiple scenarios how I could use it:
    • create the tables in Access and then export and link via an ODBC connection
    • create the tables directly in MS SQL, not have any tables at all in Access and link to them via VBA (using ADO recordsets)


    What would you recommend to me from these two? Any pros and cons?

    I feel like the second option will be better because MS SQL is more powerful engine than MS Access and I could utilize it to its full potential.

    Thanks for any insights.

    Kind regards,
    Tomas

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    811
    Definitely create the tables in SQL Server and link them to access. Take into mind that the SQL data types are different from Access data types, if you create them in SQL server you can pick the exact datatype you need.
    I would not only create the tables in SQL server but also make sure that the bulk of database work is done on the server side: generating the PK value (if PK-field is a int field, use the identity increment settings), add check constraints and defaults where necessary or useful, create views and use them to limit as much as possible the amount of data that will be pulled into the access front end, create procedures for your parameter queries, ...
    succes

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,302
    there other scenarios,

    create the tables et al in express then use passthrough queries (which is a DAO construct via odbc but sql syntax/design is sql server, not jet/ace)

    But agree with Noella - move data processing to express to minimise the amount of data brought back across the network

    Be aware that unless you are utilising more efficient methods available in express, don't expect any real performance improvement. And you are introducing a requirement for a higher level of database maintenance (i.e. not just a simple compact and repair), but I do believe the benefits outweigh the additional efforts.

  4. #4
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    Thanks for the replies.

    So I will create the tables using MS SQL, with its data types, field specifications, relationships and so on. That's now decided.

    I would like for MS SQL to perform most of the heavy work. Should I create all queries on the server then? How would that work? Is that what passthrough queries are for? I am not familiar with those.

    Will the tables be visible in the Access navigation pane with this setup? Can I refer to them from forms and reports, without using recordsets?

    Thanks again.

    T.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,048
    When you link a table in a remote database (SQL server, Oracle etc.) they appear and behave just the same as a linked table in a separate access back end(Apart from the icon being different!).
    Your forms etc. all behave the same, you can create queries on those tables with no difference.

    Complex queries with lots of joins should probably be created as Views on the server. These you then link these as another table in the front end. They can be editable if you include primary keys, and they are set up correctly.
    These will perform much better than a local query, and allow you to use the power of SQL Server to generate the results.

    Generally a passthrough query would be used when you need to adjust the parameters. Views cannot accept parameters.

    So perhaps you create a View that returns all your invoice records - vw_AllInvoiceRecords .
    You want to only see them for a specific customer so you could create a pass through and adjust the query def in VBA to produce something along the lines of

    SELECT * FROM vw_AllInvoiceRecords WHERE Customer ID = 6

    This would then produce a result set that was restricted to the single customer.
    (It's not a great example but you get the drift)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,302
    Really depends on what is going to work for you - suggest google passthrough queries in Access and also stored procedures in sql server.

    Be clear as to why you are upsizing, the usual reasons are to do with better security and/or greater capacity, performance is at the bottom of the list but will be at the top of the users list. The expectation in return is action queries (append/update/delete) are typically one record at a time so forms etc are typically based on one record returned using criteria rather than a whole table that is then filtered (ditto for subforms, but they will usually return just the records required) and imports are either handled within sql server or by parsing the import recordset and inserting/updating one record at a time.

    Will the tables be visible in the Access navigation pane with this setup? Can I refer to them from forms and reports, without using recordsets?
    No - aside from the fact you should not be linking forms/reports directly to tables. You would need to use linked tables and DAO, but will generally perform slower than linking to an jet/ace backend.

    Suggest you set up a test db in sql server express and experiment

  7. #7
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    Thanks!

    I just created a sample SQL Server database and a simple Access front end to play around with.

    I was able to do everything that's been said here, see attached:

    The server setup:

    Click image for larger version. 

Name:	db1.PNG 
Views:	20 
Size:	6.1 KB 
ID:	46392

    Sample front end:

    Click image for larger version. 

Name:	db2.PNG 
Views:	19 
Size:	26.5 KB 
ID:	46393

    Local query:

    Click image for larger version. 

Name:	db3.PNG 
Views:	20 
Size:	10.4 KB 
ID:	46394

    SQL view imported as a linked table:

    Click image for larger version. 

Name:	db4.PNG 
Views:	19 
Size:	12.8 KB 
ID:	46395


    If I understand everything that's been said, then my best bet is to use local queries for simple stuff and the second method for complex stuff... Right?

    Or should I use only the second method?

    And as for pass through queries, I need those if I want to parameter the stuff in the second method, that's the only use for them?

    Again, thanks a lot

    T.

  8. #8
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    Just to follow up a bit, I tried to code some simple recordset for this scenario:

    Click image for larger version. 

Name:	db5.PNG 
Views:	19 
Size:	24.2 KB 
ID:	46398

    I used the server View as a source and I was able to use WHERE clause just fine. I really don't see why I would need a pass through query...

    Can you please elaborate? Thank you.

  9. #9
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    811
    Hi,

    to speed things up: when working with records on SQL server: SQL has a batch approach, not a row by row and loop approach. This is begging the server to go slow and on a tuning project the first thing I do is look at this and advise the development department to replace these with SQL batch processes. Never update/delete/insert row by row on a SQL database, but use joins and criteria to get the set and do the actions for all records in 1 operation.
    To speed up 2: use procedures for parameter queries and not pass-through queries. When using pass-through queries, the server will treat them as ad-hoc queries and build a new query plan every time you execute the query. Using stored procedures the server engine sees it as the same query because the parameter is evaluated inside the procedure, so it can reuse the cached query plan.
    To speed up 3: create the correct indexes, as a rule: index all foreign keys, choose wisely your clustered index: best choice is in 99% of the cases your primary key (mostly an int or uniqueidentifier). To look for other indexes: switch on the option "Include actual query plan", or for action queries you don't want to really execute in speed tests: display estimated execution plan. In a lot of cases this will include a hint about missing indexes. With non-clustered indexes you can include the columns you need to fetch, so the server doesn't have to do a lookup in the clustered index.

    A warning: if you have set up your database using a full recovery model, be sure to take regular log backups, or the log file will become to big for your harddisk and the server will block.

  10. #10
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    Quote Originally Posted by NoellaG View Post
    Hi,

    to speed things up: when working with records on SQL server: SQL has a batch approach, not a row by row and loop approach. This is begging the server to go slow and on a tuning project the first thing I do is look at this and advise the development department to replace these with SQL batch processes. Never update/delete/insert row by row on a SQL database, but use joins and criteria to get the set and do the actions for all records in 1 operation.
    To speed up 2: use procedures for parameter queries and not pass-through queries. When using pass-through queries, the server will treat them as ad-hoc queries and build a new query plan every time you execute the query. Using stored procedures the server engine sees it as the same query because the parameter is evaluated inside the procedure, so it can reuse the cached query plan.
    To speed up 3: create the correct indexes, as a rule: index all foreign keys, choose wisely your clustered index: best choice is in 99% of the cases your primary key (mostly an int or uniqueidentifier). To look for other indexes: switch on the option "Include actual query plan", or for action queries you don't want to really execute in speed tests: display estimated execution plan. In a lot of cases this will include a hint about missing indexes. With non-clustered indexes you can include the columns you need to fetch, so the server doesn't have to do a lookup in the clustered index.

    A warning: if you have set up your database using a full recovery model, be sure to take regular log backups, or the log file will become to big for your harddisk and the server will block.

    Hi, thanks for the tips.

    1) So you recommend that I drop recordsets entirely? What exactly are SQL batch processes? I've never heard of it. The system that I will be creating is relatively small. It will be accessed by 3-5 people max (total, not simultaneous), and we're talking tables with maybe thousands of records, not more. Is it worth it to learn such new stuff for this?
    2) Procedures you mean "stored procedures"?
    3) Yes, that's already planned to do

  11. #11
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    811
    Hi,

    1) you can use recordsets in your application as long as they are used to do things in the front end app and are not used to get/update/insert/delete records one by one from or on the server side. Check the RBAR (Row By Agonizing Row) discussions on the net. Batch processes are SQL instructions where you define a complete set of records and than treat them all together in one movement or batch.
    It is definitely worth to learn about SQL server if you are planning to use it, even with only 100 records. Not only will your application grow and it will save you much time to use your environment to the full, but it's necessary for having the correct maintenance (see my warning).
    2) indeed, I mean stored procedures
    3) extra tip: lookup the possibilities of SQL reporting, which you also can download for free. It offers the possibility to publish and distribute your reports on the web.

  12. #12
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    Thanks for the insights. I did some research today (I didn't get to RBAR yet) and I have some clarification questions if you guys don't mind.

    1) How exactly would I use a recordset to do things in the front end app? I couldn't come up with a scenario like that. Recordsets always interact with the back end, do they not?
    2) Ok thanks
    3) Isn't that only included in the Enterprise version of SQL server? I will be using the Express version. What exactly does it mean, distribute my reports on the web?

    Also a few other thoughts... If I understand things correctly:
    • All tables will be hosted on the SQL server, linked to the Access front end via ODBC
    • All queries that I will be creating will be hosted on the SQL server as well
      • queries with no criteria (like an order list with calculations) will be created as Views on the server, linked to the Access front end as "tables"
      • queries with criteria will be created as stored procedures and called via pass through queries from the front end

    • Forms, Reports - self explanatory
    • Modules - I will be calling either a pass through query or a linked View


    Or what is the best way to access things? No pun intended haha. I mean, the best way to access Views and stored procedures from the front end (forms, reports, modules).

    Thank you!

    P. S. - I am currently reading this book: https://www.amazon.com/Microsoft-SQL...4162093&sr=8-2
    That should clarify some things a bit.

  13. #13
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,302
    what is the best way
    quite frankly that depends on what you are going to do. It is like asking 'what is the best way to get from London to Edinburgh?' Fly? No, too expensive. Drive? No, I don't have a car. Bus? no, too slow. etc. And 'best' in what context? easy to write? performance? resource availability? ease of deployment? low maintenance?

    Each method has its advantages and disadvantages - just as express and jet each have their benefits and costs. So you might use one method for one action and a different one for another - or you might have to for reasons set by some other authority. You've been pointed in the direction for where to look, now you need to read and understand the implications and perhaps try different methods and see what works for you.

    Not trying to put you off, good to see you are reading up. Learning to use SQL server (express) is not that difficult since there are plenty of similarities with Access - but there are things sql server can do that access can't (or can't without the use of VBA) and things it will do better.

    Main principles are simple for good performance:

    1. keep data processing on the server, not in access front end
    2. minimise the amount of data that needs to be returned to the FE

    Which applies to using any database, including access - and as I have mentioned before, don't expect a huge leap in performance from a user perspective, the main benefit of express over jet/ace is improved security and capacity, and I would throw in a lower risk of corruption as well.

  14. #14
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    157
    I'm looking for the middle ground for a small application. I don't expect to get the definitive answer here, but rather hints to point me in the right direction.

    I don't need the BEST performance and I am willing to implement more complex solutions, but within reason because I am NOT an IT person per se.

    So, it probably wasn't a good idea to ask "what is the best way", but I think that based on the entire post, it's quite obvious what I'm looking for.

  15. #15
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,048
    I would say dive in and make a start.
    You seem to have a good grasp of the basic concepts.

    I wouldn't get too hug up about moving every single query to SQL, as it's often easier as a new user to knock those up in Access, especially during development to prove a concept.
    More so if the data returned is small and simple to drive a single rather than continuous form.

    If you notice it getting slow - look at moving it to the back end.

    Make local copies of tables that don't get updated very often (if ever), lookup lists of counties or states for instance. When these are loaded into combo boxes locally they are much quicker.
    Keep them in sync with an update routine every time the database opens.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-03-2021, 09:40 AM
  2. Replies: 9
    Last Post: 02-13-2021, 09:14 AM
  3. Replies: 4
    Last Post: 09-13-2020, 01:36 PM
  4. Access 2019 Desktop version
    By mainerain in forum Access
    Replies: 3
    Last Post: 08-26-2020, 12:06 PM
  5. MS Access 2019 aesthetics
    By shank in forum Access
    Replies: 1
    Last Post: 10-17-2019, 04:04 PM

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