Deleting & Creating Tables using VBA

[khwaja]

I routinely import excel files into my DB and then update the contents of existing tables. To do this programatically, I used the following code. I am not very confident user of VB so would like some help in improving this. It works one time but next time it freezes. However, if I delete imported tables physically, it works again. So looks like I did not get it right in the first part dealing with deletion of temp tables.


Sub Monthlyroutine()
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb

On Error GoTo Macro1_Err

DoCmd.SetWarnings False

'If tables exist, delete it.
For Each tdf In dbs.TableDefs
If tdf.Name = "tblDept1" Or tdfName = "Sale_Cust Data1" Or tdfName = "Region & Areas1" Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "tblDept1", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Departmental Sale.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Sale_Cust Data1", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Sales_Cust Data.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Region & Areas1", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Region & Areas.xls", True

' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE FROM tblDepts;"
DoCmd.RunSQL "DELETE FROM [Region & Areas];"
DoCmd.RunSQL "DELETE FROM [Sale_Cust Data];"

dbs.Execute " INSERT INTO tblDepts SELECT * FROM [tblDept1];"
dbs.Execute " INSERT INTO [Sale_Cust Data] SELECT * FROM [Sale_Cust Data1];"
dbs.Execute " INSERT INTO [Region & Areas] SELECT * FROM [Region & Areas1];"

DoCmd.SetWarnings True
Set dbs = Nothing

MsgBox ("All Data updated")

Macro1_Exit:
Exit Sub

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Sub

 

[vbaInet]

If you're not performing any data validation on the fields, don't you think you should export it directly to the table?

Creating and deleting tables continuously isn't advisable.

 

[khwaja]

Thanks. No I am not performing any validation. But can I directly update a table without exporting first? If so, could you help?

 

[vbaInet]

You are using the right functions there (Docmd.Transferspreadsheet acImport etc...). It's just a matter of changing the TableName paramter.

When it comes to importing of data I always use a function which will check every column for length, null string, data type (or whatever constraints I set up on my form) to ensure you don't get "bad" data. Then it will show a results page pointing out where errors were found if they exist.

 

[khwaja]

Thanks. I really don't have much concerns on data quality. May be I try the code in this amended form. Do you think this will achieve the same thing?

Sub Monthlyroutine()
Dim dbs As Database, tdf As TableDef
Set dbs = CurrentDb

On Error GoTo Macro1_Err

DoCmd.SetWarnings False

' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE FROM tblDepts;"
DoCmd.RunSQL "DELETE FROM [Region & Areas];"
DoCmd.RunSQL "DELETE FROM [Sale_Cust Data];"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "tblDepts", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Departmental Sale.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Sale_Cust Data", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Sales_Cust Data.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Region & Areas", "\\Nnorsogfas031\Design\99 AK Files\Zircon\Downloads\Region & Areas.xls", True


DoCmd.SetWarnings True
Set dbs = Nothing

MsgBox ("All Data updated")

Macro1_Exit:
Exit Sub

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Sub

 

[DCrake]

If your code is a cut and paste from your module then you have errors in it.

' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE FROM tblDepts;"
DoCmd.RunSQL "DELETE FROM [Region & Areas];"
DoCmd.RunSQL "DELETE FROM [Sale_Cust Data];"

Should be

' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE [COLOR="Red"]*[/COLOR] FROM tblDepts;"
DoCmd.RunSQL "DELETE [COLOR="red"]*[/COLOR] FROM [Region & Areas];"
DoCmd.RunSQL "DELETE [COLOR="red"]*[/COLOR] FROM [Sale_Cust Data];"

David

 

[khwaja]

Thanks for ypur help.

 

[LPurvis]

I think DELETE FROM is the ANSI standard syntax.
It's certainly supported in Jet and SQL Server (as well as many other RDBMS).

Cheers.

 

[vbaInet]

I think DELETE FROM is the ANSI standard syntax.
It's certainly supported in Jet and SQL Server (as well as many other RDBMS).

Cheers.

I concur, lpurvis