Sending data from Excel to Access

[kurtc]

Morning all and hi from a new member. I've come here for a bit of guidance really. I'm fairly new to VBA coding (as in the last couple of weeks i've started to learn), main reason being there are things in my job that now require it.

I've had some success in the last few days creating user forms, logging data and sending it to an access database but there is something i'd like to do now that has me a little stumped.

The task i want to perform could be done manually transferring data from one place to another, but i want it to be done using code as it will help me get to grips with coding and be something that i can show that i have learnt and built.

What i have is a list of staff names in a sheet in excel, it has their full name in one column and ther staff number in the next column. The company uses a database in access to keep track of staff but does not log their staff number as yet. What i want to do is put some code together that will search the access database, find the names of people in my excel list and then put the relevant staff number next to the correct person. I want to put a button into the sheet that when clicked performs this operation.

I'm not looking for exact coding or an exact procedure, only looking for a start off point, what i should be looking at etc

Many thanks for any help

KurtC

 

[baitmaster]

welcome to the board

various angles covered at this link. Feel free to ask something specific if you want

Basically, once you have your data held in Excel, create an ADODB link to Access, use SQL to find records held there, and then update as required

 

[kurtc]

thanks for that BM, i'm looking through it and it does seem to be similar to what i'm looking for, but alot of it i'm struggling to understand. i will give it a good read over though and see what i can take from it

 

[kurtc]

ok coming back to this now, have some code which in my opinion looks right but running it throws out a 3061 error in the "Set rs" line. any tips or advice?

Private Sub cmdPopulate_Click()
Dim Db As DAO.Database, rs As DAO.Recordset
Set Db = OpenDatabase("\\GB0000KMRedir01.hbeu.adroot.hsbc\Redir01\a4072878\Desktop\db1.mdb")
dbopen = True
TableName = "tblstaff"

Dim manSQL As String
x = 1
manSQL = "WHERE tblstaff.[field5] = " & Sheet1.Range("b" & x) & " "

Set rs = Db.OpenRecordset("SELECT * FROM " & TableName & " " & manSQL)

rs.MoveFirst
rs.MoveLast

Do While Len(Range("c" & x).Formula) > 0
With rs
.Requery
.Edit
.Fields("Field5") = Sheet1.Range("C" & x).Value
.Update
rs.MoveNext
End With

x = x + 1
Loop

MsgBox "done"

End Sub

 

[Andrew Poulsom]

Maybe you need some single quotes:

manSQL = "WHERE tblstaff.[field5] = '" & Sheet1.Range("b" & x) & "'"

 

[kurtc]

Maybe you need some single quotes:

manSQL = "WHERE tblstaff.[field5] = '" & Sheet1.Range("b" & x) & "'"

think i'm just scratching the surface of how picky this coding can be! ok i've run the macro with those in it and now it is throwing up a 3021 error saying "no current record" over the "rs.movefirst" part. what does this mean?

 

[Andrew Poulsom]

Sorry, I'll have to pass on that one.

 

[kurtc]

Sorry, I'll have to pass on that one.

i appreciate the initial help! literally feel like im walking around blindfolded at the moment!