Results 1 to 12 of 12
  1. #1
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163

    What is the difference of Access vs. Visual Studio(Enterprise) vs. SAS

    I am not an IT professional, so my question may be dumb. But I consider myself good Excel VBA developer. I am trying to ask the questions in plain language. Correct me if I am wrong, and provide any more comments I do not cover.



    I have all those three software installed in my work computer.

    Primary purpose: Developing some applications which can manage ODBC database and do some data analysis of ODBC dataset. It is not about learning some basic for self-use, it is deep learning, eventually developing application for organization.

    Background: Very familiar with Excel VBA, so I guess it may be easier for me to learn Access/Visual Studio? They are all MS products, VB is not very different from VBA.

    Access: I think I can be very familiar with Access application development fair quickly, since I can feel it is relatively easier than the other two (Visual Studio & SAS). But the big disadvantage is: Access is not good choice to handle large ODBC database. So I think I need to learn something else to handle large ODBC database.

    Visual Studio(Enterprise): I do feel it is very powerful and a lot of features. Let me ask the core question first: should Visual Studio have no problem to handle large ODBC database, correct? At least comparing to Access. I am not sure if it is my computer hard drive issue, it is so slow for me to open Visual Studio. It is really killing my enthusiasm to learn Visual Studio. I have poked around Visual Studio a few months ago, maybe it has a lot of features and I am new to Visual Studio, my first impression is: I feel lost.

    SAS: I think it is also good to handle large database, correct? A lot of existing applications in my organization were developed by using SAS. I guess the developer retired long before I joined the organization. We don't really have skilled people to maintenance those applications. My SAS knowledge is very limited, and bigger question is I am not a fan of SAS. I could not find my enthusiasm to learn SAS. I like the easy way of developing application by using Form feature (Excel Form, Access Form, Visual Studio also have Form). However, I am not rejecting to learn SAS, if it is best choice for what I need.

    After comparison, personally I favor to focus on learning Visual Studio. Please share some thoughts, especially SAS. I have no ideas how to build application when using SAS. Is there any special SAS version for software development? I only have regular version of SAS.

    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access is an easy,self contained database foe quick development of apps.
    beginners can develop using macros.

    VB studio is a more robust app development BUT takes an experienced programmer that knows OOP and has knowledge of the controls.
    NOT the most user friendly for beginners, like Access.
    The VB app can design a Frontend for any backend ,like SAS, or Access , but it must be compiled, and distributed via installation packages.

    Access can connect to a backend too, like SAS, but the user must have either the full Access app, or the free Runtime.
    delivery is just copy/paste the database.

    Access has an internal limit of 2 Gig of data, (which I stored 2 million records). Where SAS or other high end databases do not have a limit.
    but Access can connect to these Backends if large data sets are needed.

  3. #3
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163
    Quote Originally Posted by ranman256 View Post
    Access is an easy,self contained database foe quick development of apps.
    beginners can develop using macros.

    VB studio is a more robust app development BUT takes an experienced programmer that knows OOP and has knowledge of the controls.
    NOT the most user friendly for beginners, like Access.
    The VB app can design a Frontend for any backend ,like SAS, or Access , but it must be compiled, and distributed via installation packages.

    Access can connect to a backend too, like SAS, but the user must have either the full Access app, or the free Runtime.
    delivery is just copy/paste the database.

    Access has an internal limit of 2 Gig of data, (which I stored 2 million records). Where SAS or other high end databases do not have a limit.
    but Access can connect to these Backends if large data sets are needed.

    So my core question is to confirm: Visual Studio can handle very large ODBC database, correct?

    Is it possible for Visual Studio to hold some data (just like Access Table)? At least temporarily holding the data, in order to perform some data analysis.

    Thanks.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    So my core question is to confirm: Visual Studio can handle very large ODBC database, correct?
    yes - not sure why you think Access is for small databases only, it can handle pretty much anything - you don't have to use the ACE database that comes free with Access.

    Is it possible for Visual Studio to hold some data (just like Access Table)? At least temporarily holding the data, in order to perform some data analysis.
    not as such, it is a front end development environment only, but I guess you could load data to memory as a disconnected recordset or array, dictionary or collection or perhaps save to a temporary text or excel file - or even back in the source database, same as Access.

    your consideration should be development time and managing ongoing maintenance. depending on skill level, allow something like 10 times longer to develop in visual studio. If you have visual studio experts to hand, you might be able to reduce this a bit.

    and although there are plenty of similarities between vb and vba, there is plenty that is not - particularly around forms and controls

    Visual Studio on my machine only takes a second or so to load. Running a VS app connected to a backend can take a bit longer - depends on the startup procedures and quality of the network connection.

  5. #5
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163
    Quote Originally Posted by Ajax View Post
    yes - not sure why you think Access is for small databases only, it can handle pretty much anything - you don't have to use the ACE database that comes free with Access.
    .
    Maybe my work computer is old, when Access file size is above 30M, it already runs very slowly. So I would forget about 2GB (per table) limitation. But I have never dealt Access with ODBC database before, now my concern is importing tens of millions of records into Access, and importing multiple tables, then perform some data analysis based on imported tables, I am worried that Access will run very slowly or even freeze.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    now my concern is importing tens of millions of records into Access, and importing multiple tables
    Don't think you understand what Access is. It provides a front end development environment (for forms, reports, code) which links to one or more backends - often ACE with its 2Gb limit, but could be sql server, oracle or any other odbc database. Depends how big a record is but I suspect ACE would not have the capacity for 10's of millions of records. Largest db I had using ACE was around 8 or 9 million records. The point is, you don't need to import the data to access, just reference the source directly. If this is not possible for some reason then OK, you need to import to somewhere, but probably not ACE.


    when Access file size is above 30M, it already runs very slowly
    that is down to one or more of poorly designed tables/indexing/queries, poorly written code or slow network if backend is on a server. Moving to Visual Studio will not address these issues although it may force you to rethink how you want to achieve something. Moving the backend to SQL Server et al and making use of the enhanced capabilities of these databases may go some way to address these issues. The db I referred to above took around 2 minutes to analyse a months data with comparisons to previous month/year etc. and generate numerous reports. It took longer to load the data in the first place.

    You have an Excel background. Databases (any database) requires a completely different way of thinking. Simplistically, Excel combines presentation and data storage in one view. databases just store data, you use Access/Visual studio to present that data. On data storage, Excel 'tables' tend to be 'short and wide', databases tables are 'tall and thin'. Excel loads everything to memory, databases pass out data as requested through queries. Databases adhere to normalisation rules whilst Excel is about as far away from normalisation as you can get.

  7. #7
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163
    Quote Originally Posted by Ajax View Post
    Don't think you understand what Access is. It provides a front end development environment (for forms, reports, code) which links to one or more backends - often ACE with its 2Gb limit, but could be sql server, oracle or any other odbc database. Depends how big a record is but I suspect ACE would not have the capacity for 10's of millions of records. Largest db I had using ACE was around 8 or 9 million records. The point is, you don't need to import the data to access, just reference the source directly. If this is not possible for some reason then OK, you need to import to somewhere, but probably not ACE.


    that is down to one or more of poorly designed tables/indexing/queries, poorly written code or slow network if backend is on a server. Moving to Visual Studio will not address these issues although it may force you to rethink how you want to achieve something. Moving the backend to SQL Server et al and making use of the enhanced capabilities of these databases may go some way to address these issues. The db I referred to above took around 2 minutes to analyse a months data with comparisons to previous month/year etc. and generate numerous reports. It took longer to load the data in the first place.

    You have an Excel background. Databases (any database) requires a completely different way of thinking. Simplistically, Excel combines presentation and data storage in one view. databases just store data, you use Access/Visual studio to present that data. On data storage, Excel 'tables' tend to be 'short and wide', databases tables are 'tall and thin'. Excel loads everything to memory, databases pass out data as requested through queries. Databases adhere to normalisation rules whilst Excel is about as far away from normalisation as you can get.
    I was not talking about front end, I was talking about importing (acImport, storing data inside Access file), not acLink. Maybe I should consider acLink.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Maybe I should consider acLink.
    it will solve your volume issue but may not solve the performance issue. Don't know anything about your setup to advise whether using passthrough queries would be a better solution - that depends on what you are actually doing and is sometimes a matter of trial and error.

  9. #9
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163
    Quote Originally Posted by Ajax View Post
    it will solve your volume issue but may not solve the performance issue. Don't know anything about your setup to advise whether using passthrough queries would be a better solution - that depends on what you are actually doing and is sometimes a matter of trial and error.

    Are you saying that: with acLink, there is no 2GB limit? Theoretically, if linked table is over 2GB, can it still be opened via Access table?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it depends on what you are linking to - if an ACE backend, the limit is 2GB (although you can link to multiple backends which may or may not be sensible). Otherwise it depends on the RDBMS you are linking to. The point is the 2GB limit is not a limitation of Access, it is a limitation of ACE.

  11. #11
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163
    Quote Originally Posted by Ajax View Post
    it depends on what you are linking to - if an ACE backend, the limit is 2GB (although you can link to multiple backends which may or may not be sensible). Otherwise it depends on the RDBMS you are linking to. The point is the 2GB limit is not a limitation of Access, it is a limitation of ACE.
    Not sure about the term ACE. I am linking to ODBC database tables(Sybase tables), it is on Server, not in PC hard drive.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    as explained several times in this thread, ACE is the database that ships with Access - you don't have to use it

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

Similar Threads

  1. Using Access Instead of Visual Basic
    By dinz in forum Programming
    Replies: 2
    Last Post: 12-19-2019, 10:33 AM
  2. Replies: 4
    Last Post: 11-29-2017, 04:24 PM
  3. Enterprise Database Solution
    By sharkey_lsu in forum Misc
    Replies: 12
    Last Post: 11-11-2015, 03:11 PM
  4. Converting Access to Visual Basic
    By Juicejam in forum Access
    Replies: 3
    Last Post: 04-12-2012, 02:39 AM
  5. how to use visual basic in access
    By learning_graccess in forum Access
    Replies: 3
    Last Post: 12-05-2011, 03:23 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