Check if table exist delete if true

[Trevor G]

I have been asked to create a procedure to see if a table exist if it does delete it then transfer from another database, if not then transfer from another database?

I can't use a linked table as it goes across multiple sites with individual networks.

Any suggestions welcome.

 

[JANR]

Does this help: http://www.mvps.org/access/tables/tbl0001.htm

combined with

CurrentDb.Execute " DROP TABLE " & strTablename, db.FailOnError

JR

 

[Trevor G]

JR thank you

I am sure this will help me as it looks exactly what I need

Thanks

 

[Trevor G]

I have this now working, thanks JR you got me to teh right place, I have used delete table rather than Drop, not sure if this matters! example code is as follows:

Function TableExists(TableName As String) As Boolean
'*************************************************
' Purpose: Checks to see whether the named table exists in the database
'
' Returns: True, if table found in current db, False if not found.
'************************************************* ****************************
Dim strTableNameCheck
On Error GoTo ErrorCode
'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)
'If no error and we get to this line, true
TableExists = True
ExitCode:
On Error Resume Next
Exit Function
ErrorCode:
Select Case Err.Number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description & "hlfUtils.TableExists"
Resume ExitCode
End Select
End Function

Then the check

Function TestLU_Currency()
If TableExists("LU_Currency") Then
CurrentDb.TableDefs.Delete ("LU_Currency")
End If
DoCmd.TransferDatabase acImport, "Microsoft Access", "V:\TMI Reference Data.mdb", acTable, "LU_Currency", "LU_Currency", False
End Function

 

[JANR]

Great.

From Access help:
The Microsoft Access database engine does not support the use of DROP, or any of the DDL statements, with non-Microsoft Access database engine databases. Use the DAO Delete method instead.

JR

 

[gemma-the-husky]

why do you need to delete the table.

just reimport. It will overwrite existing, I am sure.

If its a linked table, its a bit trickier.

 

[Trevor G]

why do you need to delete the table.

just reimport. It will overwrite existing, I am sure.

If its a linked table, its a bit trickier.

Actually what it does is imports but adds a number so if my table was called Combined and I use the Docmd for the first time then it works fine, if I repeat this then it gives me a second table called Combined1.

Grateful though as it made me check what does happen.

 

[DCrake]

What Dave was actually suggesting was to delete the contents of the table leaving the table intact then append the records from the new table into the empty table.

If the table is part of the relationships schema Access will not let you delete it anyway.

 

[gemma-the-husky]

What Dave was actually suggesting was to delete the contents of the table leaving the table intact then append the records from the new table into the empty table.

If the table is part of the relationships schema Access will not let you delete it anyway.

I wasnt really - If I want to import a csv to a temporary table (which I often do), then on reflection I think my standard routine DOES drop the existing table, and then imports the new table. In some instances, if I need an index in the table, then I do clear a pre-existing table, instead, and import into that.

It had just occurred to me, (and I am not sure now, without checking) that if the import (docmd.transferxxx) can overwrite an existing table, then you don't even need to drop a table first. Just import with the flag set to overwrite.

Having reconsidered, I think maybe it probably doesnt overwrite an existing table, which is why you get a new table called import1 etc.

so now i've just checked, and I use this syntax to drop the current import table, ready for a new one

const importtable = "import_table"

sqlstrg = "drop table " & "[" & importtable & "]"
DoCmd.RunSQL sqlstrg

maybe the OP's code was failing because of the missing square brackets

 

[DCrake]

Dave, I know when exporting a table to a different mdb it will ask about overwriting, when importing it gives it a numeric suffix.

The only issue I see is if the new table structure is different from the deleted one. Also will there be any issues with relationships and PK's-FK's?

 

[Dairy Farmer]

I use the following code to relink data if I move the backend. This may help you

On Error Resume Next
DoCmd.DeleteObject acTable, "Table1"

DoCmd.TransferDatabase acLink, "Microsoft Access", CurrentProject.Path & "\Data\BE.accdb", acTable, "Table1", "Table1", False
MsgBox "The data tables have now been relinked.", vbInformation, "Relink Data"
DoCmd.Close