Clear Tables and Transfer Data from Excel to Access

[Beatrix]

Hi Everyone,

I have an Access Database with 16 tables and need to have a script which deletes all records from 16 tables and pull new data from Excel to Access.
There are 16 spread sheets in Excel. Each spread sheet is a data source for one table in Access.

I have below code to clear 16 tables. I need to achieve the second bit which is pulling data from Excel to Access. Can anyone help me on this please? What approach I should use etc? I came across with some scripts but not for multiple workbooks though..

Thanks in advance.

B.




Sub ClearTables()Dim T As TableDef
DoCmd.SetWarnings False
For Each T In CurrentDb.TableDefs
If T.Name Like "Term*_*" Then
DoCmd.RunSQL "DELETE * FROM " & T.Name
End If
Next T
DoCmd.SetWarnings True

End Sub

[jonh]

With CurrentDb.OpenRecordset("select [name] from msysobjects where connect like 'excel *'")
Do Until .EOF
CurrentDb.Execute "delete * from [Term" & .Fields(0) & "]"
CurrentDb.Execute "insert into [Term" & .Fields(0) & "] select * from [" & .Fields(0) & "]"
.MoveNext
Loop
End With

[Beatrix]

Hi Jonh

Thanks very much for the script. Sorry for the late response as I've thought I replied to this before.

Thanks again.
B.