 Re: Dynamic Pass Through Query -
08-08-2011
, 05:17 PM
Check to see if the Query exists -- if it does, simply replace its SQL
property; if not create as you do now.
You can reference the query with error-trapping enabled and handle the
error, or if that doesn't work, check to see if the Query's SQL property is
a Null String.
--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access
"mcolson" <mcolson1590 (AT) gmail (DOT) com> wrote
I have a pass through query I created. It creates the QueryDef just fine,
but if it already exists, it errors out. My query is going to be dynamic,
so I'd like to overwrite it, or just delete it when I'm done. I'm having
problems though recognizing if the table exists, or using
mydatabase.TableDefs.Delete "Query name". When I try to delete the query I
get a Run-time error '3265': Item not found in this collection. The table
exists function works for other tables I have created, but not the one
created via the pass-through query. Can someone help? I've included the
functions below.
Function CreateSPT(SPTQueryName As String, SQLString As String, _
ConnectString As String)
'-----------------------------------------------
' FUNCTION: CreateSPT()
' PURPOSE:
' Creates an SQL pass-through query using the supplied arguments:
' SPTQueryName: the name of the query to create
' SQLString: the query's SQL string
' ConnectString: the ODBC connect string, this must be at
' least "ODBC;"
'-----------------------------------------------
Dim mydatabase As Database
Dim myquerydef As QueryDef
Set mydatabase = DBEngine.Workspaces(0).Databases(0)
Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)
mydatabase.TableDefs.Delete SPTQueryName
' myquerydef.Connect = ConnectString
' myquerydef.SQL = SQLString
' myquerydef.Close
'
' If TableExists(SPTQueryName) Then
' MsgBox "Table " & SPTQueryName & " Exists", vbInformation
' Else
' MsgBox "Table " & SPTQueryName & " Does Not Exist", vbExclamation
' End If
End Function
Function CreateQuery()
Dim stServer As String
Dim stDatabase As String
Dim stUsername As String
Dim stPassword As String
Dim stQuery As String
Dim stConnect As String
stServer = "PRASCADA2"
stDatabase = "ChartMES"
stUsername = "PraQC"
stPassword = "Quality"
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" &
stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
stQuery = "SELECT DateTime, SUBSTRING(TagName, 14, { fn
LENGTH(TagName) } - 13) AS Location, ROUND(Value, 3) AS Pressure " & _
"FROM aaReports.dbo.History " & _
"WHERE (DateTime >= CONVERT(DATETIME, '2011-07-22 16:00:00', 102))
AND (wwRetrievalMode = 'Cyclic') AND (wwResolution = 300000) AND " & _
"(TagName IN ('Vacuum_North.Rough_SystemPressure',
'Vacuum_North.HiVac1_SystemPressure', 'Vacuum_North.Leg1_Pressure', " & _
"'Vacuum_North.Leg2_Pressure', 'Vacuum_North.Leg3_Pressure',
'Vacuum_North.Leg4_Pressure', 'Vacuum_North.Leg5_Pressure', " & _
"'Vacuum_North.Leg6_Pressure'))"
Call CreateSPT("GP_BatchDatab", stQuery, stConnect)
End Function
Function TableExists(Name As String) As Boolean
Dim objTable As TableDef
Dim db As Database
Set db = CurrentDb
On Error Resume Next
Set objTable = db.TableDefs(Name)
TableExists = Not objTable Is Nothing
Exit Function
End Function
 |
Linear Mode
