executing SQL in workbook

Question

I am unable to execute a simple sql statement. - sorry I am sure this is simple and I am missing something small. Error says missing the object.

dbPath = "C:\Users\User\Documents\test0419.accdb"
tblName = "Wait_Data_Table"
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';"

conn.Open strcon

Set wrkSpaceNew = DBEngine.CreateWorkspace("Check", "admin", "", dbUseJet)

rcdDetail = ("SELECT order.ord_id, order.job_id, order.bc_desc, order.ord_amount, order.ord_diff FROM Order")

Set rs = DBEngine.BeginTrans(rcdDetail)

`BeginTrans` is to start a transaction. `OpenRecordset` is what you need. — Gustav, May 06 '21 at 16:41
My goal is to run a simple sql querry on a local dB. Dim conn As New ADODB.Connection, rs As New ADODB.Recordset, dbPath As String Dim rcdDetail As String Dim strcon As String, Dim rs2 As Recordset Dim wrkSpaceNew As Workspace Dim obj1 As Object Dim obj2 As Object Dim db As DAO.Database dbPath = "C:\Users\User\Documents\test0419.accdb" strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';" conn.Open strcon Set wrkSpaceNew = DBEngine.CreateWorkspace("Check", "admin", "", dbUseJet) rcdDetail = ("SELECT order.ord_id, order.ord_diff FROM Order") Set rc2 = — Dave, May 06 '21 at 18:16

score 1 · Answer 1 · answered May 07 '21 at 11:53

You can use either DAO or ADODB, not normally both. See difference-between-ado-and-dao

Option Explicit

Sub UseDAO()
  
   Const SQL = " SELECT order.ord_id, order.job_id, order.bc_desc, " & _
               "        order.ord_amount, order.ord_diff " & _
               " FROM [Order]"
     
   Const dbpath = "C:\Users\User\Documents\test0419.accdb"
  
   Dim wkspace As workspace, db As DAO.Database, rs As DAO.Recordset
   Set wkspace = DBEngine.CreateWorkspace("Check", "admin", "", dbUseJet)
   Set db = wkspace.OpenDatabase(dbpath)
   
   Set rs = db.OpenRecordset(SQL)
   Sheet1.Range("A1").CopyFromRecordset rs
   db.Close
   Set db = Nothing
   wkspace.Close
   Set wkspace = Nothing
  
   MsgBox "Results on sheet " & Sheet1.Name, vbInformation, "DAO"

End Sub

Sub UseADODB()
  
   Const SQL = " SELECT order.ord_id, order.job_id, order.bc_desc, " & _
               "        order.ord_amount, order.ord_diff " & _
               " FROM [Order]"
     
   Const dbpath = "C:\Users\User\Documents\test0419.accdb"
  
   Dim strConn As String, conn As ADODB.Connection, rs As ADODB.Recordset
   strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbpath & "';"
   Set conn = New ADODB.Connection
   conn.Open strConn

   Set rs = conn.Execute(SQL)
   Sheet1.Range("A1").CopyFromRecordset rs
   conn.Close
   
   MsgBox "Results on sheet " & Sheet1.Name, vbInformation, "ADODB"

End Sub

What would cause a run time error 424 missing object? We get past the connection It hangs at the Sheet1.Range("A1").CopyFromRecordser rs. I stepped through a couple times watching and it was fine. — Dave, May 07 '21 at 14:31
When commented out it errors out at then msgBox - object required — Dave, May 07 '21 at 15:10
@Dave I'm guessing you don't have Option Explicit at the start of your code and you have deleted Sheet1. Use another sheet. — CDP1802, May 07 '21 at 15:23
I was under the understanding that Option Explicit assists when defining variable but how did that effect the object? — Dave, May 07 '21 at 17:47
@Dave With Option Explicit you should get a compiler error "Variable not defined" if Sheet1 did not exist. — CDP1802, May 07 '21 at 18:06

score 0 · Answer 2 · answered May 06 '21 at 22:37

Don't forget to add Activex Data Object in Reference Section

Dim baglanti As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sunucu, veritabani, id, sifre, sorgu As String

sunucu = "DESKTOP-GF32DLC\SQLEXPRESS"
veritabani = "AdventureWorks2014"
id = ""
sifre = ""
sorgu = "SELECT * FROM [HumanResources].[Department] Where [GroupName]='Manufacturing'"

baglanti.Open "Driver={SQL SERVER};Server=" & sunucu & ";Database=" & veritabani & _
";Uid=" & id & ";Pwd=" & sifre & ";"

rs.Open sorgu, baglanti, adOpenStatic

With Range("A1:AA1000")
.ClearContents
.CopyFromRecordset rs
End With

rs.Close
baglanti.Close

This way you can make a healthy sql connection. I used to use it to connect sql in the past

executing SQL in workbook

2 Answers2