Intermittent 3078 input table error

[Alc]

From time to time, I get the message
'Run-time error 3078
The Microsoft Access database engine cannot find the input table or query 'tblClaimsByPeriodSummarisedSource'. Make sure it exists and that its name is spelled correctly'

Things I've checked, after researching online:

1. It's a local table. No ODBC connection or a link to an external Access database is being lost.

2. It definitely does exist. If I have the Access Objects panel open when I run the code I can see it. I can also open it by double-clicking on it. In case it was some kind of refresh problem, I added a line to refresh the tabledefs for the db just before the line referencing the table. This made no difference.

3. It's spelled correctly. I even used 'rename' to copy the table name and overwrite the name in the code, just in case I was missing a typo. The fact that the code runs the majority of times I try also suggests to me that the table name can't be misspelled in the code.

4. This error happens at random times. I've found no pattern in terms of time of day, person using the database, etc. Somehow it seems to know when it will cause the most embarrassment (i.e. my manager needs to use it), but I think that's just a coincidence

Does anyone know of any reason why a db would sometimes act as if the table isn't present, when it is?

I don't know if it's relevant, but I'm using Access 2010.

 

[Ranman256]

This is strange.
I caught some weird errors when the db gets close to its max size of 2 gig. Is yours?
Lots of fake errors.

Also did you compact/repair?

 

[The_Doc_Man]

Knowing the version of Access is always relevant.

Does this database have the "Compact & Repair on Close" option set? That would be one reason why you get the failure one day and success the next. Or do you try to run a manual Compact & Repair when you see the error?

I'm not going to swear on a stack of Bibles (... but that's another thread entirely...) but if you have a ragged shut-down, it would be possible for a table to be left in an unusable state. I might not have expected the exact error you named, but there are a myriad of possible errors in a program as complex as Access and another myriad of ways to shut down Access badly, so the exact error isn't as significant.

By the way, is that Access2010 or Access RunTime 2010?

 

[Alc]

Thanks for the reply.

I haven't run compact and repair for a while, but only because it's in pretty constant use while I'm here. I just did it and the size went from 32,984kb down to 15,548kb, which is not inconsiderable.

It remains to be seen if that fixes the issue, but now I know to monitor it.

 

[JHB]

..
Does anyone know of any reason why a db would sometimes act as if the table isn't present, when it is?

Do you create the table from code/query or is it "static", I ask because you write "I added a line to refresh the tabledefs", for me it sound like you create it on the fly?

 

[Alc]

Does this database have the "Compact & Repair on Close" option set? That would be one reason why you get the failure one day and success the next. Or do you try to run a manual Compact & Repair when you see the error?

Manual only. It's been requested that none of the dbs compact and repair automatically, as people get frustrated at the wait. Yes, they get even more frustrated when things go wrong, but we does what we can, right?

I'm not going to swear on a stack of Bibles (... but that's another thread entirely...) but if you have a ragged shut-down, it would be possible for a table to be left in an unusable state. I might not have expected the exact error you named, but there are a myriad of possible errors in a program as complex as Access and another myriad of ways to shut down Access badly, so the exact error isn't as significant.

So, if I use a custom button to close the db - as opposed to the user clicking on the 'x' - that may help? I've added a custom button to a few databases already, to allow me to create backups of the data at the end of each day, so this may be something to add to this one.

By the way, is that Access2010 or Access RunTime 2010?

Full 2010. Originally installed as part of Office but now the only remaining 2010 component, the rest having been upgraded to Office 2016. This problem was happening prior to the upgrade, so I can at least rule that out as a cause.

 

[The_Doc_Man]

So, if I use a custom button to close the db - as opposed to the user clicking on the 'x' - that may help?

Specifically to this question, no. There is no difference in the effect of that parameter based on how the DB was closed. The only "gotcha" is if it WASN'T closed normally but instead was externally terminated, as e.g. by "End Task" from the Task Manager window.

we does what we can, right?

You know its true.

 

[Alc]

Do you create the table from code/query or is it "static", I ask because you write "I added a line to refresh the tabledefs", for me it sound like you create it on the fly?

My apologies, I missed your response yesterday.

The table gets created 'on the fly' each time the code is run.
The data therein comes from a pivot query, and the column headings and record values change each time.

 

[The_Doc_Man]

The table gets created 'on the fly' each time the code is run. The data therein comes from a pivot query, and the column headings and record values change each time.

