Sabtu, 29 Juli 2017

Re: [MS_AccessPros] Strange Errors when running query with a public function

 

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

qry6To7Points
qry6To7Points_F31DBFE61EC14C9CBA6BB5A50544BA69

qry8OrMorePoints
qry8OrMorePoints_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: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar