By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

update and delete single access record set via excel vba

P: 7
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.
Expand|Select|Wrap|Line Numbers
  1. Sub update()
  2. Dim cn As Object
  3. Dim rs As Object
  4. Dim a As String
  5. strFile = "D:\temp excel\EIM.mdb"
  6. strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
  7. Set cn = CreateObject("ADODB.Connection")
  8. Set rs = CreateObject("ADODB.Recordset")
  9. cn.Open strCon
  10. a = Sheet2.Range("D4")
  11. strSQL = "SELECT * FROM EIM WHERE EIM.NBKID=" & a
  12. rs.Open strSQL, cn
  13.  
  14. strSQL = "UPDATE EIM SET EIM.Person#=('" & Sheet2.Range("D5") & "')WHERE EIM.NBKID=('" & Sheet2.Range("D4")
  15. cn.Execute strSQL
  16. End Sub

In the above code the file EIM has a table called EIM with NBKID and Person# fields.
Aug 2 '10 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 636
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

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '10 #2

P: 7
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."
Aug 2 '10 #3

P: 7
Thanks a ton - Problem resolved
Aug 2 '10 #4

Expert 100+
P: 636
@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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE EIM SET EIM.[Person#]=(" & Sheet2.Range("D5") & ") WHERE EIM.NBKID=(" & Sheet2.Range("D4") & ")" 
Note: no apostrophies.

MTB
Aug 2 '10 #5

Expert 100+
P: 636
@anand padia
Glad you solved the problem.

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

MTB
Aug 2 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.