Running a query in code

[Michael Walker]

Hi

How do I run a query (ie. append, select, delete) in code, without having to go through the yes no message box prompts?

Thanks Michael

 

[Andrew Fergus]

Hi Michael
Try using the SetWarnings command, I think the code is
DoCmd.SetWarnings 0
to turn them off and
DoCmd.SetWarnings -1
to turn them back on. This will prvent modal warnings & message boxes from popping up but will not stop error messages.
HTH, Andrew.

 

[jag108]

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=esmr.airnz.co.nz;UID=your ID/password;DBQ=Databse IP;ASY=OFF;", Destination:=Range("A1"))
.Sql = Array("SELECT SW_SERVICE_ORDER.SWSERVICEORDERID, SW_SERVICE_ORDER.AIRPARENTOBJECTID, SW_SERVICE_ORDER.SWNOTE, SW_SERVICE_ORDER.SWSTATUS," _
, " SW_SERVICE_ORDER.AIRBUSINESSIMPACT, SW_SERVICE_ORDER.AIRCUSTOMERIMPACT, SW_PERSON.AIRFULLNAME, SW_SERVICE_ORDER.SWDATECREATED" & Chr(13) & "" & Chr(10) & "FROM ESM.SW_PERSON SW_PERSON, ESM.SW_PROVIDER_GRP SW_PROVIDER_GRP, ESM.S" _
, "W_SERVICE_ORDER SW_SERVICE_ORDER" & Chr(13) & "" & Chr(10) & "WHERE SW_SERVICE_ORDER.SWREPORTEDBY = SW_PERSON.SWPERSONID AND SW_SERVICE_ORDER.SWPROVIDERGRPID = SW_PROVIDER_GRP.SWPROVIDERGRPID AND ((SW_SERVICE_ORDER.SWPRODRELEASE" _
, "ID In (3357,5020)) AND (SW_SERVICE_ORDER.SWSTATUS In ('Accepted','Assigned','In Progress','Logged','On Hold','On Monitor')) AND (SW_PROVIDER_GRP.SWNAME In ('Intl Ops - Logistics Support')))" & Chr(13) & "" _
, "" & Chr(10) & "ORDER BY SW_SERVICE_ORDER.SWDATECREATED")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
Format_sheet

 

[Norie]

jag108

Did you mean to post this in the Access forum?

Perhaps it's more suitable for the Excel forum.

And if that's right perhaps you could tell us what problem you have.