Put querydef with parameters result into table

[bobmac-]

Hi,

I have a querydef with one input parameter.

Set myDb = CurrentDb()
Set rs = Nothing
Set qdf = myDb.QueryDefs("qryAssetDetails_lessBank")

qdf.Parameters(0) = Me.cboChannel

strName = "tblTempRS"
strIn = qdf.SQL
strOut = Replace(strIn, "From", "INTO " & strName & " FROM")
Debug.Print strOut

qdf.Execute strOut

The execute gives a 'data type conversion error' (3421). The SQL executes OK in Access query window.
Any ideas?

SQL:

SELECT dbo_ASSET.ASSET_ID, dbo_ASSET_COMPONENT.ASSET_COMPONENT_ID, dbo_ASSET.ASSET_CODE, dbo_ASSET.RUNNING_DISTANCE, "" AS Bank, dbo_COMPONENT_TYPE.COMPONENT_TYPE_DESC, dbo_CONDITION.RATING, dbo_ASSET.OWNERSHIP, dbo_ASSET.NUMBER_ON_PLAN
INTO tblTempRS FROM ((dbo_CONDITION INNER JOIN (((dbo_ASSET INNER JOIN dbo_ASSET_ASSET_GROUP ON dbo_ASSET.ASSET_ID=dbo_ASSET_ASSET_GROUP.ASSET_ID) INNER JOIN dbo_ASSET_GROUP ON dbo_ASSET_ASSET_GROUP.ASSET_GROUP_ID=dbo_ASSET_GROUP.ASSET_GROUP_ID) INNER JOIN dbo_ASSET_COMPONENT ON dbo_ASSET.ASSET_ID=dbo_ASSET_COMPONENT.ASSET_ID) ON (dbo_CONDITION.ASSET_COMPONENT_ID=dbo_ASSET_COMPONENT.ASSET_COMPONENT_ID) AND (dbo_CONDITION.ASSET_ID=dbo_ASSET_COMPONENT.ASSET_ID)) INNER JOIN dbo_ASSET_TYPE ON dbo_ASSET.ASSET_TYPE_CODE=dbo_ASSET_TYPE.ASSET_TYPE_CODE) INNER JOIN dbo_COMPONENT_TYPE ON dbo_ASSET_COMPONENT.COMPONENT_TYPE_CODE=dbo_COMPONENT_TYPE.COMPONENT_TYPE_CODE
WHERE (((dbo_ASSET.ASSET_NAME)=Forms!frmAssetLifeMain!cboChannel) And ((dbo_ASSET.ASSET_TYPE_CODE)="ST" Or (dbo_ASSET.ASSET_TYPE_CODE)="CH") And ((dbo_ASSET_GROUP.SHORT_NAME)="MID Irrigation"))
ORDER BY dbo_ASSET.ASSET_NAME, dbo_ASSET.RUNNING_DISTANCE;SELECT dbo_ASSET.ASSET_ID, dbo_ASSET_COMPONENT.ASSET_COMPONENT_ID, dbo_ASSET.ASSET_CODE, dbo_ASSET.RUNNING_DISTANCE, "" AS Bank, dbo_COMPONENT_TYPE.COMPONENT_TYPE_DESC, dbo_CONDITION.RATING, dbo_ASSET.OWNERSHIP, dbo_ASSET.NUMBER_ON_PLAN
INTO tblTempRS FROM ((dbo_CONDITION INNER JOIN (((dbo_ASSET INNER JOIN dbo_ASSET_ASSET_GROUP ON dbo_ASSET.ASSET_ID=dbo_ASSET_ASSET_GROUP.ASSET_ID) INNER JOIN dbo_ASSET_GROUP ON dbo_ASSET_ASSET_GROUP.ASSET_GROUP_ID=dbo_ASSET_GROUP.ASSET_GROUP_ID) INNER JOIN dbo_ASSET_COMPONENT ON dbo_ASSET.ASSET_ID=dbo_ASSET_COMPONENT.ASSET_ID) ON (dbo_CONDITION.ASSET_COMPONENT_ID=dbo_ASSET_COMPONENT.ASSET_COMPONENT_ID) AND (dbo_CONDITION.ASSET_ID=dbo_ASSET_COMPONENT.ASSET_ID)) INNER JOIN dbo_ASSET_TYPE ON dbo_ASSET.ASSET_TYPE_CODE=dbo_ASSET_TYPE.ASSET_TYPE_CODE) INNER JOIN dbo_COMPONENT_TYPE ON dbo_ASSET_COMPONENT.COMPONENT_TYPE_CODE=dbo_COMPONENT_TYPE.COMPONENT_TYPE_CODE
WHERE (((dbo_ASSET.ASSET_NAME)=Forms!frmAssetLifeMain!cboChannel) And ((dbo_ASSET.ASSET_TYPE_CODE)="ST" Or (dbo_ASSET.ASSET_TYPE_CODE)="CH") And ((dbo_ASSET_GROUP.SHORT_NAME)="MID Irrigation"))
ORDER BY dbo_ASSET.ASSET_NAME, dbo_ASSET.RUNNING_DISTANCE;

 

[vbaInet]

Is this an ADP?

I haven't scrutinised the whole sql statement but I can spot a few errors:

dbo_ASSET.RUNNING_DISTANCE, "" AS Bank - did you want dbo_ASSET.RUNNING_DISTANCE & "" AS Bank?
(dbo_ASSET.ASSET_NAME)=Forms!frmAssetLifeMain!cb oChannel)

When you copy the sql from a qdf it may leave some unwanted characters. You will need to strip it off before performing the Replace():

    dim rSQL as string
      
    strIn = qdf.SQL

    rSQL = right(strIn, 1)

    do while rSQL = Chr(10) Or rSQL = vbCr Or rSQL = " " Or rSQL = ";"
        strIn = left(strIn, len(strIn) - 1)

        rSQL = right(strIn, 1)
    loop
      
    strOut = Replace(strIn, "From", "INTO " & strName & " FROM")

 

[bobmac-]

Hi

Thanks for that.

The two errors you pointed out were actually OK. The , "" Bank is a blank column. The gap in the form control I have no idea how that got there as I used 'Build' to input the control.

With the help of your code and making sure the original query isn't already 'make table' etc, the following works fine

strName = "tblTempRS"
strChannel = Forms!frmAssetLifeMain!cboChannel
strIn = qdf.SQL
rSQL = Right(strIn, 1)
Do While rSQL = Chr(10) Or rSQL = vbCr Or rSQL = " " Or rSQL = ";"
strIn = Left(strIn, Len(strIn) - 1)
rSQL = Right(strIn, 1)
Loop
Debug.Print strIn
strOut = Replace(strIn, "[Forms]![frmAssetLifeMain]![cboChannel]", "'" & strChannel & "'")
strOut = Replace(strOut, "From", " INTO " & strName & " FROM")
myDb.Execute strOut

Cheers

 

[vbaInet]

Pleased to hear bobmac.