C
ciqala
Guest
i am building some reports that use a module to generate sql pass through queries to retrieve their data.
when the report is being generated i am checking to see if the query already exists and if so the user is prompted to see if they want to remove the existing query and create the new one. if the user says yes then i delete the existing query however at this point i am having trouble.
the query is removed from the query panel on the database window but when i try to re-create the query, my check to see if it already exists still thinks its there but when i try to delete it using my delete function again it says it no longer exists. and it just goes around and around.
this remains until i close access and re-open it by which time the deleted query no longer appears when i run my check and i am able to create it again.
however its not feasible to expect my users to close access and re-open everytime they run this report (actually a suite of reports that all use the same mechanism)
access version is 2003
has anybody else here encountered similiar problems or perhaps know of something i might try to fix this.
here is the code of the functions i'm using.
when the report is being generated i am checking to see if the query already exists and if so the user is prompted to see if they want to remove the existing query and create the new one. if the user says yes then i delete the existing query however at this point i am having trouble.
the query is removed from the query panel on the database window but when i try to re-create the query, my check to see if it already exists still thinks its there but when i try to delete it using my delete function again it says it no longer exists. and it just goes around and around.
this remains until i close access and re-open it by which time the deleted query no longer appears when i run my check and i am able to create it again.
however its not feasible to expect my users to close access and re-open everytime they run this report (actually a suite of reports that all use the same mechanism)
access version is 2003
has anybody else here encountered similiar problems or perhaps know of something i might try to fix this.
here is the code of the functions i'm using.
Code:
Function CreateQuery(QueryName As String, SQLString As String, ConnectType As String) As String
'-----------------------------------------------
' FUNCTION: CreateQuery()
' AUTHOR: Ciqala Burt
' DATE: 23rd August 2005
' PURPOSE:
' Creates an SQL query using the supplied arguments:
' ARGUMENTS:
' QueryName: the query to be created
' SQLString: the query's SQL string
' ConnectType: the ODBC connection type (Optional)
' "" - None (use local tables) [default]
' "CODA" - Use the CODA DSN
' "ASOC" - Use the Asoc DSN
' "OBS" - Use the OBS DSN
' "SUNRISE" - Use the SUNRISE DSN
' RETURNS:
' CreateQuery - Holds the name of the query we created
' NOTE: If this name is null then an error occurred
'-----------------------------------------------
Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
Dim buttonVal As String
Dim exists As Boolean, retry As Boolean
If IsNull(SQLString) Then 'no SQLString so error out
MsgBox "An error has occurred, please notify the Development Team " _
& "with the Following Information..." & vbCrLf _
& "Form: " & Str(Screen.ActiveForm) & vbCrLf _
& "Error: " & "No Sql supplied.", vbOKOnly, "Error"
CreateQuery = "" 'return a blank queryname
Exit Function
End If
'get a random name for our query
'QueryName = RandomName("qry")
'set retry = true for our first pass
retry = True
Do Until retry = False
retry = False
'see if this query exists
exists = ExistsQuery(QueryName)
If exists = False Then
'select the current access database to store query
Set mydatabase = DBEngine.Workspaces(0).Databases(0)
'create our new query using our random name and place into an object
Set myquerydef = mydatabase.CreateQueryDef(QueryName)
'retrieve our connection string based on the ConnectType variable
ConnectString = GetDns(ConnectType)
'set the connection string in our query
myquerydef.Connect = ConnectString
'set the SQL string in our query
myquerydef.SQL = SQLString
'set the query to return records
myquerydef.ReturnsRecords = True
'the query is now created so we can close the query object
myquerydef.Close
Set myquerydef = Nothing
'return the query name to whatever called this function
CreateQuery = QueryName
Else
buttonVal = MsgBox("This report is currently locked by another user." & vbCrLf & vbCrLf & _
"To continue please choose one of the following options..." & vbCrLf & vbCrLf & _
"Abort - close the report so you can try again later " & vbCrLf & _
"Retry - try to open the report again and see if the other user has now finished " & vbCrLf & _
"Ignore - Override the lock and open the report (warning: this may affect the other users work!) " _
, vbAbortRetryIgnore + vbDefaultButton1 + vbInformation, "Warning!")
Select Case buttonVal
Case vbAbort 'cancel running report
CreateQuery = ""
Exit Function
Case vbRetry 'retry
retry = True
Case vbIgnore
'delete existing query and retry
DeleteQuery QueryName
RefreshDatabaseWindow
retry = True
End Select
End If
Loop
End Function
Function DeleteQuery(QueryName As String)
'-----------------------------------------------
' FUNCTION: DeleteQuery()
' AUTHOR: Ciqala Burt
' DATE: 23rd August 2005
' PURPOSE:
' Deletes a query based on the name specified
' ARGUMENTS:
' QueryName - Specifies which query to delete
' RETURNS:
' None
'-----------------------------------------------
Dim mydatabase As DAO.Database
If IsNull(QueryName) Then
'do nothing
Exit Function
Else
' Trap for any errors.
'On Error Resume Next
'delete query
If ExistsQuery(QueryName) = True Then
'Set mydatabase = DBEngine.Workspaces(0).Databases(0)
'mydatabase.QueryDefs.Delete QueryName
DoCmd.DeleteObject acQuery, QueryName
End If
End If
End Function
Function ExistsQuery(QueryName As String) As Boolean
'-----------------------------------------------
' FUNCTION: ExistsQuery()
' AUTHOR: Ciqala Burt
' DATE: 24th August 2005
' PURPOSE:
' Checks to see if a query already exists based on the name specified
' ARGUMENTS:
' QueryName - Specifies which query to look for
' RETURNS:
' ExistsQuery - Boolean specifying if query was found or not
' "TRUE" - Query exists
' "FALES" - Query does not exist
'-----------------------------------------------
Dim mydatabase As DAO.Database
Dim test As String
Dim found As Boolean
Const NAME_NOT_IN_COLLECTION = 3265
Const JET_DB_MISSING = 3011
'default found to False
found = False
' Trap for any errors.
On Error Resume Next
' If queryName is null exit function
If IsNull(QueryName) Then
ExistsQuery = False
Exit Function
Else
Set mydatabase = DBEngine.Workspaces(0).Databases(0)
test = mydatabase.QueryDefs(QueryName).name
If Err <> NAME_NOT_IN_COLLECTION Then
found = True
Else
found = False
End If
mydatabase.Close
End If
ExistsQuery = found
End Function
