hi Jim,
Access optimizes the query and saves it. Some of the ones you don't recognize are knocked out by the criteria but not all.
Lots of information might be stored in Flags -- once you figure out why they are there, you can perhaps see a pattern but it might be in a different field too. Much of what I have figured out about the undocumented stuff is by experimentation on what I had to look at it with. I am not looking at your database, so I don't know what has been done ... but ...
Do you have multi-value or attachment fields or other complex data types?
respectfully,
crystal
~ have an awesome day ~
I changed the query to the following SQL below and it worked with the new code. I am still confused what those queries are doing there. they do not show up in the nav pane.
SELECT msysObjects.Name, GetSQL([Name]) AS [SQL], Left([SQL],(InStr(1,[SQL]," ")-1)) AS [Query Type], "Drivers License Database" AS [Database Name], msysObjects.Flags
FROM msysObjects
WHERE (((msysObjects.Name) Not Like '~*') AND ((msysObjects.Flags) Not Like -2147352320 And (msysObjects.Flags) Not Like -2147090176) AND ((msysObjects.type)=5));
Jim Wagner
On Mon Jul 31 2017 09:35:26 GMT-0700 (US Mountain Standard Time), Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Crystal,
So I am really trying to figure this out. All 28 or so databases use the original code and query with no problem. This last database I have created a new database and moved all objects to it, I have done all that has been suggested and still it is not working. But after trying your suggestion of the flag field. I created a new query with just the name and the flag field. I noticed something new. everyone of the queries that have this long weird character string making a duplicate has one interesting characteristic. The flag field has a value of -2147352320 and the query that is the correct one has a value in the flag field as 262144.
Jim Wagner
On Sat Jul 29 2017 07:35:28 GMT-0700 (US Mountain Standard Time), crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
here is a more complete list of the Flags*:
a_QryTypes 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
On 7/29/17 9:14 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:
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 (11) |
Tidak ada komentar:
Posting Komentar