My attempt at a Persistent connection from info I can find online. the connection is established when I open the workbook, but when I try retrieve data or close the workbook I get an error stating object required.
My connection code
Public objcnn As ADODB.Connection
Public Sub MySQL_Login()
Set objcnn = New ADODB.Connection
objcnn.Open "Driver={MySQL ODBC 9.1 ANSI Driver};Server=localhost; Database=******; UID=******; PWD=******"
End Sub
Private Sub Workbook_Open()
MySQL_Login
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' objcnn.Close
End Sub
When I run the following piece of code from a user form it fails on line 2
If ReportCon.Mpa_R.value <> "" And count = "1" Then
Set objRmp = objcnn.Execute("select * from con_res_hed where mpa = '" & mparep & "' and date_cast >= '" & startdate & "' and date_cast <= '" & enddate & "' order by report_nr;")
Sheets("ReportCon").Range("A8").CopyFromRecordset objRmp
Set objRmp = objcnn.Execute("Select weight1_7, weight2_7, weight3_7, dens1_7, dens2_7, dens3_7, mpa1_7, mpa2_7, mpa3_7, mpaA_7, weight1_28, weight2_28, weight3_28, dens1_28, dens2_28, dens3_28, mpa1_28, mpa2_28, mpa3_28, mpaA_28 " & _
"From con_res_data " & _
"Left join con_res_hed on con_res_data.report_nr = con_res_hed.report_nr " & _
"Where con_res_hed.mpa = '" & mparep & "' and date_cast >= '" & startdate & "' and date_cast <= '" & enddate & "' " & _
"Order by con_res_hed.report_nr;")
Sheets("ReportCon").Range("L8").CopyFromRecordset objRmp
ReportCon.Mpa_R.value = ""
ReportCon.Start_date.value = ""
ReportCon.End_Date.value = ""
ReportCon.Rep_search.Enabled = False
ReportCon.Report_Num.SetFocus
Else
2