Table Name and Column Name as Parameters in MS Access

No, you can’t use table name and column name as parameters in MS Access, unfortunately.

Here is a solution: Use a uniformed names and structures in your automations, and copy your table into a temp table with a uniformed name. After process, you copy back and overwrite the temp table to its original table name. Reasonable labour work load.

Error Massage: “Operation must use an updateable query"

In MS Access, sometimes this error massage with error number 2950 may be a problem. It might be permission issue (see http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error), or an update from issue from joined table. For later, try following method to see if it does work:

UPDATE table SET field = 3
WHERE keyfield IN(SELECT keyfield FROM query)
;

It also can be caused by calculation within the UPDATE query. In this case, the best possible solution is to calculate before save into a temp table, then to join that temp table to get the calculated result.

Add Sequence Number to an Existing Table in MS Access

Add series number to an existing table can be done by following method:

SELECT t.myDate, (
    SELECT Count(*)
    FROM myTable AS s
    WHERE t.myDate <= s.myDate) as myRank
FROM myTable AS t

However, if it does not work for you, try following option:

Alter Table Temp_SequenceChange Add Column SequenceNo AutoIncrement;

Cope With Deleted Records Recovery Issue in MS Access

MS Access does not have functionality to recover or UNDO or UNDELETE deleted records. It is easy to lose data when you mistakenly forget put () between AND and OR in WHERE clauses. There are several options to cope with it, for instance creates trail tables or backup whole database regularly. Here is another option which might be easier, better and more practicable:

1. Centralize all DELETE queries into one for better management purpose with unique query name. Since MS Access does not allow table and column name as parameters, this DELETE query will be hard coded every time when you try to delete any record.

2. Never run this query directly.

3. Put this DELETE as part of a Macro.

4. Within the Macro, before the DELETE query, put some backup queries for the table you are working with or all related key tables. The simplest way to code backup query is:

SELECT *
INTO BackupTable1
FROM WorkingTable
;

Thus, you only backup table before the deletion and can be recovered once you find the fatal mistake. You can only backup one copy only since the table name in Macro cannot be changed. Another stupidity of Microsoft.

Set MS Access As An Interface to Access Other Database

MS Access can act as an stand alone database or an interface to access ther database, or a database consists of both local tables and linked tables. To set up such links after you have been granted the access to database from DBA, do follows:

1. In Windows' Administrative Tools, click Data Source (ODBC) to set up the link.
2. In MS Access, Click External Data menu.
3. In Import, click More to find out the ODBC Database.
4. Select all tables you need.

Update with Values from Fields in Another Table in Ms Access

Ms Access uses Jet engine, which is different in somewhere from T-SQL. Here is the way to update a field in Table_1 with the values from field in Tab_B:

UPDATE Table_1
INNER JOIN Tab_B ON Tab_B.Key_Field_In_B = Table_1.Key_Field_In_1
SET Table_1.Target_Field = Tab_B.From_Field
WHERE Tab_B.Condition_Field = 'Something'

Sometimes, you may still get error message: Operation must use an updateable query. Most likely, the join key of either table are not unique key, as a result, the join action does generate more rows than that in Table_1. Since this is an update action, this does caused the error.


http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html

Pivot Large Table With Dynamic Columns

In general, it is recommended to use SUM(CASE) GROUP BY method to pivot rows into columns. For details why recommended such, please refer to http://koncordpartners.blogspot.com/2010/03/pivot-turning-rows-into-columns.html. However, in two circumstances SUM(CASE) GROUP BY method does not work:

1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.

2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.

In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.

The PIVOT function is easy:

SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q

The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.

Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.

Step 1

CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO

Step 2

Prepare dynamic queries:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q

Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:

DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'

What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q

Then, execute one by one before union together.

Step 3

To execute the queries:

EXECUTE(@query)


http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html

Using Ms Access to Update Reporting Services Database

It is strange to use legacy Ms Access to update table in reporting services database. But sometimes it happens in market. This example is because the reporting services database was first built by Ms Access as local database, then developed into SQL Server database. During the transit period the updating does still using Ms Access.

A bunch of independent queries as well as internally called queries organized into Macros. When this macro is executed, a huge reporting services table had been generated within the Ms Access.

