Access Pass Through Queries and Admin Tables, Win Vista/7

[Tim Marshall]

I've changed this drastically as I'd thought I'd posted a solution, but I was wrong. Now I'm looking for help!

I write Aceess 2003 front ends against an Oracle database using pass through queries (PTQ) instead of linked tables. I was modifying an application report I came upon Admin - 00 type tables that were mysteriously appearing in my table tab. These would appear whenever my code called upon some PTQs or when I ran those particular PTQs from the query tab.

The above issue would only occur when the machine running Access is a Win Vista or Win 7. It does not happen XP Machines (mine blew-up Monday 8) which is why I am using my employees' Win 7 and Vista machines where I discovered the issue).

The advice Keith gives in this topic does not work:
http://bytes.com/topic/access/answer...8-admin-tables

In summary, the very reasonable solution from Keith was to ensure that the log messages property of the PTQ was set to no. Unfortunately, in all of my PTQs, which are generated by my code (I can post the VBA proc I've been using since about 2002, if anyone is interested), log messages were set to no.

So the solution does not solve my problem of Admin - 00 type tables appearing everytime I run a PTQ.

I looked to see if the intelli-sense would show me any DAO query property which indicated log messages. Just to explicitly state it would be no. But I couldn't find anything scrolling through the pop-up properties of a DAO.querydef object.

Does anyone have another solution?
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Butters?" - Ditto

[NeoPa]

I'm trolling back through a number of years' memory here, but I believe there might be two situations where Admin files are created. One is the logging of errors encountered, but the other is the storing of messages explicitly displayed by the SQL routine itself.

If I'm correct, then the contents of the table will give clues as to what's happening.

Unfortunately, I no longer deal with any of the databases or servers I was working on at that time so I can't even check for you to confirm my recollection is on track. See what you can see though.

[nico5038]

Hmm, would start with trying to prevent the creation of these warnings.
They point to an error in a query, so why not solve that to stop this table creation ?

Nic;o)

[Tim Marshall]

Hi everyone, it's two+ years later, an absence from work and job change and I'm back at this application now. My apologies to respondents and especially to people with similar issues coming across this topic item via a search engine for inconsiderately leaving this so late - laziness on my part and stupidity for losing track of the myriad forums in which I lurk or participate.

The "Admin" part of these tables is explained at the bottom of the following link for A2007:

http://office.microsoft.com/en-ca/ac...010206488.aspx

I have confirmed this is a Win 7 problem that id not exist in Win XP. There is no issue with respect to the SQL, Oracle, via Pass through query, or Jet. I have confirmed that it happens when an Oracle varchar2(2000) field from the pass through query is inserted (append) into a a jet table. The message in the Admin table, under the single field called ODBC, is:

01004 - 0 - [Oracle][ODBC]String data, right truncated.

The creation of the table does not occur when the SQL (constructed in VBA) omits the varchar2(2000) field. There are many other varchar2 fields, ranging in size from 3 to 100.

The data in the pass through query and the Jet table match exactly and there is no data in any row or column in either that does not match the other.

So this tells me that the Oracle server, via the ODBC driver, is sending me messages about what has been done to the data, but thee messages are not errors.

So, so far, no solution other than the following proc to be run in an appropriate place such as the procedure that creates the mentioned Oracle query and inserts results into a local table:

Public Sub sAdminOracleTablesRemove()
'Goes through tabledefs abd deletes Admin tables if they can be deleted.
 
    Dim tb As DAO.TableDef
 
    On Error GoTo Err_Proc
 
    dbLocal.TableDefs.Refresh
 
    For Each tb In dbLocal.TableDefs
        If Left(tb.Name, 7) = "Admin -" Then
            Debug.Print tb.Name
            DoCmd.Close acTable, tb.Name, acSaveNo
            DoCmd.DeleteObject acTable, tb.Name
        End If
    Next tb
 
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 3211 'In use.
            'When the table(s) is created it is in use for the whole session and can't be deleted
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, _
                "sAdminOracleTablesRemove", Err.HelpFile, Err.HelpContext
            Resume Exit_Proc
    End Select
End Sub

[NeoPa]

Nice post Tim. Essentially though, the table is created by Access automatically when any feedback is provided by the back end system. This makes sense when you consider that there is not any more appropriate way to allow this information to get back to the user as the request is between two automated processes (One of which being the Oracle, or other SQL, server).

The particular message you are encountering may be unique to Win7, but the concept of handling messages that way is standard behaviour for Access.