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.




By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!


These posts may help answer your question too...

How To Escape Apostrophe In SQL Update Query

If you are looping a table with thousands of records, you’ll probably run into at least one that has an apostrophe in the field name. Like “Mike’s” or “M’cormick”, or something else. Anyway, here is one way to escape the string when you are doing your update query. Option Compare Database Sub YDriveLoop() ‘4/23/24 erik@loeblcomservices.com […]

How To Parse A Flat File In Excel VBA

In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]

How can I interact with other Office applications (Excel) using VBA in Access?

Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]

How To Create A Parameter Query In Access

A parameter query changes your ordinary static access query to be more dynamic and interactive. It will ask you a question about what you want to search for, allowing you to do a search query multiple times instead of just once. You can do your parameter query straight from the QBE (Query By Example) Editor, […]

Previous Post

Client Side Storage Access And WebSQL Database Part3

Next Post

How To Create A Local Web Page With Access DB Data PT1