Need to make table, refresh, see if table exists (VBA). How do I refresh?

[dk4300]

Hi!
I'm using VBA in Access 2007.

I am looping though a list of all table names in all (selected) external databases and need to append the external data to my current database table (if the table exists in my current database) or make a new table with the data (if the table does not exist in my current database). I'm missing one component which is a "refresh" after I make the new table.

Currently, when the table does not exist, it makes the table, but when I get to the next database and it looks to see if that table exists, it says it doesn't, and keeps making a new one.

Thanks in advance!

 
'Count records in external table
    strSQL = "SELECT [" & strTableName & "].*"
    strSQL = strSQL & "FROM [" & strTableName & "] "
    strSQL = strSQL & "IN '" & strDatabasePath & "'"
    intRecordcount = funRecordCount(strSQL)

'Does external table exist in current database
    blnTableExists = funTableExists(strTableName)

'If external database exists in current database then append
    If blnTableExists = True Then
        AppendToExistingTable

'If external database does not exist in current database then make table
    ElseIf blnTableExists = False Then
        MakeNewTable
    End If

 Public Function funTableExists(strNewTableName) As Boolean
On Error GoTo Err_funTableExists

    Dim db As Database
    Dim tdf As TableDef
    Set db = DBEngine(0)(0)
    funTableExists = False
    For Each tdf In db.TableDefs
        Debug.Print tdf.Name
        If tdf.Name = strNewTableName Then funTableExists = True
    Next tdf
    Set db = Nothing
        
Exit_funTableExists:
        Exit Function
    
Err_funTableExists:
        MsgBox Err.Description
        Resume Exit_funTableExists

End Function

Sub MakeNewTable()
On Error GoTo Err_MakeNewTable
    
    DoCmd.SetWarnings False
    
    strSQL = "SELECT [" & strTableName & "].* "
    strSQL = strSQL & "INTO [" & strTableName & "] "
    strSQL = strSQL & "FROM [" & strTableName & "] "
    strSQL = strSQL & "IN '" & strDatabasePath & "'"
    DoCmd.RunSQL (strSQL)
    
    DoCmd.SetWarnings True

Exit_MakeNewTable:
        Exit Sub
    
Err_MakeNewTable:
        MsgBox Err.Description
        Resume Exit_MakeNewTable
End Sub

[NeoPa]

I wondered what you meant when you say "refresh the table after the Make New", so I looked at the code and that didn't help either.

What are you trying to ask for?

[dk4300]

Hi!
Sorry for the confusion, I don't know if "refresh" is even the right word to use. I need to refresh (or something) the whole list of tables objects in the database, rather than just one table itself.

Situation (Short version): I'm doing a "make table" query via SQL. Next, I need to run code that checks the database to see if the table exists, and if so, returns "true".

Problem: Though the new table is created (and if you F5 the database object list, it appears with the others) when I run my function that loops through the tables existing in the database it does not find that table name. If I close and reopen the database it does find it. I don't know what happens on the close (or open) of a database to the databse objects so I am wondering what I am missing from after my "make table" query that lets the database recognize my new table as a TableDef in TableDefs. Does that make sense?

Situation (Longer Version): There are 12 databases in a folder on the drive, one for each month. Mostly they contain the same 70 tables, but there are exceptions (some could contain more of less tables). I need to combine all 12 databases into one, appending the records together, and ending up with 1 database with 70+ tables that all contain records for the whole year. My approach is to start with a new database, one by one do a make table query (looping, via SQL and VBA) for each of the 70 tables in the external January database. When I loop to the external Feb database, I now want to check if the those tables have already been made in my new complete database (meaning they were in Jan.), if so, append, if not, make new table. My problem: When I am importing the Feb. tables it's not recognizing that I just made those same tables for Jan and it is doing a new make table for everything and overwriting the Jan data.

I would love any help. I've struggled with it and have searched the internet. It might just be one line of code or simple explanation that is outside my familiarity.
Thanks!

