Skip to content

Home

Jump to bottom
Joshua Baldwin edited this page Aug 1, 2018 · 2 revisions

ACCESSdb is a JavaScript library used to dynamically connect to and query locally available Microsoft Access database files within Internet Explorer. All you need is an .accdb file; Access does not even need to be installed! All of the SQL queries available in Access can be executed on the fly, including SELECTs, INSERTs, UPDATEs, and JOINs. Results are output in several customizable formats including JSON, XML, and HTML.

Connecting to a Database

To create a new database connection, use the ACCESSdb contructor to create a new instance of the ACCESSdb object, passing a string containing the path to the Access .accdb file. (Make sure to double up on the backslashes!):

         var myDB = new ACCESSdb("C:\\dbfile.accdb");

To see warning popups for database errors, add the optional showErrors parameter:

         var myDB = new ACCESSdb("C:\\dbfile.accdb", {showErrors:true});

Running a Query

To query a database, use ACCESSdb's query method:

         var SQL = "SELECT * FROM myTable";
         var resultSet = myDB.query(SQL);
      
         var SQL = "INSERT INTO myTable VALUES(123, 'abc 123', 'xyz')";
         if(myDB.query(SQL)) {
            alert("Inserted!");
         }

If a query fails, the method returns false:

         var SQL = "UPDATE myTable SET col1 = 456, col2 = 'def 456', col3 = 'zyx' WHERE col1 = 123";
         if(!myDB.query(SQL)) {
            alert("Oh no!");
         }

By default, the record set of results is loaded into a JavaScript Array object. To specify a different output format, add the optional parameter for the desired format. The available choices are "xml", "json", or "table".

         var rsXML  = myDB.query(SQL, {xml:true});
      
         var rsJSON = myDB.query(SQL, {json:true});
      
         var rsHTML = myDB.query(SQL, {table:true});

Customizing Output

Tables can be given IDs and can be assigned CSS classes using the optional id and className parameters:

         var rsHTML_Str  = myDB.query(SQL, {
             table: {
                 id        : "myTable",
                 className : "myResultSet"
             }
         });

Table Headers

You can also choose not to display the column headers with the optional noHeaders parameter:

         var rsHTML_Str  = myDB.query(SQL, {
             table: {
                 noHeaders    : true
             }
         });

Customizing Dates

When returning XML or an HTML table, you can customize the formatting of Date columns using the optional formatDates parameter. This parameter is an associative array wherein each key is the name of a table column to format and the value is a format string describing how dates in that column should be displayed.

The format string uses the following abbreviations:

|

Unix Time
**@** (Unix Timestamp)
Year
**yyyy** (4 digits) **yy** (2 digits) **y** (2 or 4 digits)
Month
**MMM** (name or abbr.) **NNN** (abbr.) **N** (initial) **MM** (2 digits) **M** (1 or 2 digits)
Day of Month
**dd** (2 digits) **d** (1 or 2 digits)
Day of Week
**EE** (name) **E** (abbr) **ee** (2 char) **e** (initial)

|

Hours (1-12)
**hh** (2 digits) **h** (1 or 2 digits)
Hours (0-23)
**HH** (2 digits) **H** (1 or 2 digits)
Hours (0-11)
**KK** (2 digits) **K** (1 or 2 digits)
Hours (1-24)
**kk** (2 digits) **k** (1 or 2 digits)
Minutes
**mm** (2 digits) **m** (1 or 2 digits)
Seconds
**ss** (2 digits) **s** (1 or 2 digits)
AM/PM
**a**

|

Examples: "MMM d, y" outputs: "January 01, 2000" or "Dec 1, 1900" or "Nov 20, 00"

"M/d/yy" outputs: "01/20/00" or "9/2/00"

"MMM dd, yyyy hh:mm:ssa" outputs: "January 01, 2000 12:30:45AM"

"@" outputs: "915177604200"

Usage:

         var rsHTML_Str  = myDB.query(SQL, {
             table: {
                 formatDates    : {
                    dateColumn1 : "M/d/yy",
                    dateColumn2 : "MMM dd, yyyy hh:mm:ssa"
                 }
             }
         });

To apply a particular format to all Date columns within an XML or HTML result set, pass the format string directly into the formatDates parameter:

         var rsXML_Str  = myDB.query(SQL, {
             xml: {
                 formatDates    : "yyyy-MM-dd HH:mm:ss"
             }
         });

String Output

When selected, XML is output as an XML Document Object by default. XML can also be output as a string by using the optional stringOut parameter:

         var rsXML = myDB.query(SQL, {xml:{stringOut:true}});

When selected, the HTML table is output as an HTML table element object by default. Tables can also be output directly into a string by using the optional stringOut parameter:

         var rsHTML_Str  = myDB.query(SQL, {table:{stringOut:true}});

Batch Insertion

To insert multiple rows into a table in one call, use ACCESSdb's insert method. This method can receive the data to be inserted in the form of a JSON object, a JSON String, an XML Document Object, or an XML String. (All in the same format as is output by ACCESSdb.)

         myDB.insert("myTable", [
            {
               col1:25,
               col2:"something",
               col3:"something else"
            },
            {
               col1:345.55,
               col2:"something",
               col3:"something else"
            },

            {
               col1:234.1,
               col2:"something",
               col3:"something else"
            }
         ]);
      
         myDB.insert("myTable", "354.01Some value45Some \"value\"");

Error Handling

To add your own error handling function to a query or a batch insert, use the optional errorHandler parameter. A pointer to the Error object is passed to this function. If an insert fails, the method stops processing input records and returns false.

         var rsHTML = myDB.query(SQL, {
            table:true,
             errorHandler: function(e) {
                $("#errorBox").html("**" + e.name + ": **" + e.description);
             }
         });** **
         myDB.insert(
            "myTable",
            [
               {
                  col1:25,
                  col2:"something else"
               },
               {
                  col1:345.55,
                  col2:"something"
               }
            ],
            {
             errorHandler: function(e) {
                alert("Error inserting to myTable!\n\n" + e.description);
             }
            }
         );
Clone this wiki locally