A: Embedded Apps with MS Access Files

Scott Waldron Updated by Scott Waldron

Microsoft Access files can be uploaded then manipulated using OleDb, which is a database driver that can access various types of database engines. For all these examples, we'll be using this uploaded Access database file:

We have highlighted the name in the table, but we are more interested in the id of the uploaded Document record. Due to the amount of code in this section, we are going to assume that you know how to create command records, and we will just be discussing the code in depth. If you do not know how to create command records,start with Commands.

All the private functions are helper functions, used by the command functions further down.

/**
Gets an Access (ACE driver) connection string for an uploaded MS Access file.
The driver must have been installed on the server.
Access 2016 driver here:
https://www.microsoft.com/en-us/download/details.aspx?id=54920
Install the 32-bit version.
*/
private def _getAccessConnStr() {
    from Trestle.Files import WebFileExtensions;
    from Trestle.Data import Document;
	
    var accId = '33704'; // the id of the document
    var accDb = Document.GetRepository().Find(accId);
    var accPath = accDb.GetServerFileName();
    return string.Format(
	'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};',
	accPath);
}

/**
Creates an Access IDbInstance for working with Access via OleDb.
*/
private def _db() {
    from Trestle.Data import AccessDbInstance;
	
    return AccessDbInstance(_getAccessConnStr());
}

/**
Adds parameters to a command. Each ? in the CommandText of the cmd will be
replaced with a value. The values should be passed as parameters to this
function, after the cmd parameter. We then use the special Dino __args__
variable to access the excess parameters passed in. Since the extra params
start at index 2 (third parameter), we use a repeat loop starting at 2 and
ending at args.Count - 1. So for example if we passed in 5 extra arguments,
we would start at args[2] and end at args[6] (2,3,4,5,6 - 5 arguments).
*/
private def _params(db, cmd) {
    var args = __args__;
    repeat i 2 to args.Count - 1 {
	db.AddParameter(cmd, '?', args[i].Value);
    }
}

The _params function demonstrates a Dino feature that allows accessing extra arguments to a function. In the example here, the first two arguments in __args__ would be the db and cmd arguments that are built into the function. Any number of other arguments may be passed after these. We use this feature to make it easier to add parameters to database commands.

When working with the special __args__ variable, it's a good idea to stash it in another variable before using the values. The reasons for this have to do with Dino memory management and disposal and are not in the scope of this document. Just know that it is a best practice.

/**
Gets selection list representing the product table.
*/
private def _getAccessProductSelections() {
    var db = _db();
    var cmd = db.GetCommand('SELECT * FROM product;');
    var results = db.GetObjects(cmd);
    each r in results {
	r.Name = r.part_number + ' (' + r.product_name + ')';
	r.Value = r.id;
    }
	
    return results;
}

For SELECT queries, we generally want to use the GetObjects method of IDbInstance. Since that method completely closes and disposes of any database connections, we do not need to explicitly create the connection. We can simply grab a command, call GetObjects, and know that all the resources have been properly disposed. This is one of the advantages of GetObjects vs. a data reader.

In these selection list helper functions, we are post-processing the results, adding Name and Value (case-sensitive, remember) properties to ensure the proper display of drop-down list options.

/**
Gets selection list representing the customer table.
*/
private def _getAccessCustomerSelections() {
    var db = _db();
    var cmd = db.GetCommand('SELECT * FROM customer;');
    var results = db.GetObjects(cmd);
    each r in results {
	r.Name = r.customer_name + ' (' + r.acct_no + ')';
	r.Value = r.id;
    }
	
    return results;
}

/**
Command function to demonstrate showing records from an Access file.
*/
def cmdDisplayAccessProductList() {
    var db = _db();
    var cmd = db.GetCommand('SELECT * FROM product;');
    var results = db.GetObjects(cmd);

    return results;
}

/**
Command function to demonstrate showing records from an Access file.
*/
def cmdDisplayAccessCustomerList() {
    var db = _db();
    var cmd = db.GetCommand('SELECT * FROM customer;');
    var results = db.GetObjects(cmd);

    return results;
}

/**
Command function to demonstrate showing records from an Access file.
*/
def cmdDisplayAccessSaleList() {
    var db = _db();
    var cmd = db.GetCommand('SELECT * FROM sale;');
    var results = db.GetObjects(cmd);

    return results;
}

