Transferspreadsheet Using Vb6 (1 Viewer)

[Elvis]

I want to be able to press one button on a VB form which, deletes the contents of a table then asks for the path to an Excel spreadsheet, then updates the table with the contents of the Spreadsheet. I have been able to get this to work if I have the database open, however if the database is closed the process fails when tyring to do the following command:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ImportTab, ImportPath, True

complains about the function needing the database to be open.

I have tried opening the database from the VB code but everything I have tried does not seem to work. I open the database with ADO to delete the contents of the table but the Docmd command does not seem to see the database as open.

Any help would be much appreciated.

 

[Elvis]

Managed to get this to work by entering the following code to open the database and run the DoCmd.Transferspreadsheet command:

Dim oAccess As Access.Application
Set oAccess = New Access.Application
oAccess.OpenCurrentDatabase "C:\MyDB.mde"
oAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ImportTab, ImportPath, True
oAccess.CloseCurrentDatabase
oAccess.Quit acQuitSaveNone
Set oAccess = Nothing