Results 1 to 9 of 9

Transition to Oricle

  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    Transition to Oricle

    I'm looking for direction and wisdom from the individuals that have been where I am. I have two split DB's each used by individuals around the country, the front ends are on the users computers but the back ends are hosted on our shared drive. When there was minimal usage this worked, but as more users are using it things are getting slowed down and I think my next move probably needs to be to transition to a Oricle backend, but I need direction. I say Oricle because my company has a Oricle server but I will have go thru a long process to apply for its use. When that time comes I would like my databases and my knowledge to be ready, so here's my questions-

    1. Are there any online courses for working a access database with a Oricle?


    2. Is there such a thing as a "Test environment" that I could use to start checking my database for compatibility?
    3. Are there any rookie mistakes that you can pass on so I hopefully wont make them.

    I'm sure there are many other questions but mainly how do I get my databases, code and self ready to go to a server.

    Thanks

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,737
    I don't know about any online courses but try this website which shows different versions of sql http://www.w3schools.com/sql/default.asp. Also join an oracle forum, think about the questions you ask here - and ask them there.
    Test environment, you could try installing MySQL which is an Oracle product. You might be able to persuade your IT dept to provide you with a sandbox to play around in
    main mistake people make is they think it will be straightforward - you have the upsizing wizard to sql server for example and I believe oracle has one as well, but that in itself is generally insufficient. They also assume it will result in a performance improvement.

    Before considering a transition to Oracle or SQL server, you should review your current design to improve performance and ask yourself the following questions:

    Tables
    1. are the tables properly normalised
    2. have all lookups and multivalue fields been removed from tables - they are not compatible with anything else anyway so will need to be removed if you are upsizing. In the meantime they have a drag on performance.
    3. are the tables properly indexed (any field which is used in joins or regularly used for searches and/or sorting should be indexed - but not those which mainly contain nulls or a limited range of values such as boolean fields)

    Queries
    4. have all domain functions been replaced with subqueries/sub tables
    5. are udf functions designed efficiently and reviewed to see if they can be moved to SQL
    6. have the initial wild cards used in searches (i.e. like *something*) been removed and users trained to use them when required (they negate the use of indexes)

    Forms/Reports
    7. Have these been designed to return only the records and fields required before being populated- i.e. do not just have a table as a recordsource
    8. have these been designed to not populate the sub forms/reports until required - as a minimum after the main form current event
    9. have any controls with domain functions been reviewed to see if the data can be included in the recordsource

    Modules
    10. do they have Option Explicit just below Option Compare Database
    11. are they compiled
    12. have you done a decompile
    13. are user front ends .accde
    14. has currentdb been assigned to a database object which is referred to in code rather than currentdb
    15. does the front end maintain a persistant connection with the back end - if 14 is done right, it will

    Access
    16. Has name autocorrect been turned off
    17. has front end been compacted


    Network
    16. does it have sufficient bandwidth for the number of users and level of traffic
    17. can bandwidth be increased


    I'm sure I've missed a few, but all of the above should be done before deciding to move to another database engine. Any you have answered no to, need to be addressed.

    SQL Server, Oracle and the like are no faster than access in most situations, they can actually be slower in real life. From a performance perspective, where they win is they can utilise four cores of processing power, whilst access (plus Sql server express, and MySQL) only uses one. However Oracle is being used for many other things which will take up usage of those cores, so the benefit could well be reduced.

    They also do server side processing - the equivalent performance of a user having the backend on their machine - or (and this is an alternative strategy for you) access located on terminal server or citrix. So you can roughly even out the performance advantage on processing. Obviously, if you are moving for improved data security, volume of data to be stored or concurrent users in excess of 255 (well perhaps a bit less) then no contest, you need to move.

    Access should be considered to be more like a web application that an Excel - and should be designed with the same considerations in mind - primarily to minimise network traffic. Take a look at post 11 on this thread for one example where users should see an improvement http://www.accessforums.net/showthread.php?t=62860

    All of the above points will have an impact on performance whether your backend is Oracle or Access, so unless you address them, you may well be disappointed when you transition. And if you address them now, you may be pleasantly surprised at the improvement.

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thank you Ajax, you've given me a lot to work with and think about.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,737
    on domain functions you might be interested in this article which compares performance of different methods

    https://www.experts-exchange.com/art...Functions.html

  5. #5
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Interesting article, sounds like there is more to this then i originally thought. I think my next move will be looking into some oracle forums like you suggested and then to find some sort of sand box to play in, something that i could put on my computer that would simulate working with Oracle, personally i learn more if i can apply the method. If anyone has any good suggesting's on either please let me know. Thanks

  6. #6
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    So far i haven't found a Oracle forum that I've liked, most are geared more towards the expert rather than a noobie like myself. Does anyone have any suggestions?

  7. #7
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,737
    they don't bite

    which ones have you tried?

    Also if you have oracle onsite already, suggest talking to your IT dept, see what they use or have used in the past

  8. #8
    InsuranceGuy is offline Query and VBA Junkie
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    122
    Quote Originally Posted by Ajax View Post
    *snip*
    SQL Server, Oracle and the like are no faster than access in most situations, they can actually be slower in real life. From a performance perspective, where they win is they can utilise four cores of processing power, whilst access (plus Sql server express, and MySQL) only uses one. However Oracle is being used for many other things which will take up usage of those cores, so the benefit could well be reduced.

    They also do server side processing - the equivalent performance of a user having the backend on their machine - or (and this is an alternative strategy for you) access located on terminal server or citrix. So you can roughly even out the performance advantage on processing. Obviously, if you are moving for improved data security, volume of data to be stored or concurrent users in excess of 255 (well perhaps a bit less) then no contest, you need to move.

    *snip*
    I really appreciate that Ajax provided some solid guidance on improving in-place databases here. It's thorough and robust in the context of opportunities to mitigate the concerns the OP provided.


    My concern, and the reason for my comments, are that the comments about MSSQL and Oracle backends are questionable.


    JET (which is the database portion within a MS Access file) is designed to support rapid database development, not enterprise grade multi-location managed data needs. It can be used that way, and the suggetions Ajax raised will help people be more successful at it. That said, a properly designed Oracle, MSSQL or other similar database instance will almost always be more effective when the audience is a large scale, multi-location team when compared to a JET-only based design, measured in terms of scalability, reliability, and data security.




    Jeff
    When you post SQL, make it readable: http://poorsql.com
    Please include a sample of source data, an example of desired outputs, and any code you already have written.

  9. #9
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    3,737
    Jeff,

    I don't disagree with you, my comments were in the context of the OP comment

    but as more users are using it things are getting slowed down and I think my next move probably needs to be to transition to a Oricle backend
    and I did say

    Obviously, if you are moving for improved data security, volume of data to be stored or concurrent users in excess of 255 (well perhaps a bit less) then no contest, you need to move.
    And many of my suggestions would be enforced if you upsized anyway, potentially requiring a complete rework of the front end.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-28-2011, 06:06 PM
  2. Color Transition example
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-23-2010, 11:09 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