Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    sparkyinak is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    22

    SQL Express ignorant - Is it beginner friendly

    It appears that I am going to have to make the jump to SQL Express for my Access back end, for my growing Access DB file in order to share it with my co workers. Although I know what SQL is, I have zero experience with SQL code and servers. Thus I have many know questions let alone more questions that will pop along the way. Some of the questions I have are:

    1. SQL Express is a stripped down version of the full blown program. How do I know SQL Express is good enough for my Access needs? I do not want to necessarily go through all the hubbub of downloading, take a figurative crash course to find out SQL is not enough
    2. Is SQL is enter level enough for me? I know my way around enough Office to fill in the unknown blanks but I'm totally a newbie with SQL.
    3. Presuming I can clear the first two questions, will SQL Express be all I need or do I need separate server space?
    4. Can more than one authorized user access the SQL file via SQL Express


    5. Since it is web base, will it run on both Windows and OS?
    6. AM I biting off too much as a Green horn?

    Thanx in Advance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SQLServerExpress has a size limit of 10GB. Will that be enough? If not, might look at MySQL, also a freebie.

    What do you mean by 'SQL' - Structured Query Language is used by Access and other database applications. SQLServerExpress is a database software, not a language.

    My 2 attempts to install and test SQLServerExpress on my laptop failed as too complicated for me to figure out. IIRC, have to set up a virtual 'server'.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    SQL Server Express is the entry level version. It has been more than sufficient for my needs as a developer and for my clients for over 10 years.
    Whilst it lacks some features of the paid versions, it has the 10GB size limit already mentioned (Access is 2GB limit) and is much more secure than Access.
    Like any new software, there is a learning curve and it is likely you will only scratch the surface of its many powerful features.

    There are several options available for download.
    Recommend you download the sql server management studio (SSMS)
    I would read carefully about the different options involved before installing.
    E.g. Use Windows (not SQL Server) authentication.

    You can have many simultaneous users each with their own copy of the Access FE linked to the SS BE.
    Performance should be more stable and reliable than many users with an Access BE.
    It should not slow down with many users. Nor should it crash.

    If you use the basic sql server driver supplied with Access and DSN less connection strings, there will be no need to configure each workstation.

    I don't understand your point 5. It isn't web based.

    Converting your Access BE to SS will possibly take some time but there is a wizard to guide you.
    Do bear in mind that if you have any of the following datatypes, these cannot be imported into any other type of DB such as SS.
    Attachment / calculated / multivalued fields will all need converting before importing to SS.
    Last edited by isladogs; 05-28-2019 at 01:29 AM. Reason: Extra info
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How do I know SQL Express is good enough for my Access needs?
    SQL Server/Express is much more powerful than the Access database engine (ACE)

    will SQL Express be all I need or do I need separate server space?
    Since it is web base, will it run on both Windows and OS?
    SQL server is BE only - tables/queries, you will still need a front end application to access those tables and queries. Web based is a different requirement and brings with it many new (steep) learning points - it is the front end that needs to be 'cross platform' so you would either need to completely rewrite your app in html etc to run in your browser, or use something like citrix or terminal server. All come at a cost.

    The 'easy' option is to move to citrix/terminal server - other than modifying paths for your linked tables, there is nothing else you should need to do. Note that if moving from a single user to multiple users then you may need to modify how your db works, provide additional functionality around different screen sizes/resolutions etc. Because of the way it works, you are less likely to be affected by performance issues with slow connections.

    Can more than one authorized user access the SQL file via SQL Express
    yes - as they can with access. The difference is the number of users who can access the BE at the same time - access has a limit of 255 users although depending on how well the app is written you may start to see performance degradation in the low teens.

    Sounds like you have a stand alone, unsplit database you want others to be able to use. In a multi user environment, it is essential the DB is split - the BE (Back End) contains tables, the FE (Front End) contains forms, queries, reports, code. If you move the BE to SQL Server/Express, you can move the queries to the BE, perhaps together with some of the code functionality around managing process flow.

  5. #5
    sparkyinak is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    22
    June 7: To answer your question by what I meant by SQL. Outside of working with Access over the last few months, I have not dealt with SQL and SQL programs/programming. Unlike Office programs where they are similar in style and function where one can work their way through problems, SQL Express and other SQL type programs, I have zero experience so I’m clueless till I fire up the program the very first time.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Aside of database size and performance influencing options like usable RAM or number of cores, the only really essential feature which is available in SQL Server and is not included in SQL Server Express is the possibitity to create Job's.

  7. #7
    pdanes is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2019
    Posts
    208
    It's definitely not beginner-friendly, but it is learnable. You will need to do some studying, and there will be insanely frustrating moments, but many people have learned it. It's not impossible, by any means, nor do you need expert-level schooling and tutoring, and don't let snarky remarks from so-called experts discourage you. There are such toads, who will tell you to go back to your baby database (Access), to not play with tools you don't understand or similar disparaging remarks. Ignore them - they have nothing useful to offer, and will only discourage you if you let them. But it's not a cakewalk - you will need to work at it.

    There is an unbelievable amount of information available for free, from people who eat, breathe and sleep SQL Server. They have blogs, video lectures, tutorials, are active in SQL Server forums, and are happy to help people. I have benefitted immensely from such material, and as I have learned, I have also started paying it forward, helping the next beginners. https://www.sqlservercentral.com/ is an excellent place to start.

    One thing nobody else has mentioned, and that is scrolling. Access applications are often written similarly to a spreadsheet, where an entire recordset is made available, and users scroll up and down through it. Such an approach will not work well with SQL Server, if the recordset is of any appreciable size. All data must be fetched from the server, across your ethernet connection, and that is orders of magnitude slower then opening a local table in your own machine.

    The normal arrangement for a situation like you describe is a separate machine somewhere central, that runs all the time, or at least all the time that anyone is 'in house' to use the application. Your Access application is modified to communicate with this server via an ODBC link, and the linked tables look very much like ordinary Access tables, and may be manipulated in many of the same ways. But not in ALL ways - you will need to re-code some stuff to take into account that the data is physically elsewhere, and possibly being accessed by some other user at the same time.

    Exactly how and how much all this will differ is application-dependent. How your users work with the data affects how your application will retrieve and present it. There is no cookie cutter, that will allow you to just plug in your application and it will tell you what to do. You will need to learn, do the development work, make the mistakes, learn your own style for working with SQL Server, understand the difference between server-based SQL, local Jet SQL and VBA methods of working with data.

    It's no trivial task, but if you like working with database technology already, this is a perfect opportunity to make the next step up and begin to learn how a full-featured, hairy-chested server-based database engine operates. It may well set you on a new career path, or at least open opportunities that were previously unavailable to you.

    My recommendation is to get on it, accept that you will make some mistakes along the way, and do your best to learn from them.

    Two points I would stress, very strongly:

    1. Get your users onboard right from the start. Explain that you are learning some new technology, and that there WILL (not may) be problems along the way. But close cooperation with them is the only way to be sure you are working in the right direction. You have to understand what they need, and they have to understand what you need to hear from them. That means meetings, demos, tests, experiments, blind alleys, failed attempts, miscommunications, repaired communications - the lot. Meetings should be as small as possible - best is one-on-one, you and a user sitting together and stepping through your application. Watch what they're trying to do - your application should, if at all possible, work the way they want and expect. Naturally, there are learning curves, but if the application does not behave the way they expect, fix it so that it does.

    2. Learn and implement a robust backup mechanism, as early as you possibly can. It doesn't have to be perfect, and probably won't be, but you can tune it as you go along. But have one, and test it regularly. It doesn't matter if it's clumsy or slow. It will probably be both, at least initially, but you will improve it as you learn. It DOES matter, very much, if you lose the users' data and can't recover it.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @pdanes
    I don't know whether your remarks were aimed at anyone here but I don't see any negative or condescending remarks from anyone who posted earlier.
    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

  9. #9
    pdanes is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    @pdanes
    I don't know whether your remarks were aimed at anyone here but I don't see any negative or condescending remarks from anyone who posted earlier.
    Nor do I see any such remarks. What's your point?

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    My point was that I felt that part of your comments were unnecessary in this context.
    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

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think Colin is referring to

    and don't let snarky remarks from so-called experts discourage you. There are such toads, who will tell you to go back to your baby database (Access), to not play with tools you don't understand or similar disparaging remarks.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Precisely that section...
    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

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote Originally Posted by sparkyinak View Post
    June 7: To answer your question by what I meant by SQL. Outside of working with Access over the last few months, I have not dealt with SQL and SQL programs/programming. Unlike Office programs where they are similar in style and function where one can work their way through problems, SQL Express and other SQL type programs, I have zero experience so I’m clueless till I fire up the program the very first time.
    If you have built queries in Access then you have worked with SQL and SQL programming. SQL is the language of queries which is used by multiple db platforms: Access, SQLServer, MySQL, Oracle and probably more I've never heard of. The language library has variation between platforms but certainly what is learned in one is transportable to another.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    pdanes must not have liked my statement about my experience:
    My 2 attempts to install and test SQLServerExpress on my laptop failed as too complicated for me to figure out. IIRC, have to set up a virtual 'server'.
    Didn't mean to imply OP shouldn't do it, just that it is not as straightforward as Access for initial setup and to expect a challenge.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    pdanes must not have liked my statement about my experience:
    Didn't mean to imply OP shouldn't do it, just that it is not as straightforward as Access for initial setup and to expect a challenge

    There was nothing wrong with saying that in my opinion. If you only install SQL Server occasionally, it can be somewhat tricky making the right decisions at each stage.
    I think the advice given by everyone has been informative and positive...with the exception of the unnecessary negative comments by pdanes as quoted by ajax
    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

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

Similar Threads

  1. Access back end to SQL Express. SQL ignorant
    By sparkyinak in forum Access
    Replies: 4
    Last Post: 05-28-2019, 04:52 AM
  2. Express to match two tables
    By SONCH in forum Queries
    Replies: 1
    Last Post: 05-15-2018, 01:12 AM
  3. How do I express this formula?
    By Essmoll in forum Queries
    Replies: 7
    Last Post: 11-02-2017, 11:51 AM
  4. Used to be friendly
    By arlanb in forum Access
    Replies: 1
    Last Post: 12-05-2014, 05:06 PM
  5. Replies: 7
    Last Post: 11-04-2010, 03:59 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