Voici une solution possible
'Déclarations des variables
Dim tblReponse2 As TableDef, rstTableImport As Recordset
Dim dbsSrc, dbsDest As Database
dim strDestFile as string , strNomBase as string
Const strNomBaseCentral = "MaBaseDeDonnées.mdb"
'*************************************************
Sub Macro1
strDestFile = ActiveWorkbook.Path & "\" & strNomBase
Set dbsDest = OpenDatabase(strDestFile, dbDriverNoPrompt, False, "MS ACCESS;PWD=AlfredSirven")
Set rstTableImport = MaDB.OpenRecordset("tblReponse2") 'Ouvre la table tblReponse2 dans un objet Recordset
With Sheets(Réponse)
.Cells.Clear
For i = 0 To TableImport.Fields.Count - 1 'Copie les noms en en-tête
.Range("IT1").Cells(1, i + 1).Value = TableImport.Fields(i).Name
Next
.Range("IT2").CopyFromRecordset rstTableImport 'Importe les données
End With
rstTableImport.close 'Ferme le Recordset
dbsDest.close'Ferme la base Access
end sub
Cordialement