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.