Run SQL based on SQL result

[ajamess]

I have a solution I previously used to import csv's from a folder based on a saved spec.

DoCmd.TransferText TransferType:=acLinkDelim, _
                   SpecificationName:="samplelink", _
                   TableName:="Templink", _
                   FileName:=strPathFile, _
                   HasFieldNames:=True

DoCmd.RunSQL ("SELECT *, Date() as LOAD_DATE INTO mytable FROM Templink WHERE ...")

I've used a lot of iterations of this, and never had an issue.


Now I need to run that SQL import query, based on a SQL result from it. So if one column has all blanks I want to run one import query, vs another one if they aren't all blanks.

I found this below on stackoverflow (I can't link I guess, but can search "can i have a docmd method in if statement") But tried to use what they said like this:

DoCmd.TransferText TransferType:=acLinkDelim, _
                   SpecificationName:="samplelink", _
                   TableName:="Templink", _
                   FileName:=strPathFile, _
                   HasFieldNames:=True

rec = CurrentDB.OpenRecordset("Select max(mycolumn) from Templink")


if rec(0) = "" then
DoCmd.RunSQL (Query1)
else
DoCmd.RunSQL (Query2)
end if

I've debug printed out the Rec(0) and it seems to work. However, anytime I add this in, I get an error that the specification doesn't exist. I think something with the openrecordset must be messing with the import later. If I comment that out, as well as the if's then either query1 or query2 work smoothly.

What is the best way to approach this? Thanks

 

[cheekybuddha]

Perhaps something like:

DoCmd.TransferText TransferType:=acLinkDelim, _
                   SpecificationName:="samplelink", _
                   TableName:="Templink", _
                   FileName:=strPathFile, _
                   HasFieldNames:=True

if DCount("*", "Templink", "mycolumn & '' <> ''") > 0 then
' mycolumn not empty
  DoCmd.RunSQL (Query1)
else
' mycolumn is empty'
  DoCmd.RunSQL (Query2)
end if

 

[arnelgp]

you may also try:

DoCmd.TransferText TransferType:=acLinkDelim, _
                   SpecificationName:="samplelink", _
                   TableName:="Templink", _
                   FileName:=strPathFile, _
                   HasFieldNames:=True

Dim nRecords As Long, nNulls As Long

nRecords = DCount("*", "TempLink")
nNulls = DCount("*", "TempLinks", "MyColumn Is Null")

if nRecords = nNulls then
'all records are Null on 1 field
DoCmd.RunSQL (Query1)
else
DoCmd.RunSQL (Query2)
end if