asked on

Need to Populate a MS Access Table via VBA Code

I have this MS Access Query that I would like to populate a table with via VBA Code, but not sure how to go about it.

It needs to be an Insert Into query because I'm deleting the contents of the table first, and then need to populate the table again with this query (so refresh the data each time the Form loads).

I want this query to be executed when the form (frmMainWells) Loads.

SELECT
  MAIN_WELL_DATA.APD_VALIDFROM_MIN,
  Format([APD_VALIDFROM_MIN],'mm/dd/yy') AS ValidFrom,
  MAIN_WELL_DATA.WINS_NO,
  dbo_VW_PRM_DRL_CMP_TOTS_SCHEDULE_PUB_WELL_PIVOT.WINS,
  MAIN_WELL_DATA.API_UWI_NO,
  dbo_VW_PRM_DRL_CMP_TOTS_SCHEDULE_PUB_WELL_PIVOT.API,
  MAIN_WELL_DATA.WELL_NAME,
  dbo_VW_PRM_DRL_CMP_TOTS_SCHEDULE_PUB_WELL_PIVOT.START1_FRAC AS FRAC_SCHED_DATE,
  Format([FRAC_SCHED_DATE],'mm/dd/yy') AS FracSchedDate,
  MAIN_WELL_DATA.STATUS_ID,
  MAIN_WELL_DATA.PERMITS_FOLDER,
  GetCoaWellCount([WINS_NO]) AS COA_COUNT,
  MAIN_WELL_DATA.FRAC_ACT_DATE,
  Format([FRAC_ACT_DATE],'mm/dd/yy') AS FracActDate,
  MAIN_WELL_DATA.FRAC_STATUS_ID,
  MAIN_WELL_DATA.FRAC_DAYS_LEFT,
  LU_MAIN_WELL_STATUS.MAIN_WELL_STATUS_DESCR,
  LU_FRAC_STATUS.FRAC_STATUS_DESCR,
  MAIN_WELL_DATA.IS_ABANDONED,
  IIf([IS_ABANDONED]=0,'','Y') AS IsAbandoned

FROM ((MAIN_WELL_DATA INNER JOIN LU_FRAC_STATUS ON MAIN_WELL_DATA.FRAC_STATUS_ID = LU_FRAC_STATUS.FRAC_STATUS_ID) 
  INNER JOIN LU_MAIN_WELL_STATUS ON MAIN_WELL_DATA.STATUS_ID = LU_MAIN_WELL_STATUS.MAIN_WELL_STATUS_ID)
  LEFT JOIN dbo_VW_PRM_DRL_CMP_TOTS_SCHEDULE_PUB_WELL_PIVOT ON MAIN_WELL_DATA.WINS_NO = dbo_VW_PRM_DRL_CMP_TOTS_SCHEDULE_PUB_WELL_PIVOT.WINS;

Open in new window

I have this so far, but not sure how to complete it:

CurrentDb.Execute "delete * from tblMainWells (for frmMain_Wells Record Source)", dbFailOnError
CurrentDb.Execute "insert into tblMainWells (for frmMain_Wells Record Source) "

Open in new window

Thanks,
gdunn59

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdunn59

ASKER

PatHartman,

I'll have to get back to you on Monday. I don't have access to the database right now.

Thanks,
gdunn59

Gustav Brock

You can't execute a select query. But save the query as, say, qryMainWells and then run this code:

CurrentDb.Execute "delete * from tblMainWells where ID in (select ID from qryMainWell)", dbFailOnError
CurrentDb.Execute "insert into tblMainWell select qryMainWells.* from qryMainWells"

Open in new window

where ID is your primary key.
If the table should just be emptied, you can use:

CurrentDb.Execute "delete * from tblMainWells)", dbFailOnError
CurrentDb.Execute "insert into tblMainWell select qryMainWells.* from qryMainWells"

Open in new window

/gustav