title: Access VBA DAO | ADO Bloating Database on MakeTable Query
body: <p>I have a process that updates an Access Database from Oracle data three times a day to get latest information. Current production process involves:</p>
  
  <ul>
  <li>Create New Access Table (t1) of Most Current Data</li>
  <li>Move Previous Version of Data to backup version (t to t2)</li>
  <li>Move Current Data to Main table (t1 to t)</li>
  </ul>
  
  <p>The reason it's done this way is in case the current data fails, users can still access earlier version of data until we can troubleshoot current data or until the next run.</p>
  
  <p>There are many of the processes that we inherited and I am refactoring the process so we can trap and alert errors and stop downstream processes from running when earlier processes fail.</p>
  
  <p>I've developed the following function using DAO so that I can take advantage of <code>Execute</code> to trap errors and bow out of the whole process gracefully. However, this process bloats the database greatly and in all my searching I cannot find a way around this. Most of the research I have done points to clearing out <code>DAO.Recordsets</code> and <code>DAO.QueryDefs</code>, neither of which I deal with in the DDL statements. I created a similar function with ADO as well, but the same issue persists.</p>
  
  <p>Is there any way to clear the temp memory created in Access from this statements after executing so the code can continue without have the DB grow beyond 2GB size limit? Or perhaps it's just better to run the queries with <code>DoCmd.RunSQL</code> and build error trapping with <code>GoTo</code>. I'd like to avoid this but will settle on this if it's the only way.</p>
  
  <p>Function below:</p>
  
  <pre><code>Function ExecuteSQL(db As DAO.Database, sQuery As String) As Boolean
  
  '*******************************************************************
  '**    Sub:     ExecuteSQL
  '**    Purpose: Stores current copy of Daily Eff Date table from Daily Eff Table1 and backs up previous version in Daily Eff Date2
  '**    Notes:   Requires reference to Microsoft DAO 3.6 Object Library (or equivalent)
  '*******************************************************************
  
  Dim wSpace As DAO.Workspace
  Set wSpace = DBEngine.Workspaces(0)
  
  On Error GoTo ErrHandler
  
  With wSpace
  
      .BeginTrans
  
      db.Execute sQuery, dbFailOnError
  
      .CommitTrans
  
      ExecuteSQL = True
  
  End With
  
  LeaveExecuteSQL:
  
      wSpace.Close
      Exit Function
  
  ErrHandler:
  
      wSpace.Rollback
      Resume LeaveExecuteSQL
  
  End Function
  </code></pre>
  
  <p>Here is an example of how Function is called.</p>
  
  <pre><code>If Not ExecuteSQL(CurrentDb, "Daily Sub ALL") Then 'Bring Submission Data into Access
  
      strSubject = "ERROR in Creating The Daily Effective Date Table"
      GoTo LeaveRunProcess
  
  End If
  </code></pre>
  
  <p>Here is SQL for <code>Daily Sub ALL</code>:</p>
  
  <pre><code>SELECT PRODCT_EFF_DT, Left([DWCFEUL5_DEV_SUB_RPT_STATUS_SUBM_ALL_NM]![PRODUCT_SIC_CD],4) AS Expr1, Left([PRODUCT_SIC_CD],4) AS [SIC Short], INS_RQMT_PRODCT_NO, CMPNY_REGN_NM, PROCESSING_REGION, PROCESSING_RGN_NM, CMPNY_CD, CMPNY_NM, PUC_NAME, UW_REGION_NAME, PUC_NO, CLIENT_NAME, CLIENT_NUMBER, ACCOUNT_NUMBER, DUNS_NUMBER, DUNS_PARENT, PRODUCER_NUMBER, PRODUCER_NAME, PRODUCER_CONTACT, PRODCR_CNTCT_PRSN_NO, PRODUCT_TYPE, BRANCH_TYPE, BRANCH_NAME, DEPT_NO, NEW_DEPT_NO, DEPT_CD, DEPT_NM, NEW_DEPT_NM, NEW_PRFT_CENTR_NO, PROFT_CNTR_NM, NEW_PRFT_CENTR_NM, EXP_POLICY_NO, EXPPOLICYNO10, POLICY_NO, POLICYNO10, PRODCT_ATCHMT_PNT_AMT, DED_AMT, LMT_AMT, PRODCT_EXP_DT, QUOTE_BY_DT, PRODCT_DESIRBLTY, NEW_PRODCR_NM, PRODCT_SUCCESS_CHNC, WIN_CARR_NAME, INCUMBENT_INS_CARR, PRODCT_EFF_MONTH, LINE_OF_BUSINESS, PRODCT_NO, PROFIT_CENTER, EXP_PREMIUM, UNDERWRITER_NAME, EMPL_ID, STATE, LAST_UPDT_TS, PREM_AMT, DT_RECEIVED, DT_RESERVED, DT_ASSIGNED, DT_WORKING, DT_QUOTED, DT_BOUND, DT_ISSUED, DT_BOOKED, DT_MAILED, DT_DECLINED, DT_QUOTE_NOT_WRITTEN, CURR_STATUS, CURR_STATUS_CD, CURR_STATUS_CHG_USR, CURR_STATUS_EFF_DT, UW_ASISTANT_NAME, COMPANY_TYPE, CREATE_DT, CREATE_USR, PRM_FINCG_IND, BNKRPCY_STAT_CD, BRKR_MNSCRPT_FORMS_IND, UNDLYG_CNF_WRITN_IND, PRODUCT_SIC_CD, ACCT_SIC, ACCT_SIC_DESC, ACCT_SIC_PCT, PROG_TYP_CD, EXT_REPT_IND, MOT_TRK_LIAB_FIL, MOT_TRK_CRG_FIL, SUBJ_TO_AUDIT, COMP_RATED_IND, CONSENT_TO_RATE, IND_RISK_RATING, NY_FREE_TRD_ZONE, EPOL_DELIVERED, PAYDEX_SCORE, CREDIT_SCORE, FINANCIAL_STRESS_SCORE, YEARS_IN_BUSINESS, DNB_NO, DNB_NAME, DNB_PARENT_NO, DNB_HEADQUARTERS_NO, DNB_ADDRESS_LINE1, DNB_ADDRESS_LINE2, DNB_ZIPCODE, DNB_CITY, DNB_STATE, DNB_COUNTRY_CODE, COMMERCIAL_CREDIT_SCORE, START_YEAR, CURRENT_CONTROL_YEAR, NAICS_CODE, INSRD_NM, PRODCR_LONG_NAME, SIR_AMOUNT, EMAIL_ADDRS_TXT, SUB_PRODUCER_NO, SUB_PRODUCER_CODE, SUB_PRODUCER_NM, SUB_PRODUCER_ADDRESS_LINE1, SUB_PRODUCER_ADDRESS_LINE2, SUB_PRODUCER_ADDRESS_LINE3, SUB_PRODUCER_CITY, SUB_PRODUCER_STATE, SUB_PRODUCER_ZIPCODE, PRODUCER_PHONE_NO, SHOPPING, ASSOC_NO, VIABILITY_SCORE, POLICY_ISSUED_BY, ASSOCIATE_UW, FEIN_N0, PRODUCER_FEIN 
  INTO [Daily Eff Date1]
  FROM DWCFEUL5_DEV_SUB_RPT_STATUS_SUBM_ALL_NM
  WHERE (((PRODCT_EFF_DT)&gt;#1/1/2015#) AND ((NEW_PRFT_CENTR_NM) Not Like "Hawaii"));
  </code></pre>