dcsimg
Results 1 to 13 of 13

Thread: Database - Which Format ?

  1. #1

    Thread Starter
    Member Logit's Avatar
    Join Date
    Jan 2018
    Posts
    45

    Database - Which Format ?

    .
    I have a current completed project in VBA/EXCEL.The records are stored in the spreadsheet.

    A quick review of .NET documentation seems to indicate I can transition to .NET with an ACCESS style database format or a TEXT file. Trying to decide which one to go with.


    Present project (VBA) has the largest static file (which never changes, only perform a search of) at approx. 1,361,043 records. (Envision each row in the sheet is a single record of 8 items/columns).

    The other portion of the project has the ability to enter/edit/delete/search new/existing records. Again, envision each row in the sheet is a single record of 20 items/columns.
    This portion would probably never exceed 50,000 entries/records.

    I am not seeking millisecond return precision on a query. In other words (for an example), if ACCESS style would give me a .75 second query return vs TEXT file of 1 -3 seconds, I'm ok with going the TEXT file route. This project is a "casual / hobby type" database- not work related.

    Another aspect ... presently within EXCEL the user has the ability to use the built-in feature of FILTERING the data. A quick means of 'drilling down' through the data to display only those records
    that adhere to the chosen criteria. Would the ACCESS style have that built-in ... or does it require programming such a feature as it would for the TEXT style ?

    Thank you for your answers.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    21,897

    Re: Database - Which Format ?

    Access is the preferable solution. Much easier to manage with easy filtering and editing through SQL

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,827

    Re: Database - Which Format ?

    I've seen from your other post that you've used VBA, since this is not work related I was wondering why you want to convert this program to visual Basic. If all you want is better Filtering functions or better Data Entry screens then you could import the Excel spreadsheet to Access and then continue to program using VBA.

    I'm not trying to talk you out of learning VB, if you'd find that interesting then I'm all for it. I was just wondering if there was something you wanted to achieve that requires Visual Basic.

  4. #4

    Thread Starter
    Member Logit's Avatar
    Join Date
    Jan 2018
    Posts
    45

    Re: Database - Which Format ?

    .
    I am seeking better security for the coding. Excel provides no security at all. VB5 was given serious consideration but I have to wonder how much longer a 30 year old 32 bit product compiled product will function in the future ?
    And, since I have nothing but time on my hands at this point in life ... learning something new is a challenge.

    But I agree, sticking with VB5 might be the easiest thing to do.

    I also gave ACCESS a serious look but found out it isn't much more secure (the code from prying eyes) than EXCEL. (Unless I was researching the wrong resources who said it wasn't.)

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,827

    Re: Database - Which Format ?

    But I agree, sticking with VB5 might be the easiest thing to do.
    I never said that, in fact in another thread I said not to use VB5. Anyway good luck.

  6. #6

    Thread Starter
    Member Logit's Avatar
    Join Date
    Jan 2018
    Posts
    45

    Re: Database - Which Format ?

    I understand ... and thank you.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    32,650

    Re: Database - Which Format ?

    I'm not at all sure whether VB.NET is the right answer for you, but we are all just guessing, so your guess is as good as mine. What I'd say is that VB5/6 is likely to continue into the middle of the next decade, and is likely to continue beyond that, as well. MS seems likely to just keep it working. So, when it comes to longevity....how long do YOU expect to last, let alone your program. VB5 may be good enough, but you are right to wonder. VB.NET will certainly be around in some fashion as long as Windows is around.

    Text could be easier, as you could use XML and then reading/writing would be super easy. Of course, XML would be more insecure than anything else you did unless you encrypted it. However, with the number of records you are talking about, XML would be HUGE!!! After all, that's just turning everything into strings and wrapping each field in tags (also strings), so you are bloating up the size of any record by a fair amount. XML is fine for smaller projects, but what you are describing would be horrible in XML, and probably even more horrible (though in different ways) in any other text system.

    So, you are really needing a true database. Access is one option. SQL Server Express is another (free) option that is more robust. If you already are familiar with Access, then that would be a real advantage for Access. With SQL Server, you would need SQL Server Management Studio, which, though there is no reason for it, is not as user friendly as Access. All the features found in Access are found in SQL Server Management Studio, and quite a bit more, but Access was built to make it easier for the user. SSMS seems to have been built by geeks for geeks. There were some truly bizarre user interface decisions made in that. Once you learn the basics, it is just as easy, if not easier, but your intuition will lead you astray.

    For example, in Access, if you double click on a table....you see the table, opened and editable. In SSMS, if you double click on a table....you either do nothing, or you open a menu with no useful options on it. That's hardly what anybody would expect to have happen. Of course, it does that because the table is a node in a tree view, so clicking the node expands it, which is consistent, even if everybody knows that's not what a user is likely to want. Instead, to get the functionality you see in Access, you have to right click on the table and select the option to Edit top 200 rows. If you want more than 200 (which is an arbitrary number), then you'd have to find the button to see the SQL, and edit that to remove the Top(200), then run it again.

    So, they COULD have made it more user friendly, they just chose not to...repeatedly. Once learned, it's powerful and versatile, and you don't have to learn all that much to make use of it....but you DO have to learn those first moves.
    My usual boring signature: Nothing

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,425

    Re: Database - Which Format ?

    As suggested, a database is the way to go. If you have the Access application then that's a fair enough option. For file-based databases, Microsoft actually recommend SQLite, which was a competitor to the discontinued SQL Server CE. If you want something a bit more robust the SQL Server is an option. The Express edition is actually supported directly in Visual Studio. If you only plan to use local data files then you actually don't need SSMS, as you can create tables and work with data directly in VS. In that case, you create an MDF data file and add it to your project and it then gets attached to a LocalDB instance at run time. For a full SQL Server instance, you'd create and manage databases in SSMS and the data files would remain permanently attached. Each has their advantages but, for single-user systems, it certainly can be easier to treat the data file as part of the application than as a separate entity. Multi-user systems are generally a different proposition.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,425

    Re: Database - Which Format ?

    If you are going to work with local data files of any sort, I will point out something now that tends to trip up a lot of people. You will have multiple copies of your data file and the one that you look at via VS is not the one that your application will connect to at run time. This may sound odd but it actually makes perfect sense. Most importantly, it means that you will have a pristine data file to distribute with your final Release build regardless of how much messing about you do with the database in the debugger.

    On that subject, I suggest that you follow the first link in my signature to learn how local data files are managed. Despite any warnings to the contrary, you should set the Copy To Output Directory property of a local data file to Copy If Newer. That way, any changes you make at run time while debugging will be retained across sessions unless you make a change to the source data file itself.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,827

    Re: Database - Which Format ?

    I also gave ACCESS a serious look but found out it isn't much more secure (the code from prying eyes) than EXCEL. (Unless I was researching the wrong resources who said it wasn't.)
    Look at this http://www.dummies.com/software/micr...s-an-mde-file/

    Also
    https://www.techrepublic.com/blog/10...cess-database/

    I've never considered VB code itself to be secure but if your wanting to distribute an exe then it's the way to go.

  11. #11

    Thread Starter
    Member Logit's Avatar
    Join Date
    Jan 2018
    Posts
    45

    Re: Database - Which Format ?

    .
    I did review the second URL previously (https://www.techrepublic.com/blog/10...cess-database/). Other websites give the same recommendations for protecting the code. Most all of the instructions are identical to EXCEL VBA. There are alot of similarities between EXCEL VBA and ACCESS in this regard.

    In one of my EXCEL VBA projects I removed the menu strip (in effect if you were to open the workbook under normal circumstances you would not be able to see a menu bar at the top. Then I also disabled all of the keyboard commands that would normally allow someone to bypass the missing menu strip by pressing F11 or using a combination of keys to effect an action of accessing the VB Editor/macro code. After that I used a third party product to obfuscate the macro code and another to hide from view the macro code. Finally, placed a password on the VB Editor.

    And still there exist well published means to by pass all of that to access and view the original code without a de-compiler.

    The only way to truly secure the code is to create a project that must be compiled - machine code - which would require an interested party to utilize a HEX Editor or something similar to recreate the original code manually. It would require a "hacker" in essence to get to something that could be recreated.

    Ironically, if you do a search for DECOMPILER for VB .NET and VB there will be 367,000 returns in Google. * Sigh *

    When you compile VB .NET to an executable is it machine code or something different ?

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,425

    Re: Database - Which Format ?

    Quote Originally Posted by Logit View Post
    When you compile VB .NET to an executable is it machine code or something different ?
    Something different. It's much like Java was compiled to bytecode and then that was compiled to machine code when run. .NET worked/works much the same way, although there are ways to create .NET-based executables that cannot be decompiled so easily. The question is whether it is worth it. You can sign an executable that will prevent anyone modifying and running it and you con obfuscate source code to make it not readily decipherable. How hard are people likely to try to make your app work in a way that you didn't intend? It's still going to be harder with VB.NET than VBA.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  13. #13

    Thread Starter
    Member Logit's Avatar
    Join Date
    Jan 2018
    Posts
    45

    Re: Database - Which Format ?

    .
    I don't envision folks being really interested in getting to the source code. My "paranoia" lies in the fact of how weak and open Excel VBA is. I need to get over my fears and move forward.

    Thank you for the description of VB .NET compilation.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width