StoreProcedure not working for Excel to Access Update

[zonexs123]

Hi Everyone,

Just stuck in the middle of my project. What I'm trying to do here is, update my access table ("Table1") from excel sheet ("Myexcel.xls"). Excel file contains all the information however access table is not updated except "RefNo" field.

Condition what I coded here is when once connection is build, "RefNo" in excel matched with "RefNo" of access table , update the other fields in Access table.

Here is my code: Which is working fine (doesn't showing any error) but still data is not updated in access table. I don't know why is not updating it....

****************************************************
Private Sub Command0_Click()
Dim accessCMD As ADODB.Command
Dim accessRS As ADODB.Recordset
Dim accessParam As ADODB.Parameter

Dim bFound As Boolean

Dim strRefNo As String
Dim strInsured As String
Dim strBusiness As String
Dim updatedCount As Integer

Dim strQuery As String

Dim excelConn As ADODB.Connection
Dim excelRS As ADODB.Recordset

'open excel connection
Set excelConn = New ADODB.Connection
With excelConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & Text2.Value & ";" & "Extended Properties=Excel 8.0;"
.Open

'read data from excel
strQuery = "SELECT * FROM [Sheet1$]"
Set excelRS = excelConn.Execute(strQuery)

'start from 0
iUpdatedCount = 0

'loop records in excel
Do While Not excelRS.EOF

'fetch refno from 0 column
strRefNo = excelRS.Fields(0).Value
strInsured = excelRS.Fields(1).Value
strBusiness = excelRS.Fields(2).Value
'if refno is blank, it could be end of file
If strRefNo = "" Then
Exit Do
End If

'testing only
MsgBox strRefNo
MsgBox strInsured
MsgBox strBusiness

'create objects to read / write data to access
Set accessCMD = New ADODB.Command
Set accessRS = New ADODB.Recordset
Set accessParam = New ADODB.Parameter

'check if this refno exists in Access
With accessCMD
.ActiveConnection = CurrentProject.Connection
.CommandText = "qryRefNo"
.CommandType = adCmdStoredProc
.Parameters.Refresh


'pass refno read from excel as a parameter to access query
Set accessParam = .CreateParameter("[@RefNo]", adVarChar, adParamInput, 20)
.Parameters.Append accessParam
.Parameters("[@RefNo]") = strRefNo
End With

'load the query result in ADO RecordSet
accessRS.Open accessCMD
If accessRS.EOF = False Then
'record exists, we need to update it
bFound = True
Else
bFound = False
End If
accessRS.Close
Set accessCMD = Nothing

'since refno was found
If bFound = True Then

'recreate objects to use for updating access table now
Set accessCMD = New ADODB.Command
Set accessParam = New ADODB.Parameter
Set accessParam1 = New ADODB.Parameter
Set accessParam2 = New ADODB.Parameter

With accessCMD
.ActiveConnection = CurrentProject.Connection
.CommandText = "qryUpdate"
.CommandType = adCmdStoredProc
.Parameters.Refresh

Set accessParam = .CreateParameter("[@RefNo]", adVarChar, adParamInput, 100)
.Parameters.Append accessParam
.Parameters("[@RefNo]") = strRefNo

Set accessParam1 = .CreateParameter("[@InsuredName]", adVarChar, adParamInput, 100)
.Parameters.Append accessParam1
.Parameters("[@InsuredName]") = strInsured 'field1

Set accessParam2 = .CreateParameter("[@Business]", adVarChar, adParamInput, 50)
.Parameters.Append accessParam2
.Parameters("[@Business]") = strBusiness 'field2
End With

'execute this parameterized query
Set accessRS = accessCMD.Execute

'clean up the local objects
Set accessParam = Nothing
Set accessParam1 = Nothing
Set accessParam2 = Nothing
Set accessCMD = Nothing
'increase updated row count
iUpdatedCount = iUpdatedCount + 1
End If

'move to next record in excel
excelRS.MoveNext
Loop

'close excel recordset
excelRS.Close

'close excel connection
.Close
End With

Set excelConn = Nothing
End Sub

*****************************************

Appreciate your all help and support

Many thanks,
Santosh

 

[JHB]

Why are you not importing all the data from the Excel sheet into the database and then run a update query?

 

[zonexs123]

Thanks JHB.... yeah there are alternative which can solve my problem however I'm learning VBA... so would like to do in this way....

Can anyone, help me out in my code....

 

[JHB]

You've to reorganise the parameters so it fit with your query.

UPDATE Table1 SET Business = [@Business], InsuredName = [@InsuredName]
WHERE RefNo=[@RefNo];

Set accessParam = .CreateParameter("[@Business]", adVarChar, adParamInput, 50)
.Parameters.Append accessParam
.Parameters("[@Business]") = strBusiness 'field2
Set accessParam1 = .CreateParameter("[@InsuredName]", adVarChar, adParamInput, 100)
.Parameters.Append accessParam1
.Parameters("[@InsuredName]") = strInsured 'field1
Set accessParam2 = .CreateParameter("[@RefNo]", adVarChar, adParamInput, 100)
.Parameters.Append accessParam2
.Parameters("[@RefNo]") = strRefNo