Read and write to an access database using Javascript

Question

first I want to mention that I am aware that in web interaction with databases should
always be with server side languages due to security reasons and for the fact that javascript
as is doesn't offer and compatibility with the windows file system.

that said I am facing a though situation and I am trying to think creatively.
I am not allowed any access to a server sided scripting and SQL.

and I need to create a client based application for an intranet that will be able to store data as time progress.

I have found 2 solutions so far but none of them has enough documentation for me to use correctly.

one is a javascript library called ACCESSdb which can be found here:ACCESSdb
unfortunately I couldn't understand how to use it to write or read data from the DB...

and the other is those 3 pieces of code:

Adding a Record:

function AddRecord() {
var adoConn = new ActiveXObject("ADODB.Connection");
var adoRS = new ActiveXObject("ADODB.Recordset");

adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='/\dbName.mdb'");
adoRS.Open("Select * From tblName", adoConn, 1, 3);

adoRS.AddNew;
adoRS.Fields("FieldName").value = "Quentin";
adoRS.Update;

adoRS.Close();
adoConn.Close();
}

Removing a Record:

function DeleteRecord() {
var adoConn = new ActiveXObject("ADODB.Connection");
var adoRS = new ActiveXObject("ADODB.Recordset");

adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\dbName.mdb'");
adoRS.Open("Select * From tblName Where FieldName = 'Quentin'", adoConn, 1, 3);
adoRS.Delete;
adoRS.Delete;

adoRS.Close();
adoConn.Close();
}

Editing a Record:

function EditRecord() {
var adoConn = new ActiveXObject("ADODB.Connection");
var adoRS = new ActiveXObject("ADODB.Recordset");

adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\dbName.mdb'");
adoRS.Open("Select * From tblName Where FieldName = 'Quentin'", adoConn, 1, 3);

adoRS.Edit;
adoRS.Fields("FieldName").value = "New Name";
adoRS.Update;

adoRS.Close();
adoConn.Close();
}

out of them only the add new record one worked for me for some reason...
also I found that to read the value of any cell in the first row all I had to do was to write:

alert(adoRS(cellNum));

but how do I get the value of cells in the later rows? lets say (row 3,cell 5).

Thank you for reading this far! I'll appreciate your help a lot!

Jake

I haven't used ActiveXObject created ADODB before but you may try to change `adoRS.Delete` to `adoRS.Delete()`, `adoRS.Edit` to `adoRS.Edit()` and `adoRS.Update` to `adoRS.Update()`. You need to use a ms-access db(it is on a network share or something)? — Prusse, Mar 19 '12 at 20:38
Thanks, I will try your suggestions. yes it will be sitting on a network drive. also do you know how I can pull values from cells in rows lower then the first one? — Jake, Mar 19 '12 at 21:02
Did you try `adoRS.moveNext()`? http://msdn.microsoft.com/en-us/library/windows/desktop/ms677527(v=vs.85).aspx — Prusse, Mar 19 '12 at 21:12
Thanks it does get me to the next record. :) do you know though of a way to get straight to the 50th record without looping the adoRS.moveNext() 50 times? like lets say adoRS.moveNext(50) or something like that... — Jake, Mar 19 '12 at 21:45
also I just tried the adoRS.Delte() and it worked perfectly. thanks a lot Prusse. I didn't even bother looking at the MSDN site before because I thought all the methods in there are only for the microsoft languages(C,C++,C#,VB...) I didn't think javascript will be supported as well. was expecting something way more complicated. — Jake, Mar 19 '12 at 21:52
"ActiveXObject" is also a microsoft only extension I guess. And this is one example of how msdn can have bad documentation. — Prusse, Mar 20 '12 at 11:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9087/discussion-between-prusse-and-jake) — Prusse, Mar 20 '12 at 11:38

Igor Turman · Answer 1 · 2016-03-21T20:53:50.020

5

First, make sure that '/\' and '\' (in connection string) is just a typo in SO.

Second, here is a version of Delete command:

function DeleteRecord() {
var adoConn = new ActiveXObject("ADODB.Connection");
var adoCmd = new ActiveXObject("ADODB.Command");

adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\dbName.mdb'");
adoCmd.ActiveConnection = adoConn;
adoCmd.CommandText = "Delete * From tblName Where FieldName = 'Quentin'";
adoCmd.Execute();

adoConn.Close();
}

And, Edit command (without looping -> updates all [matching] records):

function EditRecord() {
var adoConn = new ActiveXObject("ADODB.Connection");
var adoCmd = new ActiveXObject("ADODB.Command");

adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\dbName.mdb'");
adoCmd.ActiveConnection = adoConn;
adoCmd.CommandText = "Update tblName Set FieldName = 'New Value' Where FieldName = 'Quentin'";
adoCmd.Execute();

adoConn.Close();
}

Please note, I have not tested this (do not have Access right now), so there might be some syntax bugs...

Hope it works and helps.

edited Mar 21 '16 at 20:53

answered Mar 19 '12 at 21:51

Igor Turman

2,165
1
22
25

Thank you. one question I have though is what is the difference between ADODB.Command and ADODB.Recordset? when will the one be put to a better use then the other? – Jake Mar 20 '12 at 19:20
I think these links could be helpful to see the difference: http://www.w3schools.com/ADO/ado_ref_command.asp and http://www.w3schools.com/ADO/ado_ref_recordset.asp – Igor Turman Mar 20 '12 at 20:51
`adoCmd.CommandText("Delete * From tblName Where FieldName = 'Quentin'");` didn't work for me but `adoCmd.CommandText="Delete * From tblName Where FieldName = 'Quentin'";` did work just FYI – Martin O Leary Mar 21 '16 at 12:54
@MartinOLeary, Thank you for pointing out! I made corrections. – Igor Turman Mar 21 '16 at 20:54

score 0 · Answer 2 · edited Jun 13 '14 at 10:42

    function loadDB() {

    var connection = new ActiveXObject("ADODB.Connection");
    var connectionstring = "Data Source=.;Initial Catalog=EmpDetail;Persist Security Info=True;User ID=sa;Password=Micr0s0ft;Provider=SQLOLEDB";
    connection.Open(connectionstring);

    var rs = new ActiveXObject("ADODB.Recordset");
    rs.Open("select * from emp", connection);
    rs.MoveFirst();

    var span = document.createElement("span");

    span.style.color = "Blue";

    span.innerText = "  ID " + "  Name " + "   Salary";

    document.body.appendChild(span);

    while (!rs.eof){

        var span = document.createElement("span");
        span.style.color = "green";

        span.innerText = "\n " + rs.fields(0) + " |  " + rs.fields(1) + " |  " + rs.fields(2);

        document.body.appendChild(span);

        rs.MoveNext();
    }
    rs.close();
    connection.close();
}

Read and write to an access database using Javascript

2 Answers2

Linked