Append using ADO

Public Function RefreshLocalTmpTbl() As Boolean
On Error GoTo Err_RefreshLocalTmpTbl

  Dim strSQLbe As String
  Dim strSQLfe As String

  'Check if we are suppose to refresh the FE Temp Table
  If flgRefreshFETempTable = False Then
    RefreshLocalTmpTbl = True
    GoTo Exit_RefreshLocalTmpTbl
  End If

  'LOC for Pass-Through query defined in Access VBA code
  [COLOR=Red][B]'Select one or the other query for strSQLbe variable!!![/B][/COLOR]
  strSQLbe = "SELECT [p].[id],[p].[authid],[p].[logtimestamp],[p].[title] " & vbCrLf & _
             "FROM [dbo].[projects] AS [p] " & vbCrLf & _
             "ORDER BY [p].[id];"

  'LOC for Pass-Through query to execute a Stored Procedure query
  [COLOR=Red][B]'Select one or the other query for strSQLbe variable!!![/B][/COLOR]
  strSQLbe = "SET NOCOUNT ON;" & vbCrLf & _
             "DECLARE @id smallint;" & vbCrLf & _
             "SET @id = " & intRecordID & ";" & vbCrLf & _
             "EXEC dbo.clsObjProjectsTbl_RefreshLocalTmpTbl_1ea @id;"


  strSQLfe = "INSERT INTO tmptblqry_projects (id,authid,logtimestamp,title) " & vbCrLf & _
             "SELECT t.id,t.authid,t.logtimestamp,t.title " & vbCrLf & _
             "FROM"

  'Call the shared API and return its return code
  RefreshLocalTmpTbl = dbutils_RefreshLocalTmpTbl("clsObjProjectsTbl_RefreshLocalTmpTbl", strSQLbe, strSQLfe)

  'If that call got a good return code, then set the flag that the FE Temp Table is now valid
  If RefreshLocalTmpTbl = True Then
    flgRefreshFETempTable = False
  End If

Exit_RefreshLocalTmpTbl:
  Exit Function

Err_RefreshLocalTmpTbl:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: RefreshLocalTmpTbl()")
  RefreshLocalTmpTbl = False
  Resume Exit_RefreshLocalTmpTbl

End Function

'This API populates the FE temp table via a query of the BE database
Public Function dbutils_RefreshLocalTmpTbl(ByVal strQueryAPIName As String, ByVal strSQLbe As String, ByVal strSQLfe As String) As Boolean
On Error GoTo Err_dbutils_RefreshLocalTmpTbl

  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameBE As String
  Dim strQryNameFE As String

  'Define the name for the BE query
  strQryNameBE = "vbaqry" & strQueryAPIName & "_PT"

  'Define the name for the FE query
  strQryNameFE = "vbaqry" & strQueryAPIName

  'Make sure they do not exist, delete the queries we need to build
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Build the BE PT Query
  Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
  With daoQDFbe
    .Connect = ObjAppSettings.ODBCConnectString()
    .SQL = strSQLbe
    .Close
  End With

  'Append the name of the BE query onto the FE SQL to complete the FROM clause
  strSQLfe = strSQLfe & " " & strQryNameBE & " AS t;"

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute [COLOR=Blue][B]dbFailOnError[/B]
[/COLOR]     .Close
  End With

  'Good return code
  dbutils_RefreshLocalTmpTbl = True

Exit_dbutils_RefreshLocalTmpTbl:
  'Delete the queries we just built
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Clean up the connection to the database
  Set daoDB = Nothing
  Set daoQDFbe = Nothing
  Set daoQDFfe = Nothing

  Exit Function

Err_dbutils_RefreshLocalTmpTbl:
  Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()")
  dbutils_RefreshLocalTmpTbl = False
  Resume Exit_dbutils_RefreshLocalTmpTbl

End Function