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:)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
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.
Free! Subscribe To Our YouTube Channel!
by 