Client Side Storage Access And WebSQL Database Part2

In this revised procedure I will create the SQLlite (WebSQL) database to hold
the Access query contents and then display them on a web form in a table format.

(don’t be concerned with the colors in the following code:)


Sub GenerateContainmentInspectionsHTML()
   
    'HERE YOU ARE MAKING THE DATABASE FOR THE INSPECTIONS
    
    Dim intRandomDB As Integer
    Dim intSeed As Integer
    Dim strDB As String
    
    'this is seed value for the RND function's random number generator.
    Randomize
    
    'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
    intRandomDB = Int((2500 - 1 + 1) * Rnd(2) + 10)
    Debug.Print intRandomDB
    strDB = "inspdb" & intRandomDB
    
    strPath = CurrentProject.Path & "\containment_inspections.htm"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    strSQL = "SELECT * FROM qrySecondaryContainmenttodrain"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.MoveFirst
    
    Dim objFile As Object
    Set objFile = fso.CreateTextFile(strPath, True, True)
    objFile.write " <!DOCTYPE HTML>" & vbCrLf & vbCrLf

    objFile.write "<html>" & vbCrLf
       objFile.write "<head>" & vbCrLf
       
    '=====================================================================================
    'create a WebSQL database for the Access DB containment inpections
    '=====================================================================================
            objFile.write "<script src=""https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js""></script>" & vbCrLf
          objFile.write "<script type = ""text/javascript"">" & vbCrLf
             'objFile.write "var db = openDatabase('inspdb31', '1.0', 'Test DB', 50 * 1024 * 1024);" & vbCrLf '50mb
             objFile.write "var db = openDatabase('" & strDB & "', '1.0', 'Test DB', 50 * 1024 * 1024);" & vbCrLf '50mb
             
            objFile.write "var querystring = window.location.search.substring(1);" & vbCrLf
            objFile.write "var uid1 =querystring.split('=');" & vbCrLf
            objFile.write "containment_id=uid1[1];" & vbCrLf & vbCrLf
                
             objFile.write "db.transaction(function (tx) {" & vbCrLf
             
                'YOU CANNOT USE THE WORD "GROUP", BECAUSE IT IS A KEYWORD FOR THE SQL LANGUAGE!
                objFile.write "tx.executeSql('CREATE TABLE INSPECTIONS (id INTEGER PRIMARY KEY autoincrement, InventoryID INTEGER,DrainerID INTEGER,ResponsibleDrainer,DateDrained,TimeDrained,Group1)');" & vbCrLf
                
                Do Until rst.EOF
                    objFile.write "tx.executeSql('INSERT INTO INSPECTIONS (InventoryID,DrainerID,ResponsibleDrainer,DateDrained,TimeDrained,Group1) VALUES (""" & rst.Fields("InventoryID") & """,""" & _
                        rst.Fields("DrainerID") & """,""" & _
                        rst.Fields("Responsible Drainer") & """,""" & _
                        rst.Fields("DateDrained") & """,""" & _
                        rst.Fields("TimeDrained") & """,""" & _
                        rst.Fields("Group") & """)');" & vbCrLf
                    rst.MoveNext
                Loop
             
                
             objFile.write "});" & vbCrLf & vbCrLf
            
           objFile.write "displayAll();" & vbCrLf & vbCrLf
           
          'objFile.write "</script>" & vbCrLf & vbCrLf 'same script
    '=====================================================================================
    'now display the contents of the previous section in an html table
    '=====================================================================================
    'objFile.write "<script type = ""text/javascript"">" & vbCrLf 'same script
            objFile.write "function displayAll()" & vbCrLf
            objFile.write "{" & vbCrLf
                
                'objFile.write "alert('display');" & vbCrLf
                
                objFile.write "db.transaction(function(tx)" & vbCrLf
                objFile.write "{" & vbCrLf
                    objFile.write "tx.executeSql('SELECT * FROM INSPECTIONS',[],function (tx,results)" & vbCrLf
                    objFile.write "{" & vbCrLf
                        objFile.write "var n = results.rows.length;" & vbCrLf
                        objFile.write "var s = ""<table cellpadding='2' cellspacing ='2' border='1'>"";" & vbCrLf

                        objFile.write "s += '<tr><th>ID</th><th>Name</th><th>Description</th></tr>';" & vbCrLf
                        objFile.write "for(var i=0; i < n ; i++  )" & vbCrLf
                        objFile.write "{" & vbCrLf
                            objFile.write "var containment=results.rows.item(i);" & vbCrLf

                            objFile.write "s+= '<tr>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.id +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.InventoryID +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.DrainerID +'</td>';" & vbCrLf
                            objFile.write "s+= ""<td><a href='#' onclick=del('+ containment.id +')>Delete </a> | <a href='#' onclick=edit('+ containment.id +')>Edit</a> </td>"";" & vbCrLf
                            objFile.write "s+= '</tr>';" & vbCrLf
                        objFile.write "}" & vbCrLf

                        objFile.write "s += '</table>';" & vbCrLf

                        objFile.write "document.querySelector('#result').innerHTML =  s ;" & vbCrLf
                    objFile.write "});" & vbCrLf
                objFile.write "});" & vbCrLf
            objFile.write "}" & vbCrLf & vbCrLf
          objFile.write "</script>" & vbCrLf & vbCrLf
    '=====================================================================================
       objFile.write "</head>" & vbCrLf & vbCrLf
      
       objFile.write "<body>" & vbCrLf
          
           objFile.write "<div id='result'></div>" & vbCrLf
          
       objFile.write "</body>" & vbCrLf
    objFile.write "</html>"

    
    
     MsgBox "Complete"

In future posts I’ll be adding functionality to the “Delete” and “Edit” links.

Also, this is a step by step, and I’ll probably be modifying it as I go.

Please let me know if you have any questions.