Next step is to export this table into SQL Server by using Import & Export Data (32-bit) of SQL Server 2008. Before that, make sure the existing table in SQL Server is being changed name, else the new records will be simply append to the end of original table.

In Import & Export Data (32-bit) Date source is Microsoft Access, file name is the Access file. Leave User name and Password blank. Next, in Destination select SQL Server Native Client 10.0 and make a right selection in Server name. In Authentication tick Use Windows Authentication before make a right selection in Database. Next, select Copy data from one or more tables or views. The rest steps are easy.

Code Conventions: Microsoft Access SQL

1. Use original column name as much as possible until last presentative SELECT. This is to help for visual tracing back to where the data come from originally.

2. Never use format [something] as alias for the column, it would cause the visual confusion when you changed idea to reuse it, or extent to next level of expansion. You may use it at last presentative SELECT.

3. Always please comma (,) in front rather than at end of a line. It would enable you to easily conduct visual check, since all lines are started from a command or comma. For instance:

SELECT Col_1
, Col_2
FROM Tab
;

4. Always use your notepad or SQL editor to edit and save file. When complete, copy it onto Microsoft Access. This is because Microsoft Access will rewrite your scripts to make your life much harder. Every time when you need to edit it, open your text file to edit it and then paste it onto Access.

5. Doing your formatting job as much as possible in Access rather than at later stage at Excel or other presentation layer. It is even more important when you make your report automation.

PIVOT – Turning Rows into Columns

Here we exam various PIVOT functions/methods in MS Access, Oracle and SQL Server.

MS Access:

TRANSFORM does not work properly because it would generate many rows with a lot of zeros. Let us call it seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with IIF() does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

Oracle:

SELECT PIVOT does not work properly because of seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with CASE does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

OVER PARTITION BY does work fine, but not recommended because lack of flexibility. Example: http://baurdotnet.wordpress.com/2009/08/26/select/

SQL Server:

PIVOT does work fine, but strongly not recommended for two reasons: 1, Aggragate function whtin PIVOT can not be recalculated according to your will. 2, T-SQL does not allow ORDER BY working within the inner query, which does cause a lot of difficulties for layout arrangement.

How to Compact MS Access 2007

MS Access 2007 sucks. It does not improve what it should do but create new problems. Compact and repair in Access is important, because if you are over the size, it will give you funny error message and consume you next couple of hours to resolve the puzzle. How do you compact the database? Sure you need to find out the “Compact and Repair” in menu. Where is it? Ok, just forget this approach, because it will tell you nothing if it is really compacting for next hour or so, don’t even mention somehow you may completely delete your database. Nice, isn’t?

There is a way to work around it. Just save your database as MS Access version 2000. It will give you a really fast nice compacted new file. Why version 2000? Because version 2000 is the most stable amongst MS Access family. If you like, you then can compact it, again, by click Office button, Manage, Compact and repair… If you like, you can also save back as version 2007.

Where is the Office button? At top left corner, that round thing. Yes, that is it.

Clearing Obstacles for Automation in MS Access

There are many obstructive dialogue windows require manual response, which does really slow down the automation process. In one special case, MS Access is used to construct and update a considerable huge data warehouse. The update automation macro consists of over a hundred queries. The dialogue windows effectively destroy the automation itself.

Many of these dialogue windows can be set in Access Options. However, a special window cannot be resolved through setting. The message is “There isn't enough disk space or memory to undo the data changes this action query is about to make.” This is caused by running action queries on a large table.

Support of Microsoft regards this as an error message: http://support.microsoft.com/kb/161329, which also provides a number of solutions. Method 3, Setting the UseTransaction Property in an Action Query is particularily helpful:

If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:

1. Open the query in Design view.
2. On the View menu, click Properties.
3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.
4. Set the UseTransaction property to No.
5. Save the query and close it.

Most important, you would need to set no warnings, as follows:

1. In your first Macro row, right click mouse to insert a new row.
2. Click "Show All Actions" in Show/Hide menu.
3. In Action of your inserted first row, select SetWarnings.
4. Change Arguments value of the first row to "No".
5. Click Save.

Now you are ok to run Macros without any warning.

A Practical Case of Transpose Query

Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.

Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:

1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:

SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id

ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.

2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:

SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id

Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.

3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:

SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id

4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:

SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Put everything together:

WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Done.