Results 1 to 30 of 30

Thread: SQLite, SQL and VB6 - Where to start?

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    SQLite, SQL and VB6 - Where to start?

    I apologize in advance if this is a dumb request. But honestly, I'm suffering from information overload due to fragmented content.

    If I'm to learn something fast (and I need to learn fast), I usually resort to buying a tutorial course, books, etc.

    But with SQLite, SQL, VB6 and vbRichclient 6, it is all over the place and trying to 'piece' together the whole thing is giving me a headache.


    SQLite is used by many programming languages. It is used alone. There is SQL for this DB, and for that DB, and they are not all the same.

    I'm interested in SQLite as it pertains to VB6.

    But then, there is this ADO thing, and this RC6 thing, and so there are methods solely for 'that' thing, but RC6 has its own methods for SQLite, and so doing 'searches' is rough.



    Where would someone, from scratch, go to learn SQLite as it pertains to VB6 (and not other languages), and if RC6 is the recommended method of using SQLite in VB6, where to learn all the functions, methods whatever that pertains to this?

    I'd like to be able to sit down and start from the beginning, making sure I get all the basics down, learn all the methods available to me and how they work, so I can start figuring out some of these tasks I need to do.

    Where do I start?

    Thank you.

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,857

    Re: SQLite, SQL and VB6 - Where to start?

    I would just go and learn SQL
    Then when things are not available or work different then check the exceptions/differences for SQLite

    https://www.w3schools.com/sql/

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Arnoutdv View Post
    I would just go and learn SQL
    Then when things are not available or work different then check the exceptions/differences for SQLite

    https://www.w3schools.com/sql/
    Thank you.

    I'm also currently at the https://www.sqlitetutorial.net/ site.

    I'll add your url to my list.

    :-)

  4. #4
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by webbiz View Post
    But with SQLite, SQL, VB6 and vbRichclient 6, it is all over the place and trying to 'piece' together the whole thing is giving me a headache.
    Quote Originally Posted by webbiz View Post
    SQLite is used by many programming languages. It is used alone. There is SQL for this DB, and for that DB, and they are not all the same.
    Let me help you with your approach to learning this stuff. For now, I'd advise you forget about RC6, SQLite etc. Narrow your focus to ADO and SQL Server from Microsoft since they are very well documented. Learn how to use these technologies. Learn how queries work, how connections work, how database instances work etc. Once you have a firm grasp on these things, 80% of what you learn can be re-used for other database technologies. For example, the SQL language itself is mostly identical across many different types of relational database engines. You will be able to recognize patterns and know what to look for.

    For example, last year a question came up here about SQLite. I had never in my life use SQLite before but I was able to get it up and running in a few minutes in VB.Net just because I was able to tap my many years of experience dealing with SQL Server. I knew I needed the back-end engine, a software interface like ADO to communicate with it and a connection string to make actual connections. I just had to look for that info. I could go directly to the information I needed because I knew what I was looking for.

    Your problem might be trying to learn too many things at once. You need to narrow your focus until you get comfortable with the subject matter.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,382

    Re: SQLite, SQL and VB6 - Where to start?

    Have to disagree with Niya.

    SQLite is very well suitable to learn the first steps with Databases, since you don't have to go through the Pitfalls of setting up a DB-Server
    (and it doesn't matter if it's MS SQL, MySQL, Postgres, or my sock drawer in my bedroom).

    What i would advise to the OP, is to first learn "general" Database-Stuff (Normalization, Primary Keys, Foreign Keys, Constraints etc.),
    and second to stay as close as possible to ANSI-SQL.

    It doesn't matter if you use RC6, ADO, DAO, CIA, NSA or whatever three-letter-abbreviated thing to access the Database.
    In the end it boils down to DDL, DML (and DCL)
    DDL=Data Definition Language ("CREATE TABLE", "ADD COLUMN" etc.)
    DML=Data Manipulation Language ("SELECT * FROM", "INSERT INTO...", "UPDATE Table...", "DELETE FROM Table")
    DCL=Data Control Language ("GRANT privileges" --> usually on server-based DBMS)

    In over 20 years i never used something like "MyRecordSet.Edit".
    I use SQL-Statements, since i have full control of what's happening
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    The reason I suggested SQL Server is because in my opinion it has the lowest barrier to entry. Microsoft provides everything you need pretty much right away to dive right into it. Other database engines need you to download, install and configure a lot of stuff. Not that you don't have to with SQL Server but I think MS makes the process a lot easier. I donno, that's just my opinion.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  7. #7
    Frenzied Member
    Join Date
    May 2014
    Location
    Kallithea Attikis, Greece
    Posts
    1,289

    Re: SQLite, SQL and VB6 - Where to start?

    All systems for databases have differences, and the most serious one is the scope of the database. A SQLite3 database is certain a good piece of software but you have to stay on a single user scenario. You have an application for one user only and the database to do anything. Also this database has to be in your pc, to run for a descent speed. I like the rollback feature of this database. I use the sqliteodbc.exe to install the odbc driver and I make connections through odbc. Windows 10 have own SQLITE but for my need I use the odbc driver (so my app not depends on shadow updates from Microsoft).
    GK

  8. #8
    Addicted Member
    Join Date
    Aug 2013
    Posts
    235

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Niya View Post
    The reason I suggested SQL Server is because in my opinion it has the lowest barrier to entry. Microsoft provides everything you need pretty much right away to dive right into it. Other database engines need you to download, install and configure a lot of stuff. Not that you don't have to with SQL Server but I think MS makes the process a lot easier. I donno, that's just my opinion.
    Postgres is very compliant with standards as far as I know
    You can use vb6 ADO, psqlodbc, and postgres together quite easily.
    I find it pgadmin a lot more straightforward than SSMS to administer databases.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,382

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Niya View Post
    The reason I suggested SQL Server is because in my opinion it has the lowest barrier to entry. Microsoft provides everything you need pretty much right away to dive right into it. Other database engines need you to download, install and configure a lot of stuff. Not that you don't have to with SQL Server but I think MS makes the process a lot easier. I donno, that's just my opinion.
    Everyone is entitled to their opinion, and i respect that.
    And you might even be right under some specific circumstances.


    e.g. i use DB Browser for Sqlite as a "Admin"-Tool


    Download, install, fire it up, nothing to configure

    Create your first Database, and then start learning the DDL and DML for it.
    And the SQL-Dialect for SQLite is pretty close to ANSI, anyway.

    Bottom Line:
    It doesn't matter if you use SQLite, MS SQL, MySQL or whatever:
    if you don't have a clue about Normalization, Primary/Foreign Keys, Constraints, referential integrity etc. --> You will fail!

    Everything after is just DDL and DML as mentioned above, incl. slight adjustments to the Dialect of the DBMS.
    If you have the SQL down pat, it doesn't matter if you use RC6, ADO, DAO, ODBC Yes/No or whatever (anyone remember RDO?): the SQL will not fail!
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Zvoni View Post
    Bottom Line:
    It doesn't matter if you use SQLite, MS SQL, MySQL or whatever:
    if you don't have a clue about Normalization, Primary/Foreign Keys, Constraints, referential integrity etc. --> You will fail!
    Yea this is my point. I really think he should choose something that allows him to focus on just these things without getting bogged down with too many confusing details while having a comprehensive set of documentation and samples. I think SQL Server is great for this but if you think SQLite can do it, that's fine too I guess.

    I just think MS did such a great job with the Sql Server Management Studio. It was one of the biggest helpers back in the day when I was new to it. I found it to be just so user friendly. The documentation is where MS really shined. Everything was just so well documented.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: SQLite, SQL and VB6 - Where to start?

    There is probably a lot less distraction for a beginner if you start by working with Jet 4.0, which not only has good support in VB6 but also ships as part of Windows. There is significant documentation provided with VB6, it can be fiddled with using MS Access if you have that, tons of sample code and tutorials around, etc. The SQL-92 syntax available via ADO and the Jet 4.0 OLEDB Provider is also closer to the syntax used by SQL Server, which makes porting to SQL Server easier than using some off the wall RDBMS.

  12. #12
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Oh yea, I completely forgot about Access. That is also a good entry into the world of relational databases if you're a Windows/VB user. Though I can't say for sure how the documentation side of things has held up over the years. I haven't touch anything related to Access in like 10 years.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Niya View Post
    Oh yea, I completely forgot about Access. That is also a good entry into the world of relational databases if you're a Windows/VB user. Though I can't say for sure how the documentation side of things has held up over the years. I haven't touch anything related to Access in like 10 years.
    I really appreciate all the comments/contributions on this thread. It helps to get an idea what experienced DB users think.

    A very long time ago, during the 90's, I recall having written Access code. It's been a very long time since I've heard that word. In fact, I went to MS Access training (certification), as well as getting my MS Application certification (you know, pay $100 and take test at some satellite location in Los Angeles). You wouldn't know it by my questions that I once was an MS Professional. Age and career change can do that.

    On a very basic level, I understand the DB Table, Recordset relationships, vaguely something about 1 to ? and ? to ? and so forth. And not long ago had this discussion with Niya on trying to figure out a chart tools DB schema (never reached a final design).

    You all make good points.

    Most of yesterday I read sites, experimented with MS SQL Server Mgmt Studio 19, pgAdmin 4, listen to two (Russian?) guys via Udemy (SQL & Databases Design A-Z: Learn MS SQL Server + PostgreSQL). It just happens I had purchased this awhile ago so thought I might as well watch it now.

    I spent hours a few months ago trying to learn SQL itself. If you walk away from it during training, you forget it really fast. That happened.

    =========================
    DATABASE GOALS
    =========================

    My 'goal' after all this is not to find a job. It is not to become an 'expert' or enter the BI world.

    I will likely never have a need for multiple users accessing a single DB.

    What I will likely need from now till my glorious death by falling computer monitors or something as ironic is an IN-MEMORY DB (speed, compactness) for desktop single-user access.


    Can be useful whether I use VB6 or move on to C# or Python or whatever. (I truly want to advance to modern languages. If it weren't for having to support my old VB6 apps I'd already be on that path.)

    That's why SQLite as the chosen DB once I've learned what I need.

    But that doesn't mean I cannot learn the other stuff if it gets me to my chosen goal in the end.

    =========================

    What I gather is of the UTMOST importance is to get SQL itself down, regardless what DB it is tied to. The similarities are close enough that making small adjustments once writing SQLite code won't be bogged down due to query writing.

    Once done, and I'm somewhat comfortable with table relationships, groups, queries and all that, I will need to focus on SQLite/VB6 specific things. And with that, I'm told that using RC6 SQLite should make things easier? Perhaps, but I have yet to figure out how anyone learns SQLite/VB6 using RC6 without some centralized documentation that lays out all the available methods/commands and the logical use of it. The hunt and peek (and advice to 'use search') to learn makes my head hurt (literally, I'm 14 days in a row now with a headache!)

    Anyway, hopefully this kinds of explains what/why I'm doing or asking about this. 20 years ago I would have learned this quickly. It's a little more challenging these days as a retired dude. ;-)

    Thank you all!

  14. #14
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: SQLite, SQL and VB6 - Where to start?

    You can study SQL generically and academically, and that is useful because it helps to comprehend the "world view" assumed when writing declarative SQL (as opposed to procedural code in BASIC or something).

    You can study SQL pragmatically after that, where you read the reference material for a specific SQL dialect. That gives you the range of supported operations and their syntax for a specific implementation. Avoid distractions of some implementations that chose to extend their SQL syntax to add server scripting like SQL Server did with T-SQL. Treat server-side operations as a separate topic. Every product (that has them at all) implements them in different ways.

    But most casual coders prefer to skip all of that and want sample code to parrot. This can get quick results but can lock you into a world of frustration. You feel the tail and think the elephant is a rope, since you have blinded yourself. Growth is tough because you embarked on a journey of fumbling. Instead of learning to fish you are stuck with begging for fish. You may even eat the heads and throw the rest away because you can't see the entire fish.

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by dilettante View Post
    But most casual coders prefer to skip all of that and want sample code to parrot. This can get quick results but can lock you into a world of frustration. You feel the tail and think the elephant is a rope, since you have blinded yourself. Growth is tough because you embarked on a journey of fumbling. Instead of learning to fish you are stuck with begging for fish. You may even eat the heads and throw the rest away because you can't see the entire fish.
    Guilty. Due to having multiple hats in running my business(s) (even in retirement mode), it's been difficult to spend too much time on any given task.

    But it has definitely put me into a world of frustration (and repeated headaches).

    I'm trying to focus on the SQL part at the moment, although a bit difficult while watching the SP500 rip higher against all odds and ripping my trades to shreds. :-0

  16. #16
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,294

    Talking Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by webbiz View Post
    What I will likely need from now till my glorious death by falling computer monitors or something as ironic is an IN-MEMORY DB (speed, compactness) for desktop single-user access.
    If that's all you need then probably you don't need to learn SQL at all, especially if you all your data can fit in one table.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,382

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    If that's all you need then probably you don't need to learn SQL at all, especially if you all your data can fit in one table.
    OUCH!

    That's like driving a car without having a single clue how a car works.
    You have a flat tire, and you don't know what to do.


    Nevermind, that there are things, you can do with SQL within one Statement, which takes convoluted code in the Frontend to achieve the same
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,294

    Lightbulb Re: SQLite, SQL and VB6 - Where to start?

    The OP is already suffering from massive information overload trying to learn everything at once (most of which he doesn't need for his current project). I'm just saying that for what he needs it is more than enough to use a disconnected ADO recordset as explained in this article (complete with VB6 code):

    Store ADO Recordset Data Without a Database

    Quote Originally Posted by Zvoni View Post
    That's like driving a car without having a single clue how a car works.
    And yeah, 90% of drivers have no idea how a car works, especially nowadays when most cars are equipped with complex electronics!
    Last edited by VanGoghGaming; Feb 2nd, 2023 at 08:59 AM.

  19. #19
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    The OP is already suffering from massive information overload trying to learn everything at once (most of which he doesn't need for his current project). I'm just saying that for what he needs it is more than enough to use a disconnected ADO recordset as explained in this article (complete with VB6 code):

    Store ADO Recordset Data Without a Database
    Yea, but can you run queries against it using that method. It's a shame that VB6 doesn't have something like LINQ built in where you could run complicated queries on normal every day arrays right out of the box with no fuss. That would have been perfect for OP I think.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  20. #20
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,294

    Re: SQLite, SQL and VB6 - Where to start?

    Yeah, the disconnected recordset has a "Filter" property which has an uncanny resemblance to a "SELECT" statement complete with "WHERE" clause. Of course it isn't capable of complex stuff like "INNER JOIN" with multiple tables but then again does the OP need that level of complexity?

  21. #21
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    Yeah, the disconnected recordset has a "Filter" property which has an uncanny resemblance to a "SELECT" statement complete with "WHERE" clause. Of course it isn't capable of complex stuff like "INNER JOIN" with multiple tables but then again does the OP need that level of complexity?
    I suppose that would be acceptable for simple data scenarios.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  22. #22

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    If that's all you need then probably you don't need to learn SQL at all, especially if you all your data can fit in one table.
    The data could fit in one table.

    So am I missing something?

    There is a lot of manipulation on that data I will need to do.

    And it has been demonstrated that this can be done easily with SQL.

    > Select data from record# x to y.
    > Convert all data to weekly, monthly or yearly time-frame
    > Select all data that falls on a particular calendar mm/dd of each year (seasonal analysis) and perform math on it.
    > etc.

    If I don't need SQL for this, what's the point of a DB? I could stick to my old approach to load up arrays of struc and manipulate via VB6 routines.

    I think that discussion/debate has passed though. I'm decided to go the DB route.

    :-)

  23. #23

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    The OP is already suffering from massive information overload trying to learn everything at once (most of which he doesn't need for his current project). I'm just saying that for what he needs it is more than enough to use a disconnected ADO recordset as explained in this article (complete with VB6 code):

    Store ADO Recordset Data Without a Database



    And yeah, 90% of drivers have no idea how a car works, especially nowadays when most cars are equipped with complex electronics!
    BTW, the read on 'disconnected recordsets' was very interesting. Sure is a lot of code though.

  24. #24
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    You know me and Olaf had a bit of a rivalry over VB6 vs VB.Net. One of the things that came out of it was a video where he showed how to read data from a file into a memory database and query it. Perhaps it can help:-


    Now I don't know if that uses SQLite or something else but that MemDB type he used does meet your requirements like being in memory, queryable etc.
    Last edited by Niya; Feb 2nd, 2023 at 03:56 PM.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  25. #25
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: SQLite, SQL and VB6 - Where to start?

    I'm inclined to say that a DB will become increasingly valuable. You might have a single table, now, but these things have a way of growing on people. If it really IS a single table, then there can be some simple solutions, but if you go with a simple solution and then come up with something else that you want to do, it might be harder to switch.

    Overall, I'm inclined to agree with Niya's choice, though that is partly because I have a lot of experience with SQL Server Express (free and usually easy to install), a fair amount with Access, and none with SQLLite. The problem I have with Access is that it goes against what a few people have said in this thread. While much of the SQL language is the same, Access leaves out a few points that are really quite useful, such as named parameters. It also has an unfortunate tendency to become corrupted, so more attention has to be paid to backing up the data.
    My usual boring signature: Nothing

  26. #26
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Shaggy Hiker View Post
    It also has an unfortunate tendency to become corrupted, so more attention has to be paid to backing up the data.
    Ain't this the truth! I still have nightmares.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  27. #27
    Frenzied Member VanGoghGaming's Avatar
    Join Date
    Jan 2020
    Location
    Eve Online - Mining, Missions & Market Trading!
    Posts
    1,294

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by webbiz View Post
    BTW, the read on 'disconnected recordsets' was very interesting. Sure is a lot of code though.
    LoL dude, that is the bare minimum code required to work with recordsets of data! You would do yourself a favor to read that article again and implement the code in it, run it and understand what it does. Due to the fact that it doesn't use an external database it is A LOT simpler than any other solutions you might find. It can do everything you wanted, select records based on criteria you specify (like calendar date and whatnot). Regardless of the path you choose be aware that you absolutely need to learn the properties and methods of the ADODB Recordset object because it is ubiquitous in any and all database operations.

  28. #28

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by Niya View Post
    You know me and Olaf had a bit of a rivalry over VB6 vs VB.Net. One of the things that came out of it was a video where he showed how to read data from a file into a memory database and query it. Perhaps it can help:-

    Now I don't know if that uses SQLite or something else but that MemDB type he used does meet your requirements like being in memory, queryable etc.
    Thank you. I'm already past this part as I've read in CSV into a SQLite DB, and even pulled it back out again into an ARRAY, and even alternatively just pull out what I need via rs.ValueMatrix(), etc.

    And I'm happy to say that I now have a basic understanding of SQL having studied it since yesterday on into this evening (US Central Time).

    But I'll save that video for future reference.

    :-)

  29. #29

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,245

    Re: SQLite, SQL and VB6 - Where to start?

    Quote Originally Posted by VanGoghGaming View Post
    LoL dude, that is the bare minimum code required to work with recordsets of data! You would do yourself a favor to read that article again and implement the code in it, run it and understand what it does. Due to the fact that it doesn't use an external database it is A LOT simpler than any other solutions you might find. It can do everything you wanted, select records based on criteria you specify (like calendar date and whatnot). Regardless of the path you choose be aware that you absolutely need to learn the properties and methods of the ADODB Recordset object because it is ubiquitous in any and all database operations.
    It was a lot of code compared to what I've had to do using SQLite(RC6) and VB6. Creating an 'in-memory' DB, a table with fields, transferring data in, pulling it out using SQLite(RC6) seems more streamlined.

    I did go over that code, btw. Maybe it's just me.

    :-)
    Last edited by webbiz; Feb 2nd, 2023 at 08:49 PM.

  30. #30
    Lively Member
    Join Date
    Oct 2015
    Posts
    93

    Re: SQLite, SQL and VB6 - Where to start?

    SQLite, SQL and VB6 - Where to start?
    I would start with Microsoft Access Database Engine 2010 Redistributable and Microsoft Access Database Engine 2016 Redistributable. with databases in *.mdb files
    and give myself an example like here

    Code:
    Private Sub Form_Load()
    'create *.mdb file with mdbviewer plus
    constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\databases\somedatabase.MDB;Persist Security Info=False"'
    Adodc1.ConnectionString = constr
    End Sub
    Code:
    Private Sub searchForString()
    
    Dim db_file As String
    Dim statement As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
        ' Get the data.
        db_file = App.Path
        'create *.mdb file with mdbviewer plus
        If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
        db_file = db_file & "databases\somedatabase.MDB" 
        
    
        ' Open a connection.
        Set conn = New ADODB.Connection
        conn.ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & db_file & ";" & _
            "Persist Security Info=False"
        conn.Open
    
        ' Select the data.
        If findThisString = "" Then
        statement = "SELECT name FROM names ORDER BY name"
        Else
        statement = "SELECT name FROM names WHERE name LIKE '%" + findThisString + "%' ORDER BY name"
        End If
    
        'Get the records.
        Set rs = conn.Execute(statement, , adCmdText)
    
        ' Load the Title field into the ListBox.
    
        List1.Clear
        Do While Not rs.EOF
            List1.AddItem rs!name
             rs.MoveNext
        Loop
    
        ' Close the recordset and connection.
        rs.Close
        conn.Close
    
    End Sub
    Last edited by Krzysztof#; Feb 3rd, 2023 at 09:27 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
  •  



Click Here to Expand Forum to Full Width