I need help please! Run-time error '3709':

[cjohnson78]

I need help please. I can't figure out how to UPDATE a record in
Access 2010 using an Excel 2010 userform.

Run-time error '3709':
The connection cannot be used to perform this operation.
It is either closed or invalid in this context.

Dim Cn As ADODB.Connection, _
Rs As New ADODB.Recordset, _
oCm As ADODB.Command, _
iRecAffected As Integer

On Error GoTo ADO_ERROR

Set Cn = New ADODB.Connection

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Chris Johnson\My Documents\SCID Base.accdb;Persist Security Info=False"

Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic
Rs.Open "SELECT * [Technican_Name] FROM [Service Board] WHERE [Technician_Name].Text = ' & Call Holding 1 & '"

With Rs
Rs.Fields(1).Value = frmQueServiceCall.txtDateReceived.Value
Rs.Fields(2).Value = frmQueServiceCall.txtTimeReceived.Value
.Update

End With

oCm.Execute iRecAffected
If iRecAffected = 0 Then
MsgBox "No records inserted"
End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing
If Not Rs Is Nothing Then Set Rs = Nothing

ADO_ERROR:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear

Resume Next

End If

End Sub

Thank you in advance to any that reply.

Ok so I added Rs.ActiveConnection = Cn to my code but now i get a new error.

Run-time error '-214721790(8004e14)':
syntax error(missing operator)in query expression'*[Technician_Name]'

 

[sparks80]

I'm not an expert, but I don't think you can open a recordset like that from Excel.

I think you will need to do something like:

dim strSQL as String
strSQL = SELECT * [Technican_Name] FROM [Service Board] WHERE [Technician_Name].Text = ' & Call Holding 1 & '"
 set rs = cn.execute(strSQL)

Alternatively you could use an ADODB.Command object:

dim cmd as new ADODB.Command
cmd.Activeconnection = cn
cmd.CommandText = strSQL
rs.open cmd

It would help for future queries if you could specify where the error occured!

Hope this helps

 

[cjohnson78]

Thanks for the reply sparks80.

Sorry I didn't specify where the error was coming from. I totaly spaced
on that. I've made the changes you suggested. I'm getting a new error now. Not sure how the code is to be written where the errors are. I'm a rookie with SQL and barely 7 months experience self taught with VBA so I apologize.

Run-time error '424':
Object required


Dim Cn As ADODB.Connection, _
Rs As ADODB.Recordset, _
oCm As ADODB.Command, _
StrSQL As String, _
iRecAffected As Integer

On Error GoTo ADO_ERROR

Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Chris Johnson\My Documents\SCID Base.accdb;Persist Security Info=False"

StrSQL = "SELECT * [Technician_Name], [Date_Received], [Time_Received] FROM [Service Board] WHERE [Technician_Name].Text = ' Call Holding 1 '"

With Rs

StrSQL = [Date_Received].Text = frmQueServiceCall.txtDateReceived.Value 'ERROR HERE

StrSQL = [Time_Received].Text = frmQueServiceCall.txtTimeReceived.Value 'ERROR HERE

Set Rs = Cn.Execute(StrSQL)

End With

oCm.Execute iRecAffected

If iRecAffected = 0 Then
MsgBox "No records inserted"
End If

If Cn.State <> adStateClosed Then
Cn.Close
End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing
If Not Rs Is Nothing Then Set Rs = Nothing

ADO_ERROR:

If Err <> 0 Then
MsgBox Err.Description
Err.Clear

Resume Next

End If

End Sub

 

[cjohnson78]

Also I think there is a problem with Run-time error '-214721900(8004e14)'
Syntax error(missing operator) in query expression '* [Technicia_Name]'.

It highlights Set Rs = Cn.Execute(StrSQL)

But this is when I put the Set Rs right after StrSQL = "SELECT...