AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Monday, August 31, 2009

How to get rid of ADMIN - 00 tables in database

If you are running pass-through queries in your database – you can suddenly get a lot of ADMIN – 00, ADMIN – 01, etc. tables. Office Online article Create tables from the results of a pass-through query (MDB) explains why this happens:

Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.

Even you set LogMessages property to No – you can get such messages. For example - in one application I am continuously getting message like:

01003 - 8153 - [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.

To get rid of it – you have to find query, which produce this message and fix it. In my case it was a query with SQL:

Select ProductID, Sum(Qty) as Shipped from tblOrder

Qty field had some null values and is was a source of this message. So I fixed it like this:

Select ProductID, Sum(Qty) as Shipped from tblOrder where not Qty is null

Labels: ,

3 Comments:

Blogger Tecnoyokel said...

Hi Alex,

Many thanks for this info. I have just hit this problem, but using a complex forms bound to a SQL tables. The message I get is "01004 - 0 - [Microsoft][ODBC SQL Server Driver]String data, right truncation". I am pretty sure that the code is not running a pass through query anywhere, but I'll keep looking. Any further info would be fantastic! Thanks.

7:34 PM  
Blogger Alex Dybenko said...

Hi,
perhaps queries on linked tables also produce these errors, but also you could have a linked view with aggregate, or table with computed column. You can try to delete tables from your forms on backup mdb to find out which once produces the error. Would be great if you post result of your findings here

7:56 AM  
Blogger Tecnoyokel said...

You are right, it was running a query on a linked SQL table using DAO. I converted the code to use ADODB to fix it. I also seem to be getting the problem on pass-through queries accessing a column containing more than 255 characters of text.

2:12 PM  

Post a Comment

<< Home