Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2014
    Posts
    14

    Exclamation Unanswered: Access front end with SQL Express

    I have an Access file that my user wants to dramatically expand. Can I push the back-end into SQL Express and retain the front end in MSAccess. If not, what would be the simplest. most logical approach to expanding this application.

    Thanks - David

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presuming you mean have a back end in SQL Server Express?

    yup its prefectly feasable

    however you'd need to check the limits applied to the version of SQL Server Express you are planning on using to make certain it doesn't cause problems

    Microsoft supply an upsizing wizard that shoudl handle the migration issues....

    However, reaping the benefits of using a server backend (irrespective of supplier) reequires a re design of the applciation. it means using unbound recordsets/controls/forms/reports. it means doing a lot of the donkey work that Access provides if you sue JET (its default storage mechanism).

    yes you get a more robust back end, easier to maintain and backup and so on. but unless you redesign your app to use a server backend then you are not going to reap significant regards in temrs of performance and reliability.

    the risk is that using the upsizing wizard and leaving it at that is that you end up using JET style queries, meaning the applciation sucks data/indexes accross the network to workout what data is needed by the application, as opposed to using a pass through query which just returns the data required, leaving the server to work out whatr rows are required.

    it also means re thinking you user interface... do you need to load all, god knows how many selections into a combo / list box, you may need to instead attempt to laod a combo box once the user has, say typed 3 or 4 characters...

    if your desing is clunky then putting it into a server backend wont nexccesarily make it muchfater or more reliable or more responsive.

    if you want to explore the options I'd strongly suggest you look at the Access Developer Handbook. (I have ADH for 2002 and book 2 covers these sort of issues in great detail)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Posts
    14
    No, all remains in Access at this point. I've not yet loaded ServerExpress. Would it be advisable to load my Access table data into Server Express, then attempt to use Access front end, or will front end have to be rebuilt. If not what is best software option to rebuild front end with?

  4. #4
    Join Date
    Apr 2014
    Posts
    14
    To clarify - SQL Express sounds like desired option due to expected size. What should be used for front end, user app - This will be shared among 4 users.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a first time, you can keep your Access application as it is. Simply use the SQL Server Migration Tools of Access to transfer the tables to the SQL Server and create linked tables corresponding to the migrated ones.

    When this is done, your application should keep on working as before and the SQL Server will just behave as an Access back-end (more or less).

    However, If you want to get all benefits from using a SQL Server (stored procedures, computed columns, triggers etc.), things become more complex and you'll need to deeply modify your project. That's what Healdem was talking about.
    Have a nice day!

  6. #6
    Join Date
    Apr 2014
    Posts
    14
    At a decision point between SQL Server Express and Access. I'd prefer linking this Access front end to multiple back-end table files (each less that 2GB). Is that not advisable? This app may reach 4GB. If that seems safe, I'll go that route. IF NOT, there are multiple SQL Server Express versions. Is one version advisable over another for this purpose?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If there is a risk that the database exceeds 2GB (or grows near that value), I'll go for SQL Server. Using multiple B-E Access databases is looking for troubles.

    I never tried using SQL Server 2014 yet, so I cannot recommend it (although it's probably all right). The 2008 or 2012 version will do the job without any problem and with 4 users, you'll be within the limitations imposed by MS when using an Express version.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    4GB on 4 users is a heck of a lot of data...
    Access will happily support multiple userrs, the precise cutoff point where plain vanilla Access (talking to an Access/JET datastore) starts to degrade is anywhere between 15..30 concurrent users.

    reasons for using a server back end are legion, not of of them sane/reasonable in my books
    ...fits corporate policy, data is backed up/controlled
    ...you try to use Access over a WAN/VPN (which is never a good idea). Access asd a frotn end tool talking to a remote db server no problem, but not to Access/JET data
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2014
    Posts
    14
    I have loaded SQL Express locally. It correctly recognizes my data but I'm getting the connection error - "A network related or instance specific error occurred.... (provider: Named Pipes Provider, Error 40)... I'm not over a network yet. Where do I find the "Instance" to verify that and correct if necessary?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the SQL Server Configuration Manager to correctly initialize the network configuration of your server. A typical configuration consists in:
    - Shared Memory (1) Enabled
    - TCP/IP (2) Enabled
    - Named Pipes (3) Enabled
    - VIA Disabled
    Have a nice day!

  11. #11
    Join Date
    Apr 2014
    Posts
    14
    VIA?? TCP/IP and Named Pipes are both Enabled but don't see "VIA" ??

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's possible that VIA is not included in the Express version. It should be disabled anyway, so it does not matter. What (i.e. which action) yields the connection error you reported?
    Have a nice day!

  13. #13
    Join Date
    Apr 2014
    Posts
    14
    It's working now. Using the Access Forms and it seems to be working -

Posting Permissions

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