Fairly involved SQL query - Access deletes...how to recreate using VBA?

[peskywinnets]

So, having experienced a problem, I've established that Access deletes the contents of a complex SQL query, leaving it empty :-( (it happens after ever run of the query)

So I seek a way of recreating the query via VBA, here's the query's loooooong SQL code...

SELECT IIf([BillingCompanyName] Is Null Or [BillingCompanyName]="",Left([ContactName],37) & " " & [Currency],Left([BillingCompanyName],37) & " " & [Currency]) AS CustomerName, Sales.OrderDateTime, IIf([Sales]![Source]="amazon FBA" Or [Sales]![Source]="amazon",[DispatchedDate],[OrderDateTime]) AS Dispatched, Sales.Subsource, IIf([Source]="Amazon" Or [Source]="Amazon FBA" Or [Source]="mywebsite.com",[ExtRef],[LWOrderID]) AS QBMemo, Sales.InvoiceNo, Sales.PaymentMethod, IIf([source]="amazon" Or [source]="amazon fba","amazon",IIf([source] Like "EBAY","ebay",IIf([source] Like "opencart","mywebsite.com'",IIf([source] Like "DIRECT","direct","unknown")))) AS QBClass, IIf([ShippingCountry] Like "Germany","Sales Receipt DE",IIf([ShippingCountry] Like "Austria","Sales Receipt DE",IIf([ShippingCountry] Like "Switzerland","Sales Receipt DE",IIf([ShippingCountry] Like "France","Sales Receipt FR",IIf([ECorNOT] Like "EC","Sales Receipt EC","Sales Receipt nonEC"))))) AS SalesReceiptTemplate, Left([BillingAddress1],41) AS QBBillingAddress1, IIf([BillingAddress2]=[BillingAddress1] Or [BillingAddress2]=[BillingCity],Null,Left([BillingAddress2],41)) AS QBBillingAddress2, IIf([BillingAddress3]=[BillingAddress2] Or [BillingAddress3]=[BillingCity],Null,[BillingAddress3]) AS QBBillingAddress3, Left([BillingCity],31) AS QBBillingCity, IIf([BillingRegion]=[BillingCity],Null,Left([BillingRegion],21)) AS QBBillingRegion, Sales.BillingPostcode, IIf(([BillingCountry]="United Kingdom"),Null,[BillingCountry]) AS QBBillingCountry, IIf([ShippingCompanyName] Is Null Or [ShippingCompanyName]="",[ShippingName],[ShippingCompanyName]) AS QBShippingName, Sales.ShippingAddress1, IIf([ShippingAddress2]=[ShippingAddress1] Or [ShippingAddress2]=[ShippingCity],Null,[ShippingAddress2]) AS QBShippingAddress2, Sales.ShippingCity, IIf([ShippingRegion]=[ShippingCity],Null,[ShippingRegion]) AS QBShippingRegion, Sales.ShippingPostcode, IIf(([ShippingCountry]="United Kingdom"),Null,[ShippingCountry]) AS QBShippingCountry, Sales.Phone, Sales.Email, Left([ContactName],32) AS QBContactName, Sales.SKU, Sales.Qty, Sales.UnitCost, Sales.Currency, Sales.ExchRate, Sales.QBExported, Sales.BillingCompanyName, Sales.PostageCost, Sales.EbayID, Sales.ExtRef, Sales.Source, Sales.ECorNOT, Sales.Discount, Sales.ShippingCompanyName, IIf([BillingCompanyName] Is Null Or [BillingCompanyName]="",[ContactName],[BillingCompanyName]) AS QBBillingName, Sales.LWOrderID, IIf([ECZeroRated]=True,"EGZ",IIf([BillingPostcode] Like "JE*","Z",IIf([BillingPostcode] Like "GY*","Z",IIf([BillingPostcode] Like "IM*","S",IIf([ECorNOT]="nonEC","Z","S"))))) AS VATCode, Sales.InvoiceEmailed, Sales.AmazonPostageChecked, Sales.Email_Later
FROM Sales LEFT JOIN QBSalesReceipt ON CStr(Sales.InvoiceNo) = QBSalesReceipt.doc_num_h
WHERE (((IIf(Sales!Source="amazon FBA" Or Sales!Source="amazon",[DispatchedDate],[OrderDateTime]))<DMin("LatestDisbursementDate","AmazonDisbursementDates")) And ((Sales.QBExported)=False) And ((Sales.ECorNOT)="EC") And ((Sales.AmazonPostageChecked)=True) And ((Sales.Email_Later)=False) And ((QBSalesReceipt.doc_num_h) Is Null))
ORDER BY IIf([Sales]![Source]="amazon FBA" Or [Sales]![Source]="amazon",[DispatchedDate],[OrderDateTime]);

