update and delete single access record set via excel vba

Question

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.

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.

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

Answer 1

I agree as to the point of modifying the data in excel as opposed to directly in access however I just spotted that your SQL statement seems to be slightly wrong. Try changing

WHERE EIM.NBKID=('" & Sheet2.Range("D4")

to

WHERE EIM.NBKID=" & Sheet2.Range("D4")

Note the removal of the single quote mark as the original statement would have been seen as

WHERE EIM.NBKID=('123456

Answer 2

You are missing a space between the bracket and where:

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

The single quotes are needed if NBKID is a text field:

WHERE EIM.NBKID=('" & Sheet2.Range("D4") & "'"

Or for a numeric field:

WHERE EIM.NBKID=(" & Sheet2.Range("D4") 

I doubt that this is a date field, but if it was, it would take hash (#) delimiters.

Answer 3

Just as a point of efficiency, I am not sure why you actually opened the recordset at all. The cn.Execute line works very well by itself.

I would also write a function you could call and pass it parameters to, depending on what you are trying to do. If you passed in the ID, the strFileName and the strSQL, then you have an all-purpose function.

I have a prebaked SQL string somewhere on a key pair "SELECT * from tblFoo WHERE tblFoo.ixFoo = {key}". I then get the parameter and do a Replace on the strSQL to insert the key.

Sub Update(strFile as string, strKey As String, strPerson as string)
Dim cn As Object : Set cn = CreateObject("ADODB.Connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
cn.Open strCon
strSQL = "UPDATE EIM SET EIM.Person#="{person}" WHERE EIM.NBKID={key}"
strSQL = Replace(strSQL, {person}, strPerson, 1)
strSQL = Replace(strSQL, {key}, strKey, 1)
cn.Execute strSQL
End Sub

If speed of code greater than need for speed of execution (which is what I can gather here), then this approach alleviates all the quote/doublequote confusion

I would set aside a sheet in the book that had all the strSQL statements there, if you had a few. You could even pass this in and do the replace within Excel with its much faster native execution speed.

You now have a fucntion you can pick up and reuse. If you are serious about using VBA, reuse heavily. It saves so much time. VBA jobs tend to place a high amount of pressure on time to produce.

I presume that you have removed error trapping to save us the hassle of reading it.