NOW it makes more sense. Your error 3078 is telling you that whatever you are doing to build the table, it didn't exit nicely. Look at the code that rebuilds the table and specifically focus on the part that finished building this table.

Now the big question. Since you are updating multiple elements of the tabledefs, HOW are you doing that? What primitives are you using? Do you delete the table and then recreate the table via a MAKE TABLE query? Do you erase it, change the fielddefs, and then repopulate it?

HOWEVER, there is ANOTHER question to consider ... this error COULD be based on a QUERY not being able to find this beastie. Is there even a CHANCE of that? Because the MSysObjects table uses ID numbers to identify things. If there is a change in the internal ID number (as would occur with the "Delete table/MakeTable" sequence) then other things that linked to that table would know it ain't there no more! This is a variant on the topic of early/late binding.

 

[JHB]

My apologies, I missed your response yesterday.
..

No problem, can you post your database with some sample data?

 

[Alc]

No problem, can you post your database with some sample data?

I had a try at that yesterday, but there's so much confidential data (much of which is from ODBC sources) that I couldn't work out a way to do it.

 

[JHB]

Okay, can you post the code then in which you've the problem?

 

[Alc]

Okay, can you post the code then in which you've the problem?

Sorry for the delay in responding, I took a few days off for Christmas.

The sub procedure involved is shown below and the error occurs at the line in bold red font. As I say, this doesn't happen every time. At a guess, perhaps one time in ten or twelve runs.

Only one person is using this code at the moment, for testing purposes, so there's no conflict occurring.

 Sub Reload_Claims_By_Period()
    Dim Rst As Recordset
    Dim strSQL As String
    Dim ldStartDate As Date
    Dim ldEndDate As Date
    Dim liStartYearNumber As Integer
    Dim liEndYearNumber As Integer
    Dim strPeriod As String
    Dim liPeriodSort As Integer
    Dim llClosedTotal As Long
    Dim llCoveredTotal As Long
    Dim llThemedUpTotal As Long
    Dim llNotARankTotal As Long
    Dim llUnknownTotal As Long
    Dim llPendingTotal As Long
    Dim strDisposition As String
    Dim ldCalendarProcessDate As Date
    Dim liFieldCount As Integer
    Dim liCount As Integer
    Dim strfield As String
    Dim Db As Database
    
    Set Db = CurrentDb
    
    'Reload summarised data by claimed period
    DoCmd.RunSQL "DELETE * FROM tblClaimsByPeriod_Summary;"
    DoCmd.RunSQL "DELETE * FROM tblClaimsByPeriod_Detailed;"
    
    DoCmd.OpenQuery "qryRepopulatetblClaimsByPeriod_Summary"
    Set Rst = Db.OpenRecordset("SELECT * FROM tblClaimsByPeriod_Summary ORDER BY CALENDAR_PROCESS_DATE;")
    If Not Rst.EOF Then
        Rst.MoveFirst
        Do While Not Rst.EOF
            liStartYearNumber = -1
            liEndYearNumber = 0
            strDisposition = Nz(Rst!DISPOSITION, "Unknown")
            
Assign_Period:
             If Len(Rst!CALENDAR_PROCESS_DATE) = 0 Or IsNull(Rst!CALENDAR_PROCESS_DATE) Then
                ldCalendarProcessDate = #1/1/1900#
                strPeriod = "Claim period unknown"
                liPeriodSort = 100
                
                GoTo Insert_Point
            End If
            
            ldCalendarProcessDate = Rst!CALENDAR_PROCESS_DATE
            ldStartDate = SQLDate(DateAdd("yyyy", liStartYearNumber, Date))
            ldStartDate = DateAdd("d", 1, ldStartDate)
            ldEndDate = SQLDate(DateAdd("yyyy", liEndYearNumber, Date))
            If SQLDate(Rst!CALENDAR_PROCESS_DATE) >= ldStartDate And SQLDate(Rst!CALENDAR_PROCESS_DATE) <= ldEndDate Then
                If Calc_Period(SQLDate(Rst!CALENDAR_PROCESS_DATE)) = 0 Then
                    strPeriod = "Current Year"
                Else
                    strPeriod = "Current Year minus " & Calc_Period(Rst!CALENDAR_PROCESS_DATE)
                End If
                liPeriodSort = Calc_Period_Sort(SQLDate(Rst!CALENDAR_PROCESS_DATE))
            Else
                liStartYearNumber = liStartYearNumber - 1
                liEndYearNumber = liEndYearNumber - 1
                GoTo Assign_Period
            End If
            
