Issue executing Currentdb.execute in Excel / VBA

Question

Here is what I am trying to do with excel / access 2007. I have a table in Access. I am trying to create a temporary table to store the results of a query stored in the string strSQL_Insert. However when I try to execute the DoCmd.RunSQL,I get a run time error '424'. "Object required". I tried executing the query itself and getting the results into excel and that seemed to work ok. Here is what I have

Sub GetDataADO()

Dim dbConnection As Object
Dim dbRecordSet  As Object
Dim strSQL As String
Dim dbFileName As String
Dim DestinationSheet As Worksheet
Dim strTable As String
Dim strSQL_Insert As String

strTable = "tblTempTest"

Set dbConnection = CreateObject("ADODB.Connection")
Set dbRecordSet = CreateObject("ADODB.Recordset")

dbFileName = "H:\Unscan.accdb"

dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" _
& dbFileName & ";Persist Security Info=False;"

Set DestinationSheet = Worksheets("Sheet2")

With dbConnection
     .Open
 End With

strSQL_Insert = "INSERT INTO tblTempTest " _
& "SELECT TOP 10 BranchNumber , COUNT(*) as Total FROM BUDetails " _
& "GROUP BY BranchNumber ORDER BY 2 DESC ;"

CurrentDb.Execute strSQL_Insert

dbRecordSet.Close
dbConnection.Close

Set dbRecordSet = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing


Exit Sub

End Sub

Any Help will be greatly appreciated.

Welcome to Stack Overflow! Please be sure to read the helpful descriptions that pop up when selecting tags. — Charles

Jimmy Smith Jimmy Smith · Accepted Answer · 2014-01-28T19:22:04

If you are running this from within Excel, the CurrentDb may not be instantiated. Try to replace CurrentDB with dbConnection and see if this yields any results.

Issue executing Currentdb.execute in Excel / VBA

1 Answers