Do not import data if table already exists

[KP_SoCal]

I run the following code to import a table from another database into my current database.

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\MyDocs\AnotherDatabase.mdb", acTable, "AnotherTable2009", “CurrentTable2009”

If CurrentTable2009 already exists in my current database, I do not the sub routine to run. So essentially I'm saying:

If CurrentTable2009 Does Not Exist Then
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\MyDocs\AnotherDatabase.mdb", acTable, "AnotherTable2009", “CurrentTable2009”
Else
Exit Sub
EndIf

I'm just not sure how to translate this logic into the correct code for an If/Else condition. Thanks for any suggestions!

 

[WayneRyan]

KP,

Rough idea:

Dim tdf As DAO.TableDef

On Error GoTo ErrHandler

Set tdf = CurrentDb.TableDefs("AnotherTable2009")

'
' DoCmd.Transfer ....
'

Exit Sub

ErrHandler:
   Stop                 ' I don't know the error code, put the stop here
   If Err = "???" Then  ' and "hover" over Err to see the number (Then remove the STOP)
      MsgBox ("No Table. Exiting")
      Exit Sub
   End If

Wayne

 

[KP_SoCal]

Thanks for the response, but I couldn't get this to work. As a work around, I ended up utilizing the DCOUNT method and the error handler listed below. It accomplishes what I needed. I posted the code in case anyone else is interested...

Private Sub Command52_Click()
On Error GoTo ErrHandler
If DCount("*", "AnotherTable2009") > 0 Then
MsgBox "Table already exists."
Else
'Do nothing
End If
ErrHandler:
If Err = "3078" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\MyDocs\AnotherDatabase.mdb", acTable, "AnotherTable2009", “CurrentTable2009”
Exit Sub
End If
Exit Sub

 

[ghudson]

Test if table already exists

This will test if the table in question exists...

    Dim tblDef As TableDef
    
    For Each tblDef In CurrentDb.TableDefs
        If InStr(1, tblDef.Name, "YourTable") > 0 Then
            MsgBox "YourTable already exists."
        End If
    Next tblDef

 

[KP_SoCal]

Ghudson, you nailed it! Cheers!!!

 

[gemma-the-husky]

this is more efficient than iterating all the tables

exists=false
on error resume next
exists = currentdb.tabledefs(tablename).name = tablename

 

[KP_SoCal]

Thanks Dave. I'm almost embarrassed to ask, but can you show me how this would integrate into my block of code. I applied it where I thought it should go and it didn't run. Thanks!

exists=false
on error resume next
exists = currentdb.tabledefs(tablename).name = tablename

 

[gemma-the-husky]

best as a function, i would have thought

function doestableexist(tablename as string) as boolean
dim exists as boolean

   exists=false  
   on error resume next
   exists = currentdb.tabledefs(tablename).name = tablename
'note if there IS no table, this will error, hence the statement above
   doestableexist = exists
end function

sub tryit
   msgbox(doestableexist("anytable"))
   'should display true or false in a msgbox
end sub