FROM msysObjects
WHERE (((msysObjects.Name) Not Like '~*') AND ((msysObjects.Flags) Not Like -2147352320 And (msysObjects.Flags) Not Like -2147090176) AND ((msysObjects.type)=5));
here is a more complete list of the Flags*:
| QryTypI | QTyDescr | VbaConst |
|---|---|---|
| 0 | Select query | dbQSelect |
| 16 | Crosstab query | dbQCrosstab |
| 32 | Delete query | dbQDelete |
| 48 | Update query | dbQUpdate |
| 64 | Append query | dbQAppend |
| 80 | Make-table query | dbQMakeTable |
| 96 | DDL, Data-definition language query | dbQDDL |
| 112 | Pass-through query | dbQSQLPassThrough |
| 128 | Union (Set operation) query | dbQSetOperation |
| 144 | Bulk operation query | dbQSPTBulk |
| 160 | Compound query | dbQCompound |
| 224 | Stored procedure | dbQProcedure |
| 240 | Action query | dbQAction |
... I am guessing that 8 (not in list above) is Hidden. Not sure if Flags uses bit-math.
* this table came from the Analyzer, which is a free download and now on GitHub:
https://github.com/strive4peace/Analyzer
~crystal
Hi Jim,
perhaps the problem is recurrent use of CurrentDb ... perhaps try:
Option Compare Database
Option Explicit
Dim m_db As dao.Database
Public Function GetSQL(strQueryName As String) as string
's4p
If m_db Is Nothing Then
Set m_db = CurrentDb
end if
GetSQL = m_db.QueryDefs(strQueryName).SQL & ""
End Function
public sub ReleaseTheDatabase
Set m_db = Nothing
end sub
~~~~
then when the query is done, run ReleaseTheDatabase
I changed the return to a string because if Null is returned, your QueryType equation will have an error with Left -- maybe change to Left([SQL] & " " ...
However, maybe you want to get the query type another way?
use the Flags field of MSysObjects to determine query type
for instance:
0 = Select
32 = Delete
48 = Update
64 = Append
80 = MakeTable
128 = Union
other bits:
8 = Hidden?
respectfully,
crystal
~ have an awesome day ~
On 7/28/17 11:00 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:
Jim,
The msysobjects table isn't documented by Microsoft although many of us have used it. There could be near duplicates from internal processes that we have no idea about.
I'm not sure what you mean by "How does a query run but have errors?"
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, July 28, 2017 3:57 PM
To: Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Strange Errors when running query with a public function
Duane,
In database 2 I figured out why the error comes up. But confused at why it gives the error. Apparently there is a macro and it runs a series of queries. 2 of the queries are the ones with ambiguous joins. Although they run in the macro and alone. But will not let me open the queries in design view because of the ambiguous join error. How does a query run but have errors?
I changed the joins and they worked for the second database.
But the first database I am getting a list of the queries but they are being duplicated like the following
qry6To7Pointsqry6To7Points_F31DBFE61EC14C9CBA6BB5A50544BA69
qry8OrMorePointsqry8OrMorePoints_951765DA04F142C6BB3DF1CE020234EC
Jim Wagner
On Fri Jul 28 2017 09:43:29 GMT-0700 (US Mountain Standard Time), Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Duane,
I did as you instructed for both databases and received the same errors respectively for both databases.
Could the issue actually be the query syntax of each of the queries?
Is there another sql statement that I do not need the module?
Jim Wagner
On Fri Jul 28 2017 07:43:31 GMT-0700 (US Mountain Standard Time), Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Jim,
This sounds like some form of corruption. I would make a backup of the database and then Repair and Compact. If the errors still occur, create a new database and import all of the objects into it.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, July 28, 2017 9:03 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Strange Errors when running query with a public function
Hello all,
This morning I have come across 2 strange errors that only happens on 2 different databases for a process that is on 20 other databases and work correctly.
I am not sure why these two databases are causing the issues.
I would appreciate any assistance with this crazy problem.
Thank You
Jim Wagner
I have a public function
Option Compare Database
'SQL Database statements
Public Function GetSQL(strQueryName As String)
'GetSQL = CurrentDb.QueryDefs(strQueryName).SQL
End Function
Then a query
SELECT msysObjects.Name, GetSQL([Name]) AS [SQL], Left([SQL],(InStr(1,[SQL]," ")-1)) AS [Query Type], "Drivers License Database" AS [Database Name]
FROM msysObjects
WHERE (((msysObjects.Name) Not Like '~*') AND ((msysObjects.type)=5));
Database 1
The error for this database
is a run-time error 3265
Item not found in this collection.
_________________________________________
Database 2
The next database has the same public function and the same query
but the error is different
it is run-time error '3258'
The SQL statement could not be executed because it contains ambiguous joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include the query in your SQL statement.
It does not let me out of the error.
Posted by: Jim Wagner <luvmymelody@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar