Results 1 to 14 of 14

Just starting out - need advice

  1. #1
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13

    Just starting out - need advice

    Good afternoon Access Forums,

    My current configuration is Win7/Access 2010 with SharePoint 2007 lists as a back-end. We recently "upgraded" to Win10/Access 2013 and now Access will not hold the connection to SP. We have had a "premier ticket" with Micro$oft for about a month and all they have been able to tell me is that they "agree that there is a problem"

    The silver lining to this story is that our IT department has agreed to host the tables on SQL - but no one on the staff has ever done anything with Access. To prepare myself, I have watched a series of videos on SQL Server setup and configuration (Steve Bishop - Advanced Programming in Access 2013) and ordered a book (SQL Queries for Mere Mortals).

    My question to the forum is what pitfalls and/or guidance can I give to my IT when they are setting up my services? I realize this is a VERY broad subject, what I am interested in for now is to make sure they are set up properly - the other factors (stored procedures, pass thru queries, etc) can be worked out (I think) in time as long as the server set up is configured properly.



    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,446
    not much to do....just connect all the sql tables (with the passwords).
    as long as all the tables names are the same as they were before (you can rename them on your side of the link) then the app should run as normal.

  3. #3
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    Thanks ranman, it’s the initial set up I am primarily concerned about. Watching Steve’s videos, it seems that you can configure SQL Server in others ways other than for Access - in other words, there are ways to configure the set up ideally for Access. I could be wrong and I certainly don’t mind being wrong...

    Also, you bring up another concern I have. When I migrated to Sharepoint, I had to de-normalize some of my tables. Will I be able to restructure them on the client side as well, or do they have to be done prior to migration?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,446
    table design cannot be done in access. It must be done now in SQL side.

  5. #5
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    Got it, thanks!

  6. #6
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,901
    Hi NG. I see you're migrating in more than one sense...

    There is an import wizard in SQL Server to handle all upsizing issues
    As long as you haven't got any fields using attachment or multivalued datatypes, it should be fairly straightforward
    If you do need to make changes, it will guide you through what is needed.
    Make sure any Boolean fields have a default value in SQL Server as these can be true, false or null in sql server.
    Any null value will make access have a massive strop and you will get write conflict errors.

    Suggest you set this up with mixed mode authentication so that users do not need to individually login to sql Server

    It has many advantages and few disadvantages.
    If in the future you need to alter table design in SQL Server, you can write a script to do this and distribute the script via email for others to run

    Good luck
    Colin (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

  7. #7
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    Thanks Colin,

    Thnaks for the info, I will add it to my ever growing list. Fortunately, I NEVER use multi-value fields and rarely use attachments. As far as Boolean fields go, I have a few so I will have to check those for default values, thanks for pointing that out.

    Not a total migration, mind you...more of a diversification!

  8. #8
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,901
    Same for me. I flit from one place to the other....

    I mentioned Boolean fields because that issue caused me months of grief as it was so difficult to pin down.
    Colin (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

  9. #9
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    ...dbSeeChanges...who knew?!??

  10. #10
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,901
    Correct - you need to use that with SQL Server recordsets
    Colin (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

  11. #11
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    As I have learned. The migration went smooth enough, some tweaks here and there but over all pretty straight forward. Substantially faster performance than what we had with SharePoint.

    Fortunately, AC2010 still has the Upsize wizard so a lot of the heavy lifting was done for me. One strange thing I noticed is that my Boolean (bit) fields still shoe -1 for yes vice 1. I thought SQL stored 1 for true...?

  12. #12
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    589
    Hi,

    as a SQL DBA I'm not very happy with the idea of creating a database using the upgrade wizard. If you forget to set up a good maintenance plan you can quickly get into trouble. For instance if you have chosen a full recovery model and don't make regular log backups you can run out of disk space.
    Using the upgrade wizard also leaves you with a database that isn't optimized for SQL server: think indexes, constraints, data types that aren't the same as Access data types, security settings and schema's.
    When upsizing to SQL server it's a good time to optimize your database structure.

    Kind regards
    NoŽlla

  13. #13
    Gicu is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    414
    Hi,

    Hopefully you checked the box in the upsizing wizard to create timestamp fields in all your new SQL tables - https://social.msdn.microsoft.com/Fo...databasedesign

    If not you can add them now, I found that they are a must with Access front-ends and SQL back-ends.

    Cheers,
    Vlad

  14. #14
    NauticalGent65 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    13
    @NoellaG,

    Thanks for the input. One of my first issues was with Constraints. Had no idea how they got uploaded with the wizard. I can only assume they were from some validation rules I must have had with the Access tables - they sure made life rough at first. Took me a minute to figure out what the issue was and how to fix it. Also, I noticed that SQL automatically indexed my PK fields but not my FK fields. I have read on this and other forums of the performance hit you can experience with over-indexing so I will tread lightly...

    Also, regarding a Maintenance plan. I had no idea what you were talking about but a book in my personal library ("Access VBA, Inside and Out" by Andrew Couch) mentioned it and a google search filled in the blanks so I will be going down that rabbit hole soon enough.

    Edit: Found this site: http://justgeeks.blogspot.it/2012/07...plan-best.html
    Very cool.

    @Gicu,
    As fate would have it, I DID choose the Timestamp option. I was not exactly sure what it did but I assumed it had to deal with tracking updates in a multi-user environment, therefore it HAD to be a good thing. It is reassuring to hear I chose wisely.

    As RanMan256 said, it has been pretty straight forward and at first the performance was outstanding and I thought I had gotten away with not having to use Views, pass-through queries, and Stored Procedures. However, one of my remote sites in the Middle East is reporting a lag in performance - about a 10-20 second delay when moving from one record to another in Form view.

    I will start with using Views to limit the number of records in the record set and go from there. I have been trying to find a "best practice" post in regards to a form's record source but have not found anything definitive yet. I have read in this and other forums that using a pass-through query renders the RS as read-only so I am not optimistic about finding an hard, fast rule.

    At any rate, thanks again to all who have posted and thanks in advance to any further advice to this latest challenge.
    Last edited by NauticalGent65; 03-12-2018 at 05:30 AM.

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

Similar Threads

  1. Starting Access using VBA
    By DHIGHTO in forum Modules
    Replies: 3
    Last Post: 02-19-2015, 08:48 AM
  2. just starting out - need help
    By abomb987 in forum Access
    Replies: 2
    Last Post: 05-16-2014, 05:54 AM
  3. trouble starting code
    By mejia.j88 in forum Programming
    Replies: 32
    Last Post: 12-15-2011, 10:57 AM
  4. Questions about starting first database
    By JM9x in forum Access
    Replies: 15
    Last Post: 09-10-2011, 03:26 AM
  5. Starting a new database
    By JFo in forum Access
    Replies: 9
    Last Post: 08-25-2011, 10:00 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
  •  
Tech Forums: Microsoft Office Forums