[RESOLVED] Learning from the beginning? (CAIRO SQL)

[-Corso->]

Hi All,

I've hit that point where I desperately need to uncomplicate my current code.
Situation: Using huge arrays which call other lookup arrays which call other lookup arrays. It's small in data usage, but it's becoming a mess to look after.

I was learning SQL slowly at one stage but got pushed off that due to health. I can start again, but I was wondering if someone might throw a titbit, simply because I could forge ahead quite a long way before getting into more complex stuff.

Long story short, I'd like to know how to look up an ID spot and pull it's data. Not much more than that for now. Is this a big job?

For example: If this was a demo csv. How does one go about calling record ID#3's, Stat1?
And Or, can one pull the whole row into variables at once?
Demo.csv: Simple Example
ID,Category,Name,Stat1,Stat2
1,"Sword","Basic Sword",20,10
2,"Sword","Hard Sword",40,30
3,"Mace","Basic Mace",5,80
4,"Spear","Basic Spear",90,5

If this is too much? Happy to let it slide, but if someone feels like giving us plebs a hand, I'd be super grateful!

[Zvoni]

Sample-Code

'Needs Reference to MS ADO
Dim cn As Connection
Dim rs As Recordset

Sub main()
    Set cn = New Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""C:\Path\To\Some\Folder""";Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open "SELECT Stat1 FROM test.csv WHERE ID=3", cn
    Debug.Print rs("Stat1")  'Prints "5"
End Sub

Note: In DB-Speak: for Text/CSV-Files, the Folder those files are located, is the "Database", the individual Text/CSV-Files are the "Tables"
Note2: See in my SQL above you have to provide the Filename incl. the File-Extension in a SELECT-Statement

[-Corso->]

Owh, Thanks Zvoni. I'll do a bunch of testing tomorrow with this.
Very much thanks!

[Schmidt]

For example: If this was a demo csv...

Please don't start creating CSV-files when you have a real "SQLite-DB at your fingertips"...

ID,Category,Name,Stat1,Stat2

Making this "CSV-Header" into a proper (typed) DB-Table is not all that much more typing-effort:

Dim Cnn As cConnection
Set Cnn = New_c.Connection(PathToYourDBFile)
    Cnn.Execute "Create Table MyTable(ID Integer Primary Key, Category Text, Name Text, Stat1 Int, Stat2 Int)"

How to retrieve Recordsets is your bread and butter (Zvoni already gave an example for ADO-ones above, but SQLite-Rs works similar):

Set Rs = Cnn.GetRs("Select * From MyTable Where ID=?", 2)

Here is a working Demo (for a simple Table with only two Fields) -
which introduces the "Blob" FieldType - and shows how to store JPG-Images in an SQLite-DB-Table)
https://www.vbforums.com/showthread....=1#post4913861

I'd experiment with such smaller Tables in isolated Demo-Projects first - until you "get the hang of it"...

Olaf

[baka]

I have a "itemeditor.exe" that I use for my "items".
currently 4194 items where each one has a "icon" and lots of different stats.

so everything is saved into a "ui_data.dat" file that the main-program is loading at startup.
the same is monsters, npc, worldmap, maps, story and I have a huge-string-table that I use as well.
ui_data that is only info is 2.4MB

that way I don't have everything in one place. better split it into different places.
and using .txt/.ini files, its too much. need to be an editor of sort.

also everything is stored in UDT
example of how it looks for "items"

Public Type ItemData
    Name                As ByteArrayString
    Description         As ByteArrayString
    Job                 As JobDataTypes
    Req                 As ReqDataType
    Rarity              As Single
    Value               As Long
    Count               As Long
    Mesos               As Long
    Item                As Integer
    Pic                 As Integer
    Attack              As Integer
    Magic               As Integer
    Defense             As Integer
    Accuracy            As Integer
    Avoidability        As Integer
    Str                 As Integer
    DEX                 As Integer
    INT                 As Integer
    LUK                 As Integer
    Poison              As Integer
    Darkness            As Integer
    Elemental           As Integer
    PoisonResistance    As Integer
    speed               As Integer
    hp                  As Integer
    MP                  As Integer
    Type                As Byte
    Subtype             As Byte
    Category            As Byte
    Idx                 As Byte
    Weapon              As Byte
    ScrollType          As Byte
    AttackSpeed         As Byte
    Upg                 As Byte
    Font                As Byte
    Shop                As Byte
    Affinity            As Byte 
    Drop                As Integer
    Meso                As Integer
    Exp                 As Integer
    Cape                As Integer
    Locked              As Byte
    ReType              As Byte
    ReChance            As Byte
    ReRange             As Byte
    Invulnerability     As Integer
    Chances             As Integer
    Harvesting          As Integer
    Time                As Long
    Instant             As Byte
    Set                 As Integer
    tmp3                As Long
    SetTotal            As Integer
    Tmp5                As Integer
    Tmp6                As Integer
    Tmp7                As Integer
    Tmp8                As Integer
    Tmp9                As Integer
End Type

[-Corso->]

Thanks for the extras people. I'm just going to do the wriggle before I crawl thing first.

Um, Sorry Zvoni, It's already balking at

cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=""C:\Users\CONTROL\Desktop\Game Components\DD\Software\Individual Programs\SQL Test"";Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"

Error: Provider cannot be found, It may not be properly installed.

I added in the References via Microsoft Recommendation: https://learn.microsoft.com/en-us/sq...he-ado-library



Seemed all good there.

There was an extra " in your cn.Open line, so I removed it, VB was happy then. But running the code gave the error above.

I'm waaaay to green to find out what is wrong. Seeing back to back "" was already freaking me out.
I chucked in my directory, naturally. Also tried using a holder variable, but it didn't like that.
As in: Folder_Path = App.path , oh, wait, does it want a full string with "'s in it?

Still, I'm not sure what I need to do from here?

[VanGoghGaming]

You need to install that Microsoft ACE OLEDB Provider. It may come as a part of Microsoft Office so if you don't have that installed you may get it separately from here: https://www.microsoft.com/en-us/down....aspx?id=13255

[-Corso->]

You need to install that Microsoft ACE OLEDB Provider. It may come as a part of Microsoft Office so if you don't have that installed you may get it separately from here: https://www.microsoft.com/en-us/down....aspx?id=13255

Ok, just did that for the non 64bit version. No luck.
Do I need the 64 bit one instead?

Edit: It doesn't work for either scenario.
Same error as usual.

[VanGoghGaming]

I don't know but apparently the connection string should be “Microsoft.ACE.OLEDB.12.0” (not 16.0). Try that instead.

[-Corso->]

I don't know but apparently the connection string should be “Microsoft.ACE.OLEDB.12.0” (not 16.0). Try that instead.

Excellent! And the Old NON x64 install worked.

Thank you VanGoghGaming!

[-Corso->]

Ok, that's working. Next on the list of pleb wonders of SQL.
Does anyone know how to rs.AddNew ?
I've added in the adOpenKeyset, adLockOptimistic for writing to the rs. (according to Microsoft documenation).
I found on help threads, that this is the way to do it. Using rs.AddNew Fields, Values
Where flelds and values are arrays.
But I couldn't get it to work. Error: Item cannot be found in the collection corresponding to the requested name or ordinal



Sub Form_Load()
'Needs Reference to MS ADO
Dim cn As Connection
Dim rs As Recordset
Set cn = New Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Users\CONTROL\Desktop\Game Components\DD\Software\Individual Programs\SQL Test"";Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT Stat1 FROM Demo.csv WHERE ID=3", cn, adOpenKeyset, adLockOptimistic
Debug.Print rs("Stat1") 'Prints "5"

rs.MoveLast

Dim Fields As Variant
Dim Values As Variant
Fields = Array("ID", "Category", "Name", "Stat1", "Stat2")
Values = Array(5, "Axe", "Basic Axe", 50, 50)
rs.AddNew Fields, Values

rs.Close
End Sub


Thanks for helps!

[VanGoghGaming]

I'm not much of an SQL expert but when you did "SELECT Stat1 FROM Demo.csv" that means your recordset only has one field, "Stat1". Verify by doing "Debug.Print rs.Fields.Count". You want to do "SELECT * FROM Demo.csv" instead if you want to add a new record containing all fields.

You can enumerate the Fields collection too:

Dim fld as Field
For Each fld In rs.Fields
    Debug.Print fld.Name
Next fld

Also I don't know the format of your database but it doesn't make much sense to include the "WHERE ID=3" clause and then add a new record where "ID" is 5...

[-Corso->]

Holy smokes! That worked!

Yay, I updated the mini CSV with the new data set. (rs.Update)

Oh, so the * means all fields. Well how about that, I'm actually learning something. You actually need to 'Select' a region or thing to perform an operation. I did not know this.



The CSV is in my first post at the top of the page. Just little test lines:
ID,Category,Name,Stat1,Stat2
1,"Sword","Basic Sword",20,10
2,"Sword","Hard Sword",40,30
3,"Mace","Basic Mace",5,80
4,"Spear","Basic Spear",90,5

Many thanks again VanGoghGaming.

And, also learning one needs to close and open the record set each time for new actions. Certainy would be efficeint with Subroutines doing all this.

Little steps, little steps.

[VanGoghGaming]

You only need to open the recordset once, do all your updates and then close it when you're done with it. For example you can call this sub to add as many new records as you want for an open recordset:

Private Sub AddNewRecord(ParamArray Values())
    With rs
        .AddNew
        .Fields(0) = Values(0)
        .Fields(1) = Values(1)
        .Fields(2) = Values(2)
'        add more fields as needed
        .Update
    End With
End Sub

[-Corso->]

Oh! So you can use number positions as the column values. That helps a tonne!

Time to do some more messing around with it.

[VanGoghGaming]

Yeah, you already got that clue in the error message you encountered before:

Error: Item cannot be found in the collection corresponding to the requested name or ordinal

Ordinal means number in this context.

[DrBobby]

If you go the sqlite route as Schmidt suggested you can use a tool named heidi sql to work / play around with your db easily. Its totally free and supports sqlite, mysql, and a whole bunch of other db types. You can show the table structures and all of their data pretty easily. Even lets you write queries and see their results. I use it all the time for working out my sql statements. https://www.heidisql.com/

[webbiz]

If you go the sqlite route as Schmidt suggested you can use a tool named heidi sql to work / play around with your db easily. Its totally free and supports sqlite, mysql, and a whole bunch of other db types. You can show the table structures and all of their data pretty easily. Even lets you write queries and see their results. I use it all the time for working out my sql statements. https://www.heidisql.com/

Nice handy app. Access to different types of SQL DB's from a single app made having multiple apps (one for each flavor of SQL DB) unnecessary on my drives. Thanks for mentioning it.

[-Corso->]

Thanks DrBobby.
That does look like it'll do some handy work down the line. But at the moment, it's like 'casting pearls before swine'. And I'm the PIGLET.

Learning Olaf's SQLite 'for me at my knowledge level', is like tossing a squealing baby into a wolf infested forest, smearing barbeque sauce all over it and putting a sign up saying, "Wolf family meal".

Spartan learning via tech demo's is a death sport. Right now, I just need the most simplistic 'infant grade' exercises which will make me want to, a) continue with it, b) enjoy doing and learning.

I am having fun with it, this moment. I was using ChatGPT to help with solutions, but that keeps throwing up things that just don't work. It's too early in the morning for me to go smashing through boxes to find my VB Book, so I'll check it later.

Though, it's really great that that Super-SQL-Users around here share their mystical and ancient SQL sword arts. Thank you all!


This is how you all learned it, isn't it.

[webbiz]

Learning Olaf's SQLite 'for me at my knowledge level', is like tossing a squealing baby into a wolf infested forest, smearing barbeque sauce all over it and putting a sign up saying, "Wolf family meal".

Now this comment surprises me @Corso

As the resident eternal-noob all things VB6, I have been working with the RC6 SQLite and find it relatively easy to work with. Sure there are times I don't know what a method does, or what options I have available to me due to there being little (or no) documentation on it. But once you do one or two tasks with it, it's really not hard to use.

For the most part, it is going to be SQL (not the RC6) that you have to really learn. That's a DB thing, not a RC6 thing.

Personally, I really like the RC6 SQLite package. And I have an old app and a new testchart app that uses it. A DB server all tied up into the app itself. Simple pimple, pop that dimple.

[-Corso->]

I'm at the zone of complete unfamiliarity with SQL.
I did not know that * is actually a thing. That you need to ‘inject’ SQL heroine, then execute oneself to make it do something. And all the rest. I have to start in the most basic of basics if I want to get somewhere.
I can’t really see myself learning anything until a few people beat me with SQL sausages.

Sure, later on, I'll jump over to it. But I didn't realize I had all this pre-stuff to understand first.

[DrBobby]

Have you checked out the stuff w3schools has for sql. It starts off pretty basic, explains stuff very well and even lets you try stuff out in the browser as they introduce it. Highly recommend if you want to learn more about sql. The tutorial is for mysql but the syntax is basically the same as sqlite. https://www.w3schools.com/sql/

[-Corso->]

Have you checked out the stuff w3schools has for sql. It starts off pretty basic, explains stuff very well and even lets you try stuff out in the browser as they introduce it. Highly recommend if you want to learn more about sql. The tutorial is for mysql but the syntax is basically the same as sqlite. https://www.w3schools.com/sql/

[VanGoghGaming]

For your current purpose of creating a database of items in your game you absolutely do not need to learn SQL at all. It would only serve to sidetrack you from your goal.

Just read this MSDN article Store ADO Recordset Data Without a Database.

It shows how you can create a "Business Card Manager" app using VB6 and ADO, perfect for your need.

[-Corso->]

For your current purpose of creating a database of items in your game you absolutely do not need to learn SQL at all. It would only serve to sidetrack you from your goal.

Just read this MSDN article Store ADO Recordset Data Without a Database.

It shows how you can create a "Business Card Manager" app using VB6 and ADO, perfect for your need.

Are you sure? I know maps are going to be millions upon millions of data points. Even character's and their armours/weapons/items are going to go well past 100,000's of individual items too. Add in screen graphic updates, it could get rather painful.

[VanGoghGaming]

Yes mate, ADO recordsets have a "Filter" property that can return highly targeted records in the same way an SQL query works. Also if you plan to store that much data in your database then the "CSV" format might not be suitable since everything is a "String" in there. Saving the database in the ADO proprietary format will guarantee better performance. It also has the advantage of not needing an additional ODBC Provider and drivers just to work with the database. Read that article, run the demo samples and decide for yourself. The alternative is using SQLite as others have pointed out but the learning curve is much steeper in that case.

[-Corso->]

Yes mate, ADO recordsets have a "Filter" property that can return highly targeted records in the same way an SQL query works. Also if you plan to store that much data in your database then the "CSV" format might not be suitable since everything is a "String" in there. Saving the database in the ADO proprietary format will guarantee better performance. It also has the advantage of not needing an additional ODBC Provider and drivers just to work with the database. Read that article, run the demo samples and decide for yourself. The alternative is using SQLite as others have pointed out but the learning curve is much steeper in that case.

Actually, now that I recall, I believe I was learning ADO stuff in the Sam's VB guide, way back when. It’s sort of crawling back into my brain. For the SQL, SQLite, or whatever, I don’t even know what their DB files look like, how to set them up or even how to get data into them. At least the ADO stuff will take a CSV straight up. Anyway, it’s as you say, I cannot afford to be distracted. Making it work first, so I can check and balance everything is critical. How it accesses the data is an optimisation issue and that is secondary to examining it’s functionality.

I’ll go the quick ADO route for now as I’ve got written down, full examples in front of me.

[Zvoni]

For your current purpose of creating a database of items in your game you absolutely do not need to learn SQL at all. It would only serve to sidetrack you from your goal.

Just read this MSDN article Store ADO Recordset Data Without a Database.

It shows how you can create a "Business Card Manager" app using VB6 and ADO, perfect for your need.

I disagree.
over 90% of any software store data, and within there in over 90% a Database is used.
For nowadays Programmers, IMO, SQL is mandatory, because it provides you a perfect Interface to manipulate that Data without relying on any mechanisms your chosen language gives you.
In the last 20 years i've not used any of those "AddNew", "Edit", "Update"-Methods even once.
Always per SQL

[baka]

as I game-programmer I dont use such thing.
a UDT/array is what I found to be the best and it easy to know what to use when u code
I mean.

mob.hp
mob.mp
mob.....

or

item.value
item.name
item.description

and so on.

[-Corso->]

Zvoni, you're right, but at the moment, it's best that I get the functionality working.
Optimization always plays second fiddle to 'getting it working'.

I figure, by keeping the Database crawling out of the main code and in it's own little area, it will be a lot easier to SQL convert later, as needed.

Baka, I cannot get away with array's and type arrays anymore.
For example, I have to lookup Materials Tables for armour, (CSV)
With headings such as: Unique Name / HP Per Unit / Mass Per Unit / Blunt Resistance / Slash Resistance / Layer Allowed / Inherent Movement Restriction / Colour Bob R / Colour Bob G / Colour Bob B
Then to cross reference with Clothing Shapes data tables (CSV), (Apparel made with limbs)
Then check that to Species Body Limb Tables[Image clipping rules](CSV), then Species Body Part data tables(CSV), then Body Limb area tables(CSV).

It's becoming a nuisance to pull data and match data through long winded search routines through these type-arrays. It's fast, yeah, but it's completely ridiculous from a code-visual perspective. Ie: It's driving me nuts figuring out Which ID Table or Super ID table I need to refer to, then continue referring to the rest of the tables from the ID's found in that, and so on. It's very counter-productive for a Data-Heavy game (Roguelikes are just this way).

Frankly I'm fairly certain I can eliminate 90% of the code, just by filtering to a 'thing'. Most of these tables are short, less than 200 items, so speed shouldn't be a problem.
Inventory is 500 items, so it won't matter either, but it's a type-array searching/sorting/filtering nightmare.
Same with combat using type arrays, too many zones for making silly mistakes in code.

Anywoo, I've got an issue,... Next post....

[-Corso->]

VanGoghGaming, I spent a while constructing that example you gave the link for. But, it failed to operate. And I couldn't figure out why in the end. Plus, it's an 'everything' example, which makes it somewhat complex for DB wusses like me.

So, I went and did the infant thing of just making little field additons, finding the first instance of a search, bookmarking it, printing all the fields and values at the bookmark row. All fun, all sunshine, pretty easy to learn.

However, I deleted the old code that worked above for updating the Original CSV rs.update. Ie: when I was adding a new record to the mini test CSV.

So, all the new additions to the rs won't update anymore. So I'm not sure why?
I get this error now at rs.Update (bottom of the code, all the rest is just learning fluff), "Updating data in a linked table is not supported by this ISAM"

Anyone know what is supposed to happen to make this linked rs update?

Thanks for helps!

Option Explicit
Dim cn As Connection
Dim rs As Recordset
Private Sub Form_Load()
    '-------------------------------------------------------------
    '   Variables
    '-------------------------------------------------------------
    Dim A As Integer 'Counter
    '-------------------------------------------------------------
    '   Create DB Connection
    '-------------------------------------------------------------
    Set cn = New Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Users\CONTROL\Desktop\Game Components\DD\Software\Individual Programs\SQL Test"";Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"
    Set rs = New Recordset
    rs.CursorLocation = adUseClient
    '-------------------------------------------------------------
    '   Get the whole DB
    '-------------------------------------------------------------
    rs.Open "SELECT * FROM Demo.csv", cn, adOpenKeyset, adLockOptimistic
    '-------------------------------------------------------------
    '   Print All Field names and the first row values
    '-------------------------------------------------------------
    'Loop through all fields and print their names and values
    For A = 0 To (rs.Fields.Count - 1) 'Run through all fields, starts at 0
         Debug.Print rs.Fields.Item(A).Name & " : " & rs.Fields.Item(A).Value 'Print
        'the name and values on this active row
    Next
    '-------------------------------------------------------------
    '   Find Category "Axe"
    '-------------------------------------------------------------
    Debug.Print
    rs.MoveFirst 'Go to start
    Dim Search_String As String 'Our search string
    Search_String = "Category ='Axe'"
    rs.Find Search_String 'Find the row where the string occurs
    '-------------------------------------------------------------
    '   Print all field names and data values on this found row
    '-------------------------------------------------------------
    For A = 0 To (rs.Fields.Count - 1) 'Run through all fields, starts at new position
        Debug.Print rs.Fields.Item(A).Name & " : " & rs.Fields.Item(A).Value 'Print
        'the name and values on this active row
    Next
    '-------------------------------------------------------------
    '   Remember this row number
    '-------------------------------------------------------------
    Dim Bookmark As Long 'Which record we are sitting at
    Bookmark = rs.Bookmark 'Grab the current row
    Debug.Print "Bookmark " & Bookmark 'Print it
    '-------------------------------------------------------------
    '   Update 'Axe' Weapon to 'Horrid Axe'
    '-------------------------------------------------------------
    rs.Fields.Item(2).Value = "Horrid Axe" 'Change the Weapon from "Axe" to "Horrid Axe"
    Debug.Print "Item(2) New Value = " & rs.Fields.Item(2).Value 'Print the new value
    '-------------------------------------------------------------
    '   Update and Save the table
    '-------------------------------------------------------------
    
    rs.Update 'It has a spaz here.

[baka]

yeah. it all depends how u code.
I code differently than u and for me the UDT works perfectly, it would be wrong to use any database-models.
for u, instead, u code in a way where it works and the method I use u get stuck, as u point out.

what we can learn is that theres no right or wrong (even if Olaf always says there is), its how u code and what u prefer.

the "issues" u say u get when using UDT/Array doesn't exists for me.
I have a "udt/array" that has a limit of around 20.000 items right now around 4500 used, but I think it will be around 10.000 before the game is completed.
(each item has a id, so -10.000 to 10.000 the "negative" is actually just scrolls and I got like 400 of those)
so item(1) is actually a basic "sword".
monsters can drop different items, so example:
drop=1,6,15,5012,424
could be what monster(1) can drop. so 1=sword.
and the same is the library, when sorting, like the user just want to see 1h-sword weapons (I got like 100 of those) it will check those 4500 items and create a new array with just swords.
so, basically it works like that for anything. to check 4500 items is superfast since we just check the
item(x).type
as each item has a type, so weapon=1
and item(x).subtype also 1 for 1handed-swords.
the same structure is for monsters and everything else.

[VanGoghGaming]

"Updating data in a linked table is not supported by this ISAM"

That seems to be a limitation of the database driver in charge of managing "CSV" files (the one you specified in your connection string). Probably it means you can only "AddNew" and not change existing records. I told you "CSV" is not a good format for what you're trying to do.

What you can do is import the data from your "CSV" file in the recordset just like you're currently doing and then save it in a new recordset, exactly as shown in the example I've linked above. Probably you will need to import all rows one by one in a loop into the new recordset. Then you can save the new recordset in a file (rs.Save FileName) in the ADO proprietary format which allows updates and everything. So next time just open this new file with "rs.Open FileName" and you're good to go.

To be honest you should really go over that tutorial once again because it is rather short and you should get to know the basics. For example the creation of Fields which can have different types, not just "String" as in a "CSV" file.

Also instead of typing rs.Fields.Item(2).Value = "Horrid Axe", just go with the shorter variant rs.Fields(2) = "Horrid Axe"

[Schmidt]

Anyone know what is supposed to happen to make this linked rs update?

Please don't work against a CSV "through" an ADO-Rs - since it is cumbersome and error-prone...

As already said - the SQLite-functionality built-into the RC6 will allow Rs-Handling in a similar way (it's 99% compatible to ADO-Rs).
And opening an SQLite-Connection-Object can be done in a single line of code...

Here's an equivalent Demo, to what you attempted with the ADO-Rs/CSV-combination
(though working faster, typesafe - and with a bit less code)...

The example needs a reference to RC6 (in an empty Form-Project)

Option Explicit

Private Cnn As cConnection

Private Sub Form_Load()
  'ensure a Connection and create a Weapons-Table
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Table Weapons(ID Integer Primary Key, Category Text Collate NoCase, Name Text, Stat1 Int, Stat2 Int)"
  
  'ensure a few basic-records via Rs.AddNew-based Inserts
  With Cnn.GetRs("Select * From Weapons Where 1=0") '...Where 1=0 ensures a "Field-Headers-only"-Rs without any records in it
    .AddNew: !Category = "Sword": !Name = "Basic Sword": !Stat1 = 20: !Stat2 = 10
    .AddNew: !Category = "Sword": !Name = "Hard Sword":  !Stat1 = 40: !Stat2 = 30
    .AddNew: !Category = "Mace":  !Name = "Basic Mace":  !Stat1 = 5:  !Stat2 = 80
    .AddNew: !Category = "Spear": !Name = "Basic Spear": !Stat1 = 90: !Stat2 = 5
    .AddNew: !Category = "Axe":   !Name = "Basic Axe":   !Stat1 = 10: !Stat2 = 70
    .AddNew: !Category = "Axe":   !Name = "Axe":         !Stat1 = 20: !Stat2 = 90
    .UpdateBatch 'a batch-update can update multiple changes (which were priorily applied to an Rs)
  End With
  
  'get the whole Weapons-DB-Table into a (directly passed) Rs and print it
  PrintRsDump Cnn.GetRs("Select * From Weapons")
  
  'now retrieve a subset of the Weapons (filtered by Category=axe)
  Dim RsAxes As cRecordset '<- storing it in a separate "temp-Rs"
  Set RsAxes = Cnn.GetRs("Select * From Weapons Where Category=?", "Axe") '<- parametrized query via secondary-extra-args
  PrintRsDump RsAxes 'print this filtered SubSet (it should have 2 records in it, both of category "Axe")
  
  'Rs-"local" Find-Ops (here against the Name-Field) will run faster on such "smaller", prefiltered Rs
  If RsAxes.FindFirst("Name=" & "Axe") Then 'search the Name-Field with content "Axe"
     RsAxes!Name = "Horrid Axe" 'change the old Name to the new one directly on the Name-Field of the Rs (don't use Indexes)
     RsAxes.UpdateBatch 'and update the underlying table (Weapons) directly from RsAxes
  End If
  PrintRsDump Cnn.GetRs("Select * From Weapons Where Category=?", "axe") 'print the (changed) subset directly from a fresh DB-Query
End Sub
 
Public Sub PrintRsDump(Rs As cRecordset) 'prints to the VBIDE-Immediate-Window (only recommended for smaller Rs-Contents)
  Debug.Print vbLf; "Now printing"; Rs.RecordCount; "Records, retrieved from Table: "; Rs.Fields(0).OriginalTableName; vbLf; "underlying SQL: " & Rs.SQL
  Dim i As Long
  For i = 0 To Rs.Fields.Count - 1: Debug.Print Rs(i).Name,: Next 'print Field-Names
  Debug.Print
  
  If Rs.RecordCount Then Rs.MoveFirst
  Do Until Rs.EOF
    For i = 0 To Rs.Fields.Count - 1: Debug.Print Rs(i).Value,: Next 'print Field-Values
    Debug.Print
    Rs.MoveNext 'shift the "active RecordPos-Pointer" inside the Rs to the next record
  Loop
End Sub

Olaf

[-Corso->]

Actually Olaf, thank you muchly for this tutorial. That's exactly what newbies require. I'll study it obsessively. GREATLY APPRECIATED!
You know I'm still going to need more hand holding with this. :P

VanGoghGaming, yeah, that whole CSV import thing in the VB menus. It's vaguely coming back to me. I'll have a go at Olaf's tutorial before I tackle that again. It's good to have training options to review now.

Baka, I don't use table weapons, armour, monsters (only when they are initially created). Every main ‘thing’ is 'unique'. As in, every single monster, human, sword, shield, helmet, glove will be it’s own entity. Which is going to be scary for data size, but I’ll be doing things by zones, so it shouldn’t be all that bad. Only general items, like fruit, potions, ingots etc, are generic table data objects.

[VanGoghGaming]

that whole CSV import thing in the VB menus.

I have no idea what thing in what VB menus are you talking about. All I was saying is that you already have a database of items in "CSV" format and you should convert it to some other format that allows updates. Whether that's ADO format (like in the tutorial I mentioned above) or SQLite (like in Olaf's example), it's irrelevant. It's painful to recreate all existing records by hand via "AddNew" instead of importing (or converting) what you already have in "CSV" format.

[-Corso->]

I have no idea what thing in what VB menus are you talking about. All I was saying is that you already have a database of items in "CSV" format and you should convert it to some other format that allows updates. Whether that's ADO format (like in the tutorial I mentioned above) or SQLite (like in Olaf's example), it's irrelevant. It's painful to recreate all existing records by hand via "AddNew" instead of importing (or converting) what you already have in "CSV" format.

Yeah, I was reffering to that. Via the 'Add-in's menu', Visual Data Manager. That's all. I just didn't remember it all till just a little while ago.

[-Corso->]

Even the autoform builder is great.
Much easier to understand than the Business card thingy.
It's starting to come back to me, bit by bit.

[VanGoghGaming]

My thoughts on this are that if you don't understand the basic "Business card thingy" which doesn't use any SQL at all then you'll have a hard time understanding Olaf's example (beside copy-pasting it into your project)...

To move forward you should at least be comfortable creating a new recordset from scratch and appending Fields to it (think of Fields as column headers in a database table). Then you could loop through your "CSV" recordset and import your existing data into the new recordset.

[-Corso->]

I used to be super good at Access Db's & VB, but somehow, I seem to be missing all sorts of information nowdays. It was pretty easy way back when, but looks like I forgot it all(?) The SQL stuff looks reasonable.
But, ChatGPT brings up much assorted fake information. Chasing the rainbow of garbage info.
Lots of examples on the web stop short from working. This is really tiring actually.
There's multiple ways of getting/accessing recordsets, but I can't find the complete set of, this is how you make a new one (table) vs, attach to your already established (table) and do the same thing (and update), (consistent style) kind of issue. Ie: Oh yeah, thats how you do it, but it doesn't work in this instance. Eh wut?
Much of the samples I've found relate to all manner of other complicated data which just muddies the issue further.

Anyway, I usualy restart people's examples at the very top and write copycat code near it (with a tonne of explanation green text & pushing their stuff to the bottom), but adjust it enough to see it working in other ways (as a slightly different task that I set myself). That's my favoured method of learning.

Right now, Olaf, I'm just importing the DB Csv but it's saying it's not a database. Did I miss something basic again?

'Build a new Connection and connect the data
Dim Filename As String 'Filename of Database
'Set the filename
Filename = App.Path & "\Demo.csv"
'Provide the data connection to the Demo.csv
Dim Cnn As cConnection
Set Cnn = New_c.Connection(Filename)
    Cnn.Execute "Create Table MyTable(ID Integer Primary Key, Category Text, Name Text, Stat1 Int, Stat2 Int)"