Go to Top

Blog Archives

Using large parameters for SQL stored procedure with DAO

As many of you already know, SQL Server team has announced deprecation of OLEDB for SQL Server database engine (Read: we can’t use ADO because ADO uses OLEDB). Additionally, SQL Azure does not officially support ADO, though one still can get away with it using SQL Server Native Client. However, the new 13.1 ODBC driver comes with a number of features that won’t be available in the SQL Server Native …Read More

Reporting more granularly than usual

Typically, when we do reporting, we usually do it at a higher granularity. For example, clients commonly wants a monthly report of sales. The database would store the individual sales as a single record, so it’s no problem summing up the figures to the month each. Ditto with year, or even going from a sub-category to category. But suppose they need to go down? More likely, the answer will be …Read More

Ways To Do Sequential Numbering in Access

Occasionally we come across project requirements that include the ability to do sequential numbering in Access on a set of data. We cannot just use the auto-number data type because there is no promise of sequentiality but only uniqueness. In other words, if someone creates a record then later deletes it, there will be a gap in the sequence. So, what are our options? There are actually several ways, each …Read More

Working with Console Tools

We recently had a project that required us to interact with console tools. People might have different terms for those type of tools so when I speak of console tools, I’m referring to a program or script that opens a command prompt and output stuff to that prompt and maybe accept user input by typing via the command prompt. An example of such application might be ipconfig, chkdsk, telnet, wscript/cscript …Read More

Creating Custom Access Formulas and Using Them Effectively

MS Access has a very powerful support for using expressions in your forms/reports’ controls. For example, showing a sum of some column can be as simple as putting down: and we’re done! Now, sometimes, we don’t have simple requirements. We might need to calculate a percentage, which would be expressed as: This will work but not exactly without hitches. For example, what happens if the InvoiceAmount adds up to $0? …Read More

Replicating made simple: SQL Azure Data Sync

On May 13th, at 6:30 PM CST, I will be speaking about how you can easily create a solution that enable you to have your Access program working across broad geographic regions by leveraging SQL Azure Data Sync which is still in preview. In the talk, we will demonstrate how to get set up with replicating your local instance of SQL Server Express with two other SQL Azure instances; one …Read More

Running Transactions in a Stored Procedure

A lot of times, I write a stored procedure to do several things. More often than not, I might be doing mass inserts, updates or deletions. Now, I’m not your average suspenders-and-belt guy. No, I go one step further and insist on wearing an overall over my suspenders and my belt, thank you very much. So, when I have to do that kind of thing, that potentially could do brain …Read More

Working with XML Data Efficiently – Part Two

This is part two of our two-part series on working with XML data with a SQL Server backend. Part one can be found here. In part one, we showed how much simpler and effective it was to manipulate XML data by doing it directly in the SQL Server layer as opposed to front-end or middle-layer code. The inherent problem with manipulating XML is that we have to deal with both …Read More

Working with XML Data Efficiently – Part One

This is part one of a two-part series that discusses working with XML data with an SQL Server backend. Part two is discussed here. I’ve personally found XML files to be something of a bugbear. They’re going to require some kind of transformation to get it in a format that’s useful for importing or exporting. There are many, many methods to handle XML files but they can vary considerably and …Read More

Contact Us
  • This field is for validation purposes and should be left unchanged.