phendrena
06-04-2009, 12:26 AM
Hello Everyone,
I'm a little baffled.
I am getting a 'Data type mismatch....' error when passing data from Excel into Access. I can understand why this would be the case as ALL the fields in the Access DB are set as Text fields and I'm not trying to pass anything other than text values into Access.
The following is the code that I am using.
This is the 'Update Selected Record' code that i'm having problems with. The normal 'Data Transfer' works fine and other 'Update' routines that I have also work correctly.
'---------------------------------------'
'-- Update Data sUpdateData --'
'---------------------------------------'
Private Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
On Error GoTo ErrHandler
sConnect = glob_sConnect
sSQL = "SELECT * From [RSA Errors]"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE [RSA Errors] " & _
"SET [RSA Errors].GenuineError = '" & Me.cboError.Text & "' " & _
",[RSA Errors].AmendedBy = '" & Application.UserName & "' " & _
",[RSA Errors].AmendmentComments = '" & Me.txtErrorComments.Text & "' " & _
"WHERE [RSA Errors].[ID] = '" & Me.txtID.Value & "'"
' "AND [RSA Errors].[PolicyRegNo] = '" & Me.txtPolRegNo.Value & "'"
oRS.ActiveConnection.Execute sSQL
sSQL = "SELECT * From [RSA Errors]"
oRS.ActiveConnection.Execute sSQL
ary = oRS.GetRows
End If
oRS.Close
Set oRS = Nothing
Exit Sub
ErrHandler:
MsgBox "Error : " & err.Description & " " & sSQL, vbExclamation
End Sub
Is anyone able to suggest where I am going wrong with this one?
Thanks,
I'm a little baffled.
I am getting a 'Data type mismatch....' error when passing data from Excel into Access. I can understand why this would be the case as ALL the fields in the Access DB are set as Text fields and I'm not trying to pass anything other than text values into Access.
The following is the code that I am using.
This is the 'Update Selected Record' code that i'm having problems with. The normal 'Data Transfer' works fine and other 'Update' routines that I have also work correctly.
'---------------------------------------'
'-- Update Data sUpdateData --'
'---------------------------------------'
Private Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
On Error GoTo ErrHandler
sConnect = glob_sConnect
sSQL = "SELECT * From [RSA Errors]"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE [RSA Errors] " & _
"SET [RSA Errors].GenuineError = '" & Me.cboError.Text & "' " & _
",[RSA Errors].AmendedBy = '" & Application.UserName & "' " & _
",[RSA Errors].AmendmentComments = '" & Me.txtErrorComments.Text & "' " & _
"WHERE [RSA Errors].[ID] = '" & Me.txtID.Value & "'"
' "AND [RSA Errors].[PolicyRegNo] = '" & Me.txtPolRegNo.Value & "'"
oRS.ActiveConnection.Execute sSQL
sSQL = "SELECT * From [RSA Errors]"
oRS.ActiveConnection.Execute sSQL
ary = oRS.GetRows
End If
oRS.Close
Set oRS = Nothing
Exit Sub
ErrHandler:
MsgBox "Error : " & err.Description & " " & sSQL, vbExclamation
End Sub
Is anyone able to suggest where I am going wrong with this one?
Thanks,