Verify Table Exists

[ghudson]

I have a process that creates a table [make table query] which is done when the records need to be refreshed [this is not a simple requery process]. Then next part of the function is opening up a record set based on that table. I am getting a runtime error # 3078 - The Microsoft Jet database engine cannot find the input table because the make table process has not completed and the record set 'step' is trying to populate the data. How can I loop through this code to continue until the table exists?

    Set dbRisk = OpenDatabase("X:\mydatabase.mdb")
        
    DoCmd.OpenQuery "qryCreateRecords" 'make tblRecords table query

[COLOR=Blue]
    Dim db As Database
    Dim rs As Recordset
    Dim x As Object
    For Each t In CurrentDb.TableDefs
        If x.Name = "tblRecords" Then
'            MsgBox "table exists"
        End If
    Next x
[/COLOR]    
    Set rs = db.OpenRecordset("tblRecords")

Thanks in advance for your help!

 

[dcx693]

That's a good question. I don't have a good answer, just a workaround that you've already suggested: just throw in a Do loop to continue looping until the table exists.

 

[The_Doc_Man]

I might suggest that you add a DoEvents (or whatever that call is) inside your loop to let Access flush its pending-event lists.

Once the table exists with a single record, its name will already have to exist in the tabledefs collection. So looking for the table by name won't help.

The other possibility if both procedures are in the same database is that I would set a public variable in a general module and have a public function code that tests the state of the variable. Use it as an explicit table lock. Don't run the second round of the code until the first round unlocks the table. Just 'cause Office has a lock file doesn't mean you can't have one.

 

[ghudson]

I am in an endless loop. What am I doing wrong?

    Dim t As Object
    For Each t In CurrentDb.TableDefs
    Do While t.Name <> "tblRecords"
    DoEvents
    Loop
    Next t

 

[FloBob]

Might want to put the next t before the loop statement...

 

[ghudson]

Might want to put the next t before the loop statement...

I had already tried that for I get the error message... "Do without loop"

Any other suggestions?

 

[FloBob]

    Dim t As Object
    For Each t In CurrentDb.TableDefs
    if t.Name = "tblRecords" then
    DoEvents
    end if
    Next t

if you need it to not = or whatnot just replace if not t.name = "tblrecords" then. My mistake about the last post that wouldnt work anyway... though you are using a do loop to do the work of an if statement. Best to just use the if imo... please let me know if Im missing anything.

 

[ghudson]

I am still getting the runtime error 3078 because the table does not exist when the record set is called. I tried your suggestion above and I also tried it with this line... If t.Name <> "tblRecords" Then since I need the loop to continue if the table does not exist. The table is being made with a make table query and then the record set is populated.

    Dim t As Object
    For Each t In CurrentDb.TableDefs
    If t.Name = "tblRecords" Then
    DoEvents
    End If
    Next t

This code appeared to be close to what I needed but it still was not looping [testing for the existance of the tblRecords table] but I am stuck in a non ending loop.

    Dim t As Object
    For Each t In CurrentDb.TableDefs
    Do While t.Name <> "tblRecords"
    DoEvents
    Loop
    Next t

Any other suggestions on how I can test for when the table tblRecords finally exists [is created] before the record set is refreshed? Thanks for your help!

 

[FloBob]

Public Function CheckTable(strTblName As String) As Boolean
On Error GoTo checktable_err

Dim objTBL As Object

For Each objTBL In CurrentDb.TableDefs
    If objTBL.name = strTblName Then
    CheckTable = True
    End If
Next


If IsEmpty(CheckTable) Then
CheckTable = False
End If

checktable_finish:
Exit Function


checktable_err:
MsgBox Err.Description
Resume checktable_finish
End Function

There you go I tested this too so I know it works... Let me know if this helps, just put it in a module or in your code somplace it can be seen and use the following code to activate it you probably want to do something different then have it tell the user P. I hope this works for you if you get an error message please let me know the exact one, thanks P...

    If CheckTable("Your Table") Then
        MsgBox "IT EXISTS"
    Else
        MsgBox "IT DONT EXIST"
    End If

 

[ghudson]

Thanks FloBob! Your function is working. I do not quite understand how since I do not see how/where it is looping if the table does not exist so that it keeps verifying if the table exists until it does. Or is it by the time it takes the function to run that the table is finally made by the make table query?

I added a GoTo [I know, not efficient coding] but I think that I need to do something if the table has not been created, yet for it only takes a few seconds to run the make table query. Here is what I added to the code

    DoCmd.OpenQuery "qryCreateRecords" 'make tblRecords table query

Continue:
    If VerifyTableExists("tblRecords") = False Then GoTo Continue

    Set dbRisk = OpenDatabase("X:\mydatabase.mdb")
    Set rs = db.OpenRecordset("tblRecords")

Thanks!

 

[FloBob]

You dont really need to do the goto you can make a doevents command right before you check for the tables existance and it will make sure all previous commands are finished before proceding. Up to you though I hate goto statements outside of using them for error handling and you dont really need to do that either. You have the possibility of creating a continuous loop if another part of your program breaks or what have you. Anyway I'm glad that helped.

 

[The_Doc_Man]

The structure implied by the

A: IF NOT DONE THEN GOTO A

is also handled (better) by WHILE or UNTIL statements, which semantically say the same thing but can be coded a little bit better for the purists. Of course, for an empty WHILE loop, that is what would get compiled anyway, but we won't bother the purists which such heresy.