Insert_Point:
             If ldCalendarProcessDate <> #1/1/1900# Then
                DoCmd.RunSQL "UPDATE tblClaimsByPeriod_Summary " & _
                             "SET tblClaimsByPeriod_Summary.Period = '" & strPeriod & "', " & _
                             "tblClaimsByPeriod_Summary.Period_Sort = " & liPeriodSort & " " & _
                             "WHERE CALENDAR_PROCESS_DATE = #" & ldCalendarProcessDate & "#;"
            Else
                DoCmd.RunSQL "UPDATE tblClaimsByPeriod_Summary " & _
                             "SET tblClaimsByPeriod_Summary.Period = '" & strPeriod & "', " & _
                             "tblClaimsByPeriod_Summary.Period_Sort = " & liPeriodSort & " " & _
                             "WHERE (Len(CALENDAR_PROCESS_DATE) = 0 or IsNull(CALENDAR_PROCESS_DATE));"
            End If
             Rst.MoveNext
        Loop
    End If
    Set Rst = Nothing
    'DoCmd.OpenQuery "qryClaimsByPeriodSummary"
    Db.TableDefs.Refresh
    DoCmd.OpenQuery "qryRecreatetblClaimsByPeriodSummarisedSource"
    Db.TableDefs.Refresh
    RefreshDatabaseWindow
[COLOR=red][B]    Set Rst = Db.OpenRecordset("tblClaimsByPeriodSummarisedSource", dbOpenTable)[/B][/COLOR]
    If Not Rst.EOF Then
        Rst.MoveFirst
        liFieldCount = Rst.Fields.Count
        For liCount = 1 To (liFieldCount - 1)
            strfield = Rst.Fields(liCount).Name
            strSQL = "UPDATE tblClaimsByPeriodSummarisedSource " & _
                     "SET tblClaimsByPeriodSummarisedSource.[" & strfield & "] = 0 " & _
                     "WHERE tblClaimsByPeriodSummarisedSource.[" & strfield & "] Is Null;"
            DoCmd.RunSQL strSQL
        Next
        liCount = 1
    End If
    Set Rst = Nothing
 End Sub

 

[The_Doc_Man]

In the code above, you have what by context appears to be a MAKE TABLE query named "qryRecreatetblClaimsByPeriodSummarisedSource" which you did not post.

Looking at your process, I don't see why you need recordset operations vs. a slightly more complex SQL with restrictive WHERE clauses. Further, if you are regenerating a table to have a certain type of data aggregates, a SELECT clause can do that, so I don't see why you need the separate table. Reports can run on sources of SELECT queries, too. I certainly don't want to seem meddlesome, but the recordset operations are not nearly as fast as would be a sequence of queries using WHERE clauses to limit what each one does to just the targeted records.

Reading through the code, it appears that ALL of your raw data is available in a single table from which you can extract the data you need. You are already building queries "on the fly" using substitution so you don't seem bothered by doing that kind of work, and that is perhaps all you might need. Look up Parameter Queries as an alternative to rebuilding queries on the fly. If you can do the WHERE logic correctly, you could use parameter queries to build your reports for a given calendar period. "BETWEEN...AND" works just fine in WHERE clauses.

 

[JHB]

..
The sub procedure involved is shown below and the error occurs at the line in bold red font. As I say, this doesn't happen every time. At a guess, perhaps one time in ten or twelve runs.

For testing suppose I would create a loop and call the sub in the loop, for get an idea of how often it happen. It is easier with a loop as clicking a button again and again.
Something like below:

  Dim x As Integer
  For x = 1 To 100
      Me.Counter = x 'Create a control on a form call it Counter
      Call Reload_Claims_By_Period
  Next x

When you've got an idea of how often it happen I would add a DoEvents just before the problem codeline, and then run the above test again to see if that gives any improvement.

  [B]DoEvents[/B]
[COLOR=red][B]  Set Rst = Db.OpenRecordset("tblClaimsByPeriodSummarisedSource", dbOpenTable)[/B][/COLOR]

Sorry - but personally I do not like your programming style, especially building code by labels are for me spaghetti programming. Only place that I accept it is associated with error handling.
Why do you open/shows the queries, (DoCmd.OpenQuery ...)?
Can't you instead (deleting and) creating the table, just empty it? Then the error: "The Microsoft Access database engine cannot ..." should not appear!

 

[Minty]