Bearing in mind the length of it, what's the best route here? I found this simple bit of code for recreating a query...

(ref http://www.geeksengine.com/article/access-error-query-destination-field.html)

Dim strQueryName as String
strQueryName = "qry_2ndQuarter"

Set objDB = CurrentDb

If objDB.QueryDefs(strQueryName).SQL = "SELECT *;" & vbCrLf Then

    '' Delete the query 
    objDB.QueryDefs.Delete(strQueryName)

    '' Re-create the query 
    qdef = objDB.CreateQueryDef(strQueryName, "SELECT * FROM Sales WHERE quarter=2") 
End If

'' Output to Excel 
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, "2ndQuarter.xls", True

but dropping my SQL text into where his was, sees the VBA compiler start projectile vomiting!

Any top tips?

 

[pbaldy]

Well, the double quotes in your SQL would have to be changed to singles, otherwise they terminate the string and leave what follows in no-mans-land. That would make the compiler barf.

 

[CJ_London]

I would be more concerned about determining why access deletes the sql. But what you can do is copy the sql text and store in a memo field somewhere - note, change all your double quotes to single quotes. single quotes are a better habit to get into

then a simple bit of vba to retrieve the record and either then create and populate the querydef, or if this is to be displayed in a form just assign it to the form recordsource

I note you are using Sales!Source - the usual notation is Sales.Source although I guess the ! works but you may get issues down the road since ! is used when referencing forms and reports

 

[peskywinnets]

I would be more concerned about determining why access deletes the sql.

Others are experiencing the same problem....

http://stackoverflow.com/questions/23689162/access-erasing-sql-contents-of-query-after-first-run

http://www.geeksengine.com/article/access-error-query-destination-field.html

Re the double quotes ... I had thought a single & double quote had different meanings (& therefore can't be just interchanged at will?). Incidentally, 95% of that SQL was created in Access design view, then I simply switched to SQL view & lobbed in a bit that couldn't be done in design view.

 

[CJ_London]

must admit I've never had the problem myself - but see your first link provides the same solution I suggested in more detail.

I do use this technique quite a bit because I often build queries in vba and store the component parts or template in a table so they can be reused - e.g. a template might look something like

UPDATE {Target} ({FieldList}) VALUES ({ValueList})

That way I don't need to modify code - just substitute the relevant values to the 3 elements with the replace function

 

[CJ_London]

re double/single quotes - access provides double quotes as a default in the query builder, but you can use single quotes - most other sql languages enforce the use of single quotes.

also as already mentioned if you copy/paste some sql code from a query to vba you have to substitute the single quotes otherwise the string is not complete

within vba single and double quotes have different meanings, but not within access sql - except when you are building a string in sql e.g.

prompttext: "Say 'Hello'"

because you need to differentiate between the two - using single quotes you substitute a single quote in the text with two single quotes

prompttext: 'Say ''Hello'''

 

[peskywinnets]

re double/single quotes - access provides double quotes as a default in the query builder, but you can use single quotes - most other sql languages enforce the use of single quotes.

also as already mentioned if you copy/paste some sql code from a query to vba you have to substitute the single quotes otherwise the string is not complete

within vba single and double quotes have different meanings, but not within access sql - except when you are building a string in sql e.g.

prompttext: "Say 'Hello'"

because you need to differentiate between the two - using single quotes you substitute a single quote in the text with two single quotes

prompttext: 'Say ''Hello'''

Many thanks....useful to know

 

[peskywinnets]

I'm a kludger at heart & had a 'workaround...

DoCmd.DeleteObject acQuery, "1_QB_Export_EC"
DoCmd.CopyObject , "1_QB_Export_EC", acQuery, "1_QB_Export_backup"

essentially I'm storing the SQL code in another 'backup' query which never gets touched nor corrupted (& hence always intact) & can therefore be copied to recreate the needed query afresh each time.