VBA - Compact and repair Access 2016 Database from an excel macro

[RuiFlora]

Hi Guys,

I am trying to copy data from excel to Access with ADODB and then in the end i want to compact and repair the database.
The copy process is working fine but i am getting an error for the Compact/repair part. I have the following:

'--------------------------------------
Dim dbPath As String, scn As String, Pathwb as String
Dim Catalog As Object
Dim cn As ADODB.connection

dbPath = Pathwb.Path & "\Flatfile.accdb"
dbPathCompacted = Pathwb.Path & "\NewFlatfile.accdb"

scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

Set Catalog = CreateObject("ADOX.Catalog")

Catalog.Create scn
Set Catalog = Nothing

Set cn = New ADODB.connection

With cn
.Open scn
.Execute "CREATE TABLE Flatfile 'Creates the db fields.....
End With

TheSQL = "INSERT INTO Flatfile 'SQL to insert into...




cn.Execute TheSQL
cn.Close

DBEngine.CompactDatabase dbPath, dbPathCompact -----> Here I am getting the error "3343: Unrecognized database format (...) \Flatfile.accdb

End Sub
'----------------------------------------------------

I looked over and over the forums about a solution but it goes down to JetEngine which is not supported in latest Access versions. I am stuck.

Thanks a lot if someone has an idea how to solve this.

Cheers!