Mystery tables

Question

I have an Access 2003 app with a front end / back end. On a regular basis, I find new tables in the front end, all named like this:

Admin - 00

Admin - 01

Admin - 02

Etc

The single field in each of these tables is named "ODBC" and has two identical records, the content of which is this:

02000 - 100 - [IBM][CLI Driver][DB2/NT64] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000.

This is obviously related to some call via the DB2 DSN in this application; however, these do me very little good because the users never report ODBC-related errors, and there is no field in these tables to even indicate when this occurred. I remove these tables any time I find them; however, in one case, removing these tables resulting in an unrecoverable front end that I then had to restore from a backup.

How can I prevent creation of these tables?

Answer 1

I think it's your own code creating them. Search the VBA code for "Admin"...

---

jmh

Answer 2

Nope. I wrote this, and I do not create tables or even table links via code. A few of the tables were manually created in the front end, and the remainder are manually-created links to either the Access back end or the DB2 database (via the DB2 ODBC DSN).

Nevertheless, I searched my entire application for the string "Admin" and came up with nothing.

Answer 3

You are executing a SQL statement that failed.  The warning message is created and stored in a new table.

This is similar to what happens when you import data into a table and some of the imports fail.  You get a table with an error message for each row that failed.

In this case the results was empty when an empty set was not expected.

When you step throught he code what statement causes an Admin - xx table to be created?

---

http://www.saberman.com

Answer 4

Thank you. I knew it looked similar to the import failure tables, but I could not pin it down.

That explains it, although narrowing it down to the specific call will be very difficult. This program can run for weeks without an error, and then when I need to make some change to it, there will be several/many Admin - ## tables there. I have been entirely unable to reproduce it by stepping through the code.

There are many, many queries as well as SQL insert/update statements in VBA code, and most of these interact with the DB2 database via the DSN, so it may be like the proverbial finding a needle in a haystack. I wish the auto-created table would at least give me a datetime stamp so I could have a clue regarding which user is using the application when this happens.

Answer 5

Note on the numerous queries -- if the queries are groups of basically the same types of queries but different parameters -- you can reduce the number of queries significantly by just having one of each type of query and using parameters.  In the query window -- in the criteria field you can right-click and select build.  From the window that comes up you can pick a form (choose a loaded form -- a form that is already running) then select a field to query on -- double click this field.  then in the criteria field you will see something like [Forms]![Form1]![Text0].  You can also add a wildcard like this to the criteria

Like [Forms]![Form1]![Text0] & "*"

You need the keyword Like  and the wildcard "*"

---

Rich P

Answer 6

This is not an issue. I am not sure how adding runtime parameters to the queries helps when each query has different fields from different tables. This is a legacy import/export app that handles many different import file formats, each with its own very unique requirements, so the queries are widely varied in the required fields, and I am in the process of removing queries and replacing them with VBA code that accomplishes the same thing anyway.

I am not sure how reducing the number of queries would help pinpoint the source of my mystery table problem, though, since no error arises for the user, and by the time I see the mystery tables, it is long after the query has run.

Answer 7

You should be able to right click the auto-created tables and see when they were created from their property.

HTH

Answer 8

I am not sure how reducing the number of queries would help pinpoint the source of my mystery table problem, though, since no error arises for the user, and by the time I see the mystery tables, it is long after the query has run.

(with all due respect) when you have numerous queries of the same tables -- that is redundant.  You can reduce this redundancy with VBA by creating your queries dynamically with -- say -- a select statement where the user selects a query (for example) and based on the selection you create the query at runtime.  If anything -- this would reduce the number of objects in your Access DB, and this may help to isolate at what point a "Mystery" table is created -- or maybe even eliminate these "Mystery" tables.

---

Rich P

Answer 9

I do understand the redundancy issue, but this is not an application that my client wants to pay me thousands of dollars to re-code so there are fewer queries. This is an app whose original design I did seven or eight years ago before I learned how to modify the SQL property of the query object in the QueryDefs collection or (as I do now almost exclusively) use the Execute method of the CurrentDB object with the dbFailOnError argument.

Having said that, and as previously indicated, on those occasions when I do have to modify or add code to this app, I replace any affect queries with context-specific SQL statements in VBA which are then executed by a public procedure which accepts the SQL string as an argument and runs the Execute method of the CurrentDB object.

The entire mystery table issue popped up in the last several months, having previously run for five years or more without these issues. Per saberman's postearlier in this thread, the content of these tables is no longer a mystery; they are the result of an unexpected response to a call to the DB2 database via the DB2 ODBC DSN--in this case, an empty result set.

Regardless of where it occurs, though, the users have reported no error or indication of this failure at runtime. So perhaps the question becomes this: how can I tell whether the mystery tables are created via a query or via an Execute statement in VBA?

Answer 10

Getting into a little bit of touchy country here --- but --- the whole concept of an RDBMS application is to minimize/eliminate redundancy -- whether it's Access or Sql Server/Oracle...  If you are not exploiting this concept --- you are basically on your own.  If something unexplainable is happening in your app --- that is never a good thing.   The only way to achieve explainable results in your app is to use proper development/structure (implement proper coding methods/referential integrity).  If someone is starting out in Application development and develops a basic but working app and then needs to expand this application to a level beyond what that developer is currently proficient at -- the developer should either collaborate with someone who is at the required proficiency level -- or -- hope for the best.

---

Rich P

Answer 11

My application is a relatively minor middleware application used only to import/export data in and out of a huge DB2 database (several hundred tables and currently almost 30 GB in size). I have no control over the structure of that database nor over its front end.

Having said that, and since you claim a superior proficiency level, perhaps you can explain how to configure referential integrity over a DB2 database from within Access.

Your suggestion that only a lack of experience could cause one to forego rewriting an application to use better methods is, unfortunately, a sentiment not shared by the people that pay the bills. I have no problem at all rewriting this entire application so that there are no queries and all SQL statements are executed from within VBA. But, alas, my question was actually about how to resolve one relatively minor anomaly, not about how to justify to management a complete rewrite.

Answer 12

Welcome to my world :).  Having past the touchy part (no offense intended) -- if the proprietors want to continue with an application that is outdated (an app that was not designed in a manner that would make it scalable), that is on them.  If you were to explain that a rewrite would be in order and they decline -- here are your options 1) live with the anomalies  2) if they want you to magically fix the problem and your magic wand isn't working -- there may be some politics there  3) rewrite the thing on your own (which they may be trying to get you to do -- you know -- worker exploitation).

I had an assignment once where some admin person had hacked together an app which was not scalable.  I explained to the management that a rewrite was required -- from scratch -- they declined.  I moved on.

---

Rich P