/**
Command function to demonstrate calling a stored query in Access.
*/
def cmdDisplayAccessSalesReport() {
    var db = _db();
    var cmd = db.GetProcedure('sale_report');
    var results = db.GetObjects(cmd);
    each d in results {
	d.line_total = d.unit_price * d.quantity;
    }
	
    return results;
}

To call a stored query, instead of calling db.GetCommand, we call db.GetProcedure. Additionally, we just pass in the name of the stored query, rather than the entire command text of the SQL query.

In this method we are also post-processing our result set, in this case adding a line_total field to the output.

/**
Command function to demonstrate adding an Access record.
*/
def cmdCreateAccessCustomer(strCustomerName, strAcctNo, txtBillAddr, txtShipAddr) {
    var db = _db();
    with conn = db.GetConnection() {
        var cmd = conn.CreateCommand();
	cmd.CommandText =
        $$
        INSERT INTO customer
        (
	    customer_name,
	    acct_no,
	    billing_addr,
	    shipping_addr
        )
        VALUES
        (
	    ?,?,?,?
        );
        $$;
	_params(db, cmd, strCustomerName, strAcctNo, txtBillAddr, txtShipAddr);

	conn.Open();
	cmd.ExecuteNonQuery();
    }
}

When we need to do something beyond SELECT, we need to handle the connection open and disposal ourselves. The best way to work with connections is inside a with statement, which will automatically close and dispose of the connection (even if there is an error) before exiting the statement. Notice that we open the connection, but we never explicitly close it. The with statement will ensure that it is closed upon exit.

Also demonstrated here is calling the _params function with a variable number of arguments.

/**
Command function to demonstrate adding a more complex Access record.
*/
def cmdCreateAccessSale(strCustomer, strSaleNumber) {
    $$
    <param name="strCustomer"
	   selections="_getAccessCustomerSelections()" />
    $$
    from System import DateTime;

    var lastId;
	
    var db = _db();
    with conn = db.GetConnection() {
	var cmd = conn.CreateCommand();
	cmd.CommandText =
        $$
        INSERT INTO sale
        (
	    sale_number,
	    customer_id,
	    sale_date
        )
        VALUES
        (
	    ?,?,?
        );
        $$;
	_params(db, cmd, strSaleNumber, strCustomer, DateTime.Now.Date);

	conn.Open();
	cmd.ExecuteNonQuery();
	cmd.CommandText = 'SELECT @@IDENTITY;';
	lastId = cmd.ExecuteScalar();
    }

    host.setUserData('lastSaleId', lastId);
	
    return host.nextCommand('2249');
}

The ExecuteScalar method on DbCommand is used to return the first value of the first rowset. It is useful for getting things like counts, or in this case, getting the identity of the auto-incremented id in an Access database table with SELECT @@IDENTITY, which when used immediately after an INSERT returns the identity column of the most recently added row.

Remember that you will need to change the id that is passed to host.nextCommand to whatever is created when you add your command records.

/**
Adds an item to an Access sale record that has its id in user data.
*/
def cmdAddItemToAccessSale(strProduct, intQuantity) {
    $$
    <param name="strProduct"
	   selections="_getAccessProductSelections()" />
    $$
    var prods = cmdDisplayAccessProductList();
    var prod;
    each p in prods {
	if p.id.ToString() == strProduct {
	    prod = p;
	    break;
	}
    }
    var lastId = host.getUserData('lastSaleId');
	
    var db = _db();
    with conn = db.GetConnection() {
	var cmd = conn.CreateCommand();
	cmd.CommandText =
        $$
        INSERT INTO sale_item
        (
	    product_id,
	    quantity,
	    unit_price,
	    part_number,
	    item_name,
	    description,
	    sale_id
        )
        VALUES
        (
	    ?,?,?,?,?,?,?
        );
        $$;
	_params(db, cmd, prod.id, intQuantity, prod.unit_price, 
            prod.part_number, prod.product_name, prod.description, lastId);

	conn.Open();
	cmd.ExecuteNonQuery();
    }

    // loop around
    return host.nextCommand('2249');
}

The cmdAddItemToAccessSale function should be an "invisible" command record, that is its Context should be set to None (blank). Its only purpose is to be called from cmdCreateAccessSale or itself.

Custom Forms and MS Access Files

We'll be expanding the documentation in this section soon. Please contact us if you have questions.

How did we do?

A: NPOI and Excel, DocX and Word

A: Returning JSON Data

Contact