Using Access 97 a database is not creating a table when it should how to I fix this?

Question

Error: The Microsoft Jet database engine cannot find the input table or query 'tblPartsUsed'. Make sure it exists and that its name is spelled correctly.

Usually we just manually recreate the table and it works but for some reason this isn't working any more.

Private Sub cmdRefresh_Click()

Call RefreshData

Function RefreshData()

Dim tmpRec, tmpRec1

tmpRec = Form_frmStatus.RecordSource

Form_frmStatus.lstSearch.RowSource = ""

Form_frmStatus.RecordSource = ""

tmpRec1 = Form_frmPartsUsed.RecordSource

Form_frmPartsUsed.RecordSource = ""

DoCmd.Close acForm, "frmStatus"

DoCmd.SetWarnings False

'Me.RecordSource = ""

'Me.lstSearch.RowSource = ""

DoCmd.OpenQuery "qrymakePArtsUSed"

DoCmd.OpenQuery "qryMakeSummary"

DoCmd.SetWarnings True

DoCmd.OpenForm "frmStatus"

Form_frmStatus.RecordSource = tmpRec

Form_frmPartsUsed.RecordSource = tmpRec1

'Me.RecordSource = tmpRec

Form_frmStatus.lstSearch.RowSource = "qrysearchall"

'Me.lstSearch.Requery

'Me.Requery

'Me.Detail.Visible = True

End Function

If anyone can help it is greatly appreciated.

Answer 1

PS:  Your link isn't publicly shared, so we can't use it anyways.

Answer 2

Re-read my post. I did not ask you to post your database, but rather for you to debug the issue.

Then maybe post the results here.

Answer 3

Here's a link to a google drive for the file

https://drive.google.com/file/d/1-Ise1CSJ4FMktK\_xvAveprBATGz-F9u1/view?usp=sharing

Answer 4

You appear to be running two MakeTable queries:

DoCmd.SetWarnings False

DoCmd.OpenQuery "qrymakePArtsUSed"

DoCmd.OpenQuery "qryMakeSummary"

As Daniel said, this is highly irregular but let's ignore that for a minute. Also note the use of SetWarnings which essentially says "do not show me any errors that may occur; just keep going".

You need to comment that line out. Then set a breakpoint on the next line and carefully step over the two MakeTable lines. 99% chance the error message is going to tell you something interesting.

Answer 5

Without seeing the details of each query it is virtually impossible for us to help you.  It's it possible to be given a link to download a copy for closer look (with some instructions as to what exactly isn't working)?

That said, a '97 database is more than deprecated!  The fact that you need to recreate a table over and over indicates either a major issue with the database itself, or a fundamental design flaw.  You truly should be upgrading to an accdb file format (which is 14-15 years old already).

When was the last time the database was compacted and repaired?

Do you have functional backups you could revert to?