So I thought I would share my results for anyone else that is where I was a month ago (searching here for answers).
Not all of us here are EXPERTS (me included ) .. so this may be of help to some other New-be here
most of my information came from this site : https://www.tutorialspoint.com/sqlite/index.htm
So here is a module that will handle a SQLite file
If anyone has suggestions or problems or bugs let me know.
If you think this should be in the "Farm" please put it there.
' ' Gambas module file '' Requires gb.db.sqlite3 '' Module file to handle SQLite file '' SQLite file handeling subroutines '' writen by GrayGhost4 Marvin Clavey June 2022 Public RS As Result Public sadata As String Public $con As New Connection Public Sub GetFileData(sFilename As String, scolumnName As String) As String Dim test As Variant Try $con.Close() ' Close the connection. The try allows it to fail without error $con.Type = "sqlite3" ' Defines the type of connection $con.Host = Application.path ' Host will be the path where the sqlite db file is $con.Name = "data.SQL" ' database name is the name of the database file" Try $con.Open() ' We activate and open the connection, the try is to allow an error If Error Then Print "Cannot Open Database. Error = "; Error.Text '' Retreve all the data from sFilename that is in scolumnName RS = $con.Exec("Select * From " & Quote(sFilename)) Return RS.All(scolumnName) '' return all the data from one column End Public Sub GetRecordData(sFileName As String, sFileColumn As String, sFileIndex As String) As String Dim saInfo As New String ' must have "NEW" or you will get a null error with .add Dim obj As ResultField RS = $con.Exec("Select * From" & Quote(sFileName) & "where" & Quote(sFileColumn) & "like" & Quote(sFileIndex)) For Each obj In RS.Fields sainfo.Add(RS[obj.Name]) Next Return saInfo End Public Sub UpdateFileData(sFileName As String, sFileIndex As String, sainfo As String) Dim saCommand As String Dim obj As ResultField Dim x As Integer = 0 For Each obj In RS.Fields ''assemble the update command "column" , "data" saCommand &= Quote(obj.Name) & " = " saCommand &= Quote(sainfo[x]) & "," Inc x Next saCommand = Left(saCommand, -1) '' remove the last "'" from saCommand $con.Exec("update" & Quote(sFileName) & "set" & saCommand & "where accname like" & Quote(sFileIndex)) End Public Sub InsertNewRecord(sFileName As String) Dim sNewRecord As String For Each obj As Object In RS.Fields ' This assenbles the File column names it to A string sNewRecord &= Quote(obj.Name) & "," ' to insert into a blank record to be edited later Next sNewRecord = Left(sNewRecord, -1) ' remove the last "'" from command sNewRecord = Right(sNewRecord, -(InStr(sNewRecord, ",") - 1)) ' Remove the first reccord Field (which is the index to the file) sNewRecord = "(" & Quote("newwAcc-" & Str(Rand(20, 100))) & sNewRecord & ")" ' Add a new first field (random) for index $con.Exec("INSERT INTO " & Quote(sFileName) & " VALUES " & sNewRecord) End Public Sub deleatOneRecord(sFileName As String, sFileField As String, sFileIndex As String) '' Deleat one reccord from the table with an indexed coluem $con.Exec("delete from" & Quote(sFileName) & "where" & Quote(sFileField) & "like " & Quote(sFileIndex)) End