update and delete single access record set via excel vba

[anand padia]

I have this tool where employee information needs to be updated. I call in the MDB data to excel in one sheet. Now I use vlookup to see what is there and change it if needed.

I have tried some tricks however some thing seems to be wrong.. please help.

Sub update()
Dim cn As Object
Dim rs As Object
Dim a As String
strFile = "D:\temp excel\EIM.mdb"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
a = Sheet2.Range("D4")
strSQL = "SELECT * FROM EIM WHERE EIM.NBKID=" & a
rs.Open strSQL, cn
 
strSQL = "UPDATE EIM SET EIM.Person#=('" & Sheet2.Range("D5") & "')WHERE EIM.NBKID=('" & Sheet2.Range("D4")
cn.Execute strSQL
End Sub

In the above code the file EIM has a table called EIM with NBKID and Person# fields.

[MikeTheBike]

Hi

You have not said what 'some thing seems to be wrong' is??

Looking at the code my only guess (and in the absence of more info it is only a guess) is thet you try this mod

strSQL = "UPDATE EIM SET EIM.[Person#]=('" & Sheet2.Range("D5") & "') WHERE EIM.NBKID=('" & Sheet2.Range("D4") & "')"

Square brackets round Person#, a space between )WHERE and closing the last parentheses and apostrophe

??

ps what values are in D4 & D5

MTB

[anand padia]

So sorry about not explaining the request clearly, I have this tool which allows people to update there information. I cannot use access to manipulate it as not all have access available and even it would be available I do not want to give them access to master database.

We have more than 500 employee's when ever some one moves out of one role to another or when someone leaves the organization. A manager has to request for hierarchy report which takes time to come.

Instead of going through that I want this tool to maintain record of all the employees, here nbkid is nothing but system id and person# is employee number or id.

I have a code to update the information however if someone needs to edit it due to some change to their role than I need another set of code.

By doing it in excel it is easy to manage - no additional training.

I have this button which should update the change made to the vaules updated in excel sheet. I dont want it to check if the record change, I just want it to use update.

In range D4 - I allow the user to enter there nbkid(system login id - unique and primary key) & with D5 - I allow the user to enter the changed person no(employee id)

When I run the above code I get error saying "No value given for one or more reqired parameters."

[anand padia]

Thanks a ton - Problem resolved

[MikeTheBike]

@anand padia
Hi

When I run the above code I get error saying "No value given for one or more reqired parameters."

By the above code I assume you mean your origional post (not my suggestion?).

Also, I was asking what the actual values where/are (typically) in D4 & D5, not what they represented.

In addition, what is the data type of the NBKID and Person# fields in table EIM ??

If they are Numeric (as I now suspect) then you could try this

strSQL = "UPDATE EIM SET EIM.[Person#]=(" & Sheet2.Range("D5") & ") WHERE EIM.NBKID=(" & Sheet2.Range("D4") & ")" 

Note: no apostrophies.

MTB

[MikeTheBike]

@anand padia
Glad you solved the problem.

Fore the record, would you mind letting use know what the solution was/is?

MTB