I'm using an MS Access (2010) database for semi-critical data management for my department. It's currently small, ~137 MB, and the queries I'm running are pretty simple, mostly just joins to combine multiple data sources.
I'm running into several types of intermittent errors, where a query function will work fine at first, but on subsequent runs, without my changing anything, it will fail. Sometimes it will start working again.
Most recently, I have a query that runs fine that I export to Excel. During the same Access session, it will work the first time, but then return an "object invalid or no longer set" error when I try to export it a second time. It works again after closing and re-opening the database. This is just one example.
I'm becoming concerned that Access may be a danger to my data, to the point I'm not comfortable continuing to use it. Is this sort of behavior typical of Access, and does it result in data loss or corruption?
Edit to add query code for the example issue. This is actually set up as a series of Access queries, the SQL of which are:
Final query =
SELECT DISTINCT Var1, Var2, ...VarX
FROM Query1
LEFT JOIN Union_query
ON (Query1.DOB = Union_query.DOB)
AND (Query1.FST_NM = Union_query.FST_NM)
AND (Query1.LST_NM = Union_query.LST_NM);
Query 1 =
SELECT *
FROM ROSTER_LATEST
INNER JOIN (SELECT max(UPDATE_DATE) AS LAST_DATE, SUB_ID
FROM ROSTER_LATEST GROUP BY SUB_ID)
AS GRAB_DATE
ON (ROSTER_LATEST.SUB_ID = GRAB_DATE.SUB_ID)
AND (_ROSTER_LATEST.UPDATE_DATE = GRAB_DATE.LAST_DATE);
Union Query =
SELECT *
FROM Query2
UNION
SELECT *
from Query3;
Query 2 =
SELECT Var1, Var2, ...VarX
FROM All_FHP
INNER JOIN Query1
ON (All_FHP.Date_of_Birth = Query1.DOB)
AND (All_FHP.Last_Name = Query1.LST_NM)
AND (All_FHP.First_Name = Query1.FST_NM);
Query 3 =
SELECT Var1, Var2, ...VarX
FROM CBP_LIST
INNER JOIN Query1
ON (CBP_LIST.Date_of_Birth = Query1.DOB)
AND (CBP_LIST.Last_Name = Query1.LST_NM)
AND (CBP_LIST.First_Name = Query1.FST_NM);