Have you considered using db.execute for all your qry runs. I believe it has some advantages. Also try dbOpenDynaset on your table, rather than dbOpenTable.

I think I'm with the Doc_Man though , that you could I think achieve the same results more efficiently with some better built queries, thus avoiding having to keep recreating the table.

In access I only use temp tables when I'm trying to present data is some whacky format for end users, and the queries get too complex/slow on big recordsets. In SQL server I use them all the time...

 

[Alc]

.

Sorry - but personally I do not like your programming style, especially building code by labels are for me spaghetti programming. Only place that I accept it is associated with error handling.
Why do you open/shows the queries, (DoCmd.OpenQuery ...)?
Can't you instead (deleting and) creating the table, just empty it? Then the error: "The Microsoft Access database engine cannot ..." should not appear!

I would normally use the clear and refill process, but the underlying crosstab query means that the number and name of the fields changes each time the process is run. Trying to find a way around this is why the code is far more complicated than I've previously attempted. I didn't even know that labels COULD be used to create a table, until I found an example online. It worked, so I went with it. I accept that my coding isn't up to your standards, but thanks for taking the time to help

In the code above, you have what by context appears to be a MAKE TABLE query named "qryRecreatetblClaimsByPeriodSummarisedSource" which you did not post.

Sorry, this is that query

 SELECT qryClaimsByPeriodSummary.* INTO tblClaimsByPeriodSummarisedSource
FROM qryClaimsByPeriodSummary;

which comes from this query

 TRANSFORM Count(tblClaimsByPeriod_Summary.Period) AS Claims
SELECT tblClaimsByPeriod_Summary.Period
FROM tblClaimsByPeriod_Summary
GROUP BY tblClaimsByPeriod_Summary.Period
PIVOT tblClaimsByPeriod_Summary.Disposition;

Looking at your process, I don't see why you need recordset operations vs. a slightly more complex SQL with restrictive WHERE clauses. Further, if you are regenerating a table to have a certain type of data aggregates, a SELECT clause can do that, so I don't see why you need the separate table.

Earlier on, I was asked to store 'snapshots' of the data so we could run comparisons. I thought creating a table was the easiest way to do this. I didn't realize I'd cause problems by doing so.

Reports can run on sources of SELECT queries, too. I certainly don't want to seem meddlesome, but the recordset operations are not nearly as fast as would be a sequence of queries using WHERE clauses to limit what each one does to just the targeted records.

That's worth knowing. I used the recordset method, as it was already present at numerous places in the database. Continuing with it just made the work quicker but if changing to queries would be faster, I'll do that.

Reading through the code, it appears that ALL of your raw data is available in a single table from which you can extract the data you need. You are already building queries "on the fly" using substitution so you don't seem bothered by doing that kind of work, and that is perhaps all you might need. Look up Parameter Queries as an alternative to rebuilding queries on the fly. If you can do the WHERE logic correctly, you could use parameter queries to build your reports for a given calendar period. "BETWEEN...AND" works just fine in WHERE clauses.

I tried the parameter route to start, but was having problems passing the parameters to the crosstab query. The 'method' I arrived at
was chosen to get things up and running as fast as possible, using what I already knew. A similar approach is being used in a few other places within the database so, as it works most of the time, I didn't see a reason to change it wholesale. Apparently, that was a mistake.

Have you considered using db.execute for all your qry runs. I believe it has some advantages. Also try dbOpenDynaset on your table, rather than dbOpenTable.

I hadn't, but shall do so now. Thanks for the tips.

 

[The_Doc_Man]

Earlier on, I was asked to store 'snapshots' of the data so we could run comparisons.

This is actually a perfectly valid reason for storing the data in a separate table, particularly if you are purging older data from the main data table and want to keep the summaries. This sort of thing is done for inventory systems quite often (make summary records and then archive). However, my comment about recordset vs query as a way to generate your snapshots is still valid, I think.

Further, depending on the "compression ratio" it might still make sense to not purge or delete the summary table in question, but simply create your cross-tab and then insert the results into an archiving table with the applicable date ranges and other values as parts of a compound prime key. Then when you want to compare apples to apples, your apples are in the same table. If so, you can then use the MS GRAPH internal linkup with Access to visually show your historic trends right out of the archiving table. Just take this as a couple of thoughts not trying to be pushy, but offering a different viewpoint.

Concur with Minty on {dao} db.execute for action queries - particularly with the "Fail on Error" option - and with dbOpenDynaset as being better ways to do queries and recordsets.