What database to use ?-VBForums
Results 1 to 20 of 20

Thread: What database to use ?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2017
    Posts
    25

    What database to use ?

    right im looking to design a orginzer like this one http://pigeonplanner.com/ i know it will not be a easy task and i know im find it super hard but somthing i would like todo, what type of database should i use in vb? as i will probly need about 4-5 differnt databases and then from forms to link to 2-3 of them some times to get the info from them ?

  2. #2
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: What database to use ?

    Any of the smaller ones (e.g., Access, SQLite, SQL Server Express) should work for you though the Microsoft ones may have better wizard integration. The ADO.Net commands for each of them should be the same (ignoring the different object names).

    You will need 4-5 TABLES perhaps, not databases.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,810

    Re: What database to use ?

    Right. A database can have almost any number of tables. There is rarely a need for multiple databases. Database design is an entire subject of its own. In fact, there are books and courses on it. In general, though, it's just records consisting of a bunch of fields, so it's rows and columns.

    Access probably has the easiest tools to work with. People often think that Access IS the database, but Access is really just a program used to view, create, edit, and otherwise manipulate data in one of a few different kinds of databases that are generally called Access databases. SQL Server Express is a bit more robust, but the program itself is just the database management tools. You need to include SQL Server Management Studio (both are free, and they can be downloaded together, or separately, but getting them together is almost always easier) to view/edit/manipulate the database. Management Studio is not nearly as user friendly as Access. Once you understand it, it isn't bad, it's just that there are some really goofy conventions they use that make it less intuitive than it could be. It is also more powerful, though you have to get into the weeds to really see that, which you often don't need to do at all.

    SQLite is one that I haven't worked with, so I can't say much about it, but my feeling is that it is a pretty powerful database program, more at the level of SQL Server Express than Access, but it also has some kind of management studio that you would want, and which I have no knowledge of or opinion about.

    The SQL used for all three of those is very similar....and just different enough that you have to learn a few quirks for each one. For example, Access wraps dates in # symbols, whereas SQL Server wraps dates in single quotes. Wrap a date in single quotes and give it to Access, and Access won't like it. Wrap a date in # symbols and give it to SQL Server and you'll have a nice, cryptic, error message. However, the languages are sufficiently similar that you can start out with Access for the ease of use, then switch to SQL Server at a later date without too many changes.

    So, I would say that you might consider Access to start with for ease, but ONLY if you have it already. I wouldn't pay a dime for it, because SQL Server Express is free and pretty good (as is SQLite, though I don't know much about it). Just be sure to get SQL Server Management Studio along with SQL Server Express if you go that route. It might be packed as SQL Server Express with Advanced Tools, or something like that. I've looked many times, and it's never as clear as it could be.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2017
    Posts
    25

    Re: What database to use ?

    thanks ill look into i just thought is i had a couple differnt data databases to hold differnt things just thought it make it easyer for me to understand like 1DB Pigeons 2DB Contact Details somthing along these lines

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,810

    Re: What database to use ?

    It'll actually make it harder on you to use different databases. A database isn't like a file or document (actually, it CAN be, but don't think about it that way). A database is more like a folder, but it is really it's own thing. A database consists of one or more tables, so you have the organization that you are talking about. Whether you use Access or SQL Server Express, you'd be looking at all the tables when you opened the database. There are times when multiple databases does make sense from an organizational perspective, but then you often end up creating different connections to each database with different connection strings, and that's more of a hassle, so you only do it if it makes sense. The typical place for multiple databases is if you have two databases covering different things, but they both have some tables that make sense for both of them, in which case it may make sense to have a third database that holds the common stuff, or something like that.

    Start with one database having multiple tables. Go to multiple databases only if it makes sense down the road. That would be the easier way to start out, from an organizational perspective.
    My usual boring signature: Nothing

  6. #6
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: What database to use ?

    Sorry to interupt, but Chris I would suggest you to try with SQL. I'm not trying to be a wise guy, but based on your VB experience I would say that Access will be a hard nut for you (as for many others is). You can also find much more examples (and videos) for SQL and how to do that in VB.NET. By doing this you won't have so much problems (my opinion) as in Access, I've been there and done that.

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

    Re: What database to use ?

    What do you mean when you say SQL?

    SQL stands for Structured Query Language, and is used by virtually all databases as a query language. Access uses one version, SQL Server Express uses a slightly different version, and SQLite uses yet another slightly different version. It's all SQL, though.
    My usual boring signature: Nothing

  8. #8
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: What database to use ?

    I think he means SQL Server maybe the Express version
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: What database to use ?

    I think he means SQL Server maybe the Express version
    My bad. Yes, I meant that, I only responded because I know that chris is a beginner. And probably Expres version for him, I think he is building more like a small home project.

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,810

    Re: What database to use ?

    I like SQL Server Express, but I'd say that Access is easier to use for somebody starting out...as long as you already have it. I feel that the interface is much easier to use and more intuitive than SQL Server Management Studio.
    My usual boring signature: Nothing

  11. #11
    Addicted Member
    Join Date
    Apr 2010
    Posts
    131

    Re: What database to use ?

    Quote Originally Posted by Chrisroscoe View Post
    thanks ill look into i just thought is i had a couple differnt data databases to hold differnt things just thought it make it easyer for me to understand like 1DB Pigeons 2DB Contact Details somthing along these lines
    They are called Tables, not databases. A database can have many tables, so 1 table called Pigeons, another table called Contact Details and so on. Given the fact that you seem to be a complete beginner about this, I'd also recommend you to use Access for your database.

  12. #12
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: What database to use ?

    Quote Originally Posted by Shaggy Hiker View Post
    I like SQL Server Express, but I'd say that Access is easier to use for somebody starting out...as long as you already have it. I feel that the interface is much easier to use and more intuitive than SQL Server Management Studio.
    I could talk about that a lot, but I reckon that you have a lot more experience than me, even in Access. But from my point of view - learning something new is allways hard and Access is no exception. My experiences with It (Imports, multi-user environment, manipulate data from .net etc) was not something I would say easy, rather frustrating.

  13. #13
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    3,794

    Re: What database to use ?

    I feel like Access has a bunch of deployment pitfalls. Seems like we have an awful lot of threads about figuring out exactly which way to connect to it? If there's a handy guide someone should link it.

    I use SQLite for all of my projects. It's not as full-featured as any flavor of MSSQL, but it gets the job done. It's relatively easy to deploy (just make sure a DLL travels with your program). A lot of people get cross with it because the column type is more a suggestion than enforced, doesn't have stored procedures, and all in all isn't as full-featured as MSSQL. These are things you learn to get over when your application doesn't need those features.

    I've never used MSSQL Express, but I hear it's sort of between SQLite and MSSQL in terms of features. I imagine it's easier to deploy than MSSQL. There's a lot of people here who use it a lot, and they can certainly help you.

    If I had to make a recommendation, I'd say it doesn't matter if you pick MSSQL Express or SQLite. I think Access is a generally poor choice and should only be selected by people who already know how to use it well. And if you've never used a relational database before, you'd do well to pick up a book on SQL and at least give it a skim. You won't get very far without a working knowledge.

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

    Re: What database to use ?

    I'm really surprised that Lucky and Sitten think that creating a database with Access if more difficult to use than SQL Express or SQLite. I agree with Shaggy, I think Access has a far superior development environment. I do agree with Sitten that for shear ease of application deployment, SQLite is the easiest.

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,546

    Re: What database to use ?

    I think that the three main contenders are SQL Server Express, Access and SQLite. The first is server-based, i.e. you must install a server in order to use it, while the last two are file-based.

    In the case of Access, if you use the old MDB format then the database engine is built into Windows while the newer ACCDB format will require that either a recent version of Microsoft Office is installed or you install the standalone ACE database engine. You can create an Access database without the Access application but the other options are a bit of a pain.

    SQLite is a third-party product but is recommended by Microsoft. Microsoft used to provide SQL Server CE as a file-based option but they stopped developing that a few years ago and recommend SQLite in its stead. The SQLite ADO.NET provider is all you need and it can be deployed with your app. It will even create a database on the fly for you.

    It's also worth noting that you don't need SQL Server Management Studio to work with SQL Server Express. Microsoft have recently provided the LocalDB database engine, which is a severely stripped-down edition of the SQL Server database engine and you can work against that in Visual Studio, creating tables, relations and data in the familiar VS interface.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  16. #16
    Hyperactive Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    489

    Re: What database to use ?

    I intentionally didn't want to start discussion because It's not the place we should, but I think we are all way pass that. So let's turn ourselves into beginner skin for a second (which is trying to bulid DB for his VB.NET app to manipulate whatever data he needs, in terms of "+" and "-"):

    Access:
    "+"
    1. Building tables - definitely a nice interface, probably the best. Even an autonumber field feature, which I miss a lot when working with Oracle.
    2. Building relationships - nice built-in GUI. You can have those in DBMS too, but they are mostly not included.
    3. Almost anything can be done by a click - even building a query is easier. Although building a complexed query for m-m relationships is different story.
    4. Good for learning about DB - but only in Access with working for Access - Access for .net is not my favourite choice.

    "-"
    1. As sitten mentioned - connecting, from .net. A pain. You need ace oledb library installed on your pc in order to get It even working, and even that doesn't allways solve the problem. I would really like to show you how that problem can lead you into struggling with Win registry, I think nobody would he happy about It. Not to mention mdb format - you will probably try 100 examples you find online before you will get It working.
    2. Querying - ever done multiple Joins, like for m-m tables, or trying to Upsert, Merge ?...Not a lot to say, DBMS has no problems with this. You could also try to import into Access, with e.g. no duplicates - I would like to see how that import of your works - from my experience, you can't relly on It at all - a completely valid SQL for Access just doesn't perform allways as It should. My suggestion for Access is actually - whenever you can, allways use only Text fields.
    3. Autosave feature - a lot of developers spit on that. Try developing a simple app with "Edit", "Save", "Cancel" buttons along with monitoring changes in your fields and disregarding those changes If you want to save only when you click on "Save". You'll quickly find yourself deep in VBA, with a combination of turning on/off Access features.
    4. Multi-user environment - what DBMS doesn't really consider a problem. Yes, you can enable It, and with some clicks actually, but in my opinion nobody does know how to do that straight out of head. What about something like "enterprise" installation ? Ever tried to link your Access tables to relative path instead of absolute path as It requires, e.g. to have It shared in some group ?

    DBMS:

    Only "-" as I see It is that you need to learn first, but that goes for Access too. Everything else is a "+", If comparing to Access (not talking about creating tables, which is simple in Access). My bottom line - Access is good for starting to know what DB is, but once you know something about this just drop It. Sooner you learn something in real DBMS, better for you. And Chris (one who posted thread), did a step over that when he reached for a vb.net solution, so I really don't believe that he will have an easier job with Access than any other MSSQL versions, where you can find thousands of examples online. Access examples - I'm afraid half of what you find doesn't work. VBA is not well updated and VB.NET doesn't get along with a lot too (like ADO or DAO problems) etc.
    Last edited by LuckyLuke82; Feb 17th, 2017 at 02:37 AM.

  17. #17
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,810

    Re: What database to use ?

    Quote Originally Posted by LuckyLuke82 View Post
    I could talk about that a lot, but I reckon that you have a lot more experience than me, even in Access. But from my point of view - learning something new is allways hard and Access is no exception. My experiences with It (Imports, multi-user environment, manipulate data from .net etc) was not something I would say easy, rather frustrating.
    I'd like to endorse this argument. If you don't know either one, then I'd go with SQL Server Express for this very reason. Ultimately, I think that's the most useful one to learn, so if you're going to learn one, then learn the most useful one. There probably is roughly equivalent amounts of learning to get up and running with Access or SQL Server using SQL Server Management Studio. Access may be slightly easier to learn, but learning SQL Server will probably be more advantageous in the long run, so you might as well start with that.
    My usual boring signature: Nothing

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Feb 2017
    Posts
    25

    Re: What database to use ?

    very intresting read tbh, so meny differnt views now i have used both when i use to make do a legends of mir private server one have sql and ones at access i found that access was a little easyer to edit things in tables ect but i guess ill have a think im in no rush to start it as no doublt ill start crying when i do lol

  19. #19
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    728

    Re: What database to use ?

    Quote Originally Posted by Chrisroscoe View Post
    i found that access was a little easyer to edit things in tables
    That may be true. I have rarely used SSMS but maybe it doesn't allow you to edit table data via a GUI (i.e., you must use a SQL statement directly). Surely there are GUI tools for SQL Server that would though. I know they exist for Oracle, MySQL and SQLite.

  20. #20
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    30,810

    Re: What database to use ?

    SSMS does allow it, it's just a bit less intuitive than one might expect. For example, clicking a table for Access opens it for editing. Clicking a table in SSMS (SQL Server Management Studio, for those who didn't pick up on that) expands it, because the table is actually a node in a treeview, or treeview like structure. To open the table the way Access does it, you have to right click on the table and choose Edit Top 200 records, and that opens those 200 records. You can change the 200 by altering the SQL statement, but that is hidden by default. If you choose Select Top 1000, you can see the top 1000, plus the SQL that got you that top 1000, but you can't edit any of the records.

    This is all fine, but it shows the issues with SSMS: It's all there, it just isn't all intuitive. Why do you see the SQL for the Select Top 1000 by default, but it is hidden by default for Edit Top 200? Why 1000 when selecting, but 200 when editing, especially since both are easily changed? There's just a lack of consistency as if SSMS is nothing more than a group of tools stuck together somewhat haphazardly. The power is there, the functionality is there, but the attention to detail is not.
    My usual boring signature: Nothing

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

Survey posted by VBForums.