Results 1 to 6 of 6
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Should I change DB engine?

    Hi All

    Some of you may "know me" and the DB project that I'm working on, but if you don't thats okay.

    I have written COBOL programs interfacing with RDMS 30 years ago, but Access is total new me and I keep jumping into the deep end. I choose Access because it's free, I know Excel well, Like VBA, Its part of Office, and thought it could handle it okay. (I did know about that recursion problem/fix)

    As I have worked though this project it appears to be bigger and deeper than I thought. (what else is new (Project Creep). Having made the decision that my Model is good enough, I have started to loading and writing some queries I'm finding that SQL requires a thought pattern that I have not got yet (I will soon or latter).



    At this time The model has 2 hierarchical temporal (Command and Heritage), 1 Bill of Material (TOE), and generational Parent-Child (Lineage) relationships. I know that Access (jet) does NOT do recursion, I also know that VBA in can work around this problem (VBA in itself doesn't scary me).

    My question to you guys is:
    A) Switch the DB engine at this time, and to which one?
    B) Work though this, everything else will be just as messy.


    Attachment: An old ERD diagram of the project
    Click image for larger version. 

Name:	Lineage 181031.jpg 
Views:	37 
Size:	135.6 KB 
ID:	37119
    Note: Table 29 is now 12 tables called the "E" tables (TOE)
    5 major parts, A) Unit, B) Sources, C) Location, D) Historical persons (nothing yet) E) TOE

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that really is your decision - you can use sql server express as a backend which is free - that does do recursion but the sql is quite complex, although there are plenty of examples about. Also be aware that although a number of responders here can use TSQL (the version of sql for sql server) the expertise you require will probably be better found on a forum that specialises in sql server. But also be aware the pool of responders on these forums is smaller and their language is generally more 'geeky' and you will need to be very clear about what you want (apologies to anyone from over there that are also over here who think that is an unfair comment).

    In my opinion, the scale of your project is quite small in terms of data, albeit complex. There is very little you can do in sql server that you can't do in an access database (or ACE to give it its proper name) for a project of your scale - particularly if you include VBA. Usual reasons for using sql server are around file size (ACE has 2Gb limit), security (ACE is not as secure as sql server) or accessibility from multiple locations, none of which apply in your case.

    And if you do upsize, be aware that SLQ server has a much higher maintenance requirement - compare standard petrol engine with formula 1 engine.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks Ajax

    Quote Originally Posted by Ajax View Post
    - that does do recursion but the sql is quite complex,
    and
    Quote Originally Posted by Ajax View Post
    be aware that SLQ server has a much higher maintenance requirement
    These helped me with my decision to stay with this engine.

    Another question, Since I'm doing much of this in VBA, should I try and use any Access queries or just stick with VBA queries ?

    Thanks for looking

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends what you mean by vba queries. If you mean run a query from vba whether it be one that already exists or one you build a sql string for, then yes, that is the normal way (e.g. using something like docmd.openquery or currentdb.openrecordset) - they will be faster - vba just controls the process. If you want to build a sql string, build the query first using the query grid, then swap to the sql view, copy the sql and then adapt it in vba as required.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks
    Just didn’t know

  6. #6
    bavya is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2021
    Posts
    3
    Thanks for sharing an useful post.

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

Similar Threads

  1. Chrome Engine for Webbrowsercontrol
    By GrasM4n in forum Forms
    Replies: 1
    Last Post: 01-05-2019, 02:11 PM
  2. Replies: 4
    Last Post: 05-22-2017, 03:39 PM
  3. Search Engine
    By kacocal in forum Access
    Replies: 4
    Last Post: 07-24-2015, 02:04 PM
  4. access database engine does not recognize
    By slimjen in forum Forms
    Replies: 8
    Last Post: 05-30-2014, 11:53 AM
  5. Jet Engine
    By wharting in forum Access
    Replies: 10
    Last Post: 08-27-2011, 04:50 AM

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