PDA

View Full Version : Error when passing data from Excel into Access (Office '97)



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,

Bob Phillips
06-05-2009, 07:30 AM
Haven't done any testing on this. Any chance of the workbook and the DB?

phendrena
06-05-2009, 07:35 AM
Haven't done any testing on this. Any chance of the workbook and the DB?DB contains 15000+ records none of which could be kept in a public forum. I'll see about creating a stripped down version and post that.