Update an Existing Access Record from Excel

[dramnath1981]

Hi Hui & Friends,


I want to identify an existing record in Access and update a range of Blank fields with some value in Excel.


I tried for nearly 2 days but in vain. If anyone can help, it will be great help.


To give you more information, This is like a stage by stage application flow. 1st Team will update some 5 fields, which will be exported to Access as a new record and the next team will pick the existing record and update their own fields (may be 10 more fields) and the cycle will go on till when the application is finally processed.


I have the below query, but I don't know how to write a range of fields in to Access by selecting a particular record.


Sub update()

Dim cn As Object

Dim rs As Object


strFile = "C:Documents and Settingsramnath.divakaranDesktopMy Database2.mdb"

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon


strSQL = "UPDATE Table1 SET Table1.Currency = 'EUR'where Account_Number = '1234567890'"


Sheet1.Range("D2") & ")"

cn.Execute strSQL

End Sub


Regards,

Ramnath

 

[vijaySharma]

Ramnath,


How many records are present in the excel sheet that you need to update in the access database. Are you searching for a specific account number in the access database and then update as necessary?


Also..... this line seems to be incorrect Sheet1.Range("D2") & ")"


You can loop through D2 till the last record and update your access database by using the Update statement as above.


~Vijay

 

[dramnath1981]

Hi Vijay,


Thanks a lot for your kind reply. I got it right. Its only one record.


Regards,

Ramnath