[NeoPa]

How bizarre. I just fell over this myself - and if I'm not mistaken - the very same day you did (Edit - or maybe a day or so earlier) :-D You want to know why a newly created object in the database doesn't appear to be in the appropriate collection of those objects. Here's a link to the thread I was involved in at the time - just for curiosity (export access into separate excel worksheet by value).

I found that the DAO.Database object returned by CurrentDb() contains collections which only reflect those objects available at that time (the time CurrentDb() was invoked). Simply set your db variable to CurrentDb() again after the new object (Table in this case) has been added (I chose to set it to Nothing first, but I'm not positive that's necessary as Access should do that for you. It has a habit of not clearing things up too well though, so it doesn't hurt).

[ADezii]

@dk4300:

1. I'm not 100% sure on this, but you may need to Refresh the TableDefs Collection immediately after the creation of the New Table in MakeTable(), (Code Line #7), as in:
   

   strSQL = "SELECT [" & strTableName & "].* " 
   strSQL = strSQL & "INTO [" & strTableName & "] " 
   strSQL = strSQL & "FROM [" & strTableName & "] " 
   strSQL = strSQL & "IN '" & strDatabasePath & "'" 
     DoCmd.RunSQL (strSQL) 
   
   CurrentDb.TableDefs.Refresh

2. To make sure that the New Table is displayed in the Database Window:
   

   Application.RefreshDatabaseWindow

3. Let me know if this approach works out, I'm curious myself.

[NeoPa]

Strangely (very), when I do a test now I cannot even reproduce the behaviour of the new item not being visible in the first place :-S

When I fell over this a few days ago though, I found that resetting the value of db certainly got around it.

That's not to say that ADezii's idea cannot work too. I'd go further. I expect it would. I checked the Object Browser (F2) and that indicated it should. Unfortunately I was unable to test definitively due to being unable to reproduce the error, but I'm sure it would work well.

PS. In my recollection of the problem refreshing the db window was never necessary, as the problem didn't manifest there, but that's certainly something worth knowing about. I may apply it to some code I've been having problems with.

[ADezii]

@NeoPa:
If, for any strange reason, the problem cannot be resolved, there is a viable work-a-round. Table Names can be stored in either a Public Collection or Array and referenced in either one of these as opposed to the TableDefs Collection.

[NeoPa]

I expect both approaches will work actually. Worry if the OP reports the problem is persisting.

[dk4300]

Thank you both so much for your responses. Sorry for MY late response, I was keeping tabs on your answers but was pulled onto something else and didn't want to respond until I actually was able to try what you suggested.

I tried both of your suggestions (TableDefs.Refr esh from ADezii and CurrentDB from NeoPa). Thanks for explanation on the CurrentDB, that totally makes sense.

In the end, the CurrentDB.Table sDef.Refresh wasn't necessary (tried it both ways) and I found that I needed CurrentDB added to line 6 of the funTableExists function (which I'm sure I pulled off the internet). So instead of:

Set db = DBEngine(0)(0)

I updated it to:

Set db = CurrentDB

And now it's great.

I don't know about DBEngine(0)(0), so I don't know why that didn't work but CurrentDB does, but I have been using this function in multiple places and am relieved to be back in business. Especially since this procedure is something I may be likely to use in other projects.

Thanks for your time and suggestions!

[NeoPa]

I'm glad to here you resolved it DK, but I'm not sure why getting CurrentDb only once seems to work. The fact your code's working is the important thing though :-)

[dk4300]

I put it in the function that checks if the table exists (not the main procedure), so the CurrentDB is updated every time that function is called (which is on every table).
So it's (generally):

Get first table name. Set = strTableName
Call function to check if table exists
Set db = CurrentDB
Loop through each tabledef in tabledefs.
Return true of false if tabledef.name = strTableName
If true, append to strTableName
If false, make new table
Loop to next table name.

Does that answer your question?
Thanks!

[NeoPa]

Yes. I think it does :-)