Phelony
05-30-2013, 05:00 AM
Hi Guys
I've started working with Workflows to automate a number of tasks within SharePoint, as part of this, we need to extract data from current reporting processes and transplant it into SharePoint Lists.
I've got the data out of SharePoint via a linked table, taken what I need from it and need to put it back, however, one of the fields is a "Name" field which in SharePoint is a "Person or Group" field which Access reinterprets as a "number", the default for a Lookup Field.
Naturally, the data in this field is text...so the basic Append Query I wanted to run hit a Conversion Error and fell over.
However, the data can easily be manually cut and paste from the Access native table (Export Data) to the SharePoint List (Unposted Hours Data) without incident.
I attempted to throw together some VBA to replicate this automatically, however it keeps hitting the same conversion issue.
I have no doubt that the below code isn't the best way of doing it (Access and I have long been mortal enemies), but wondered if someone could point me in the right direction or perhaps let me in on the secret of how this issue is overcome (Google has not been kind in this regard either!)
Any :help is greatly appreciated.
Phel
Sub datacopy()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("unposted hours data", dbOpenDynaset)
Set rs2 = db.OpenRecordset("export data", dbOpenDynaset)
rs.AddNew
rs.Fields("Title") = rs2.Fields("Title")
rs.Fields("Name") = rs2.Fields("Name")
rs.Fields("M Resource Location") = rs2.Fields("M Resource Location")
rs.Fields("Current Month") = rs2.Fields("Current Month")
rs.Fields("Historic") = rs2.Fields("Historic")
rs.Fields("M Resource Team") = rs2.Fields("M Resource Team")
rs.Fields("M Resource Grade") = rs2.Fields("M Resource Grade")
rs.Fields("M Resource Group") = rs2.Fields("M Resource Group")
rs.Fields("Reminder Counter") = rs2.Fields("Reminder Counter")
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
I've started working with Workflows to automate a number of tasks within SharePoint, as part of this, we need to extract data from current reporting processes and transplant it into SharePoint Lists.
I've got the data out of SharePoint via a linked table, taken what I need from it and need to put it back, however, one of the fields is a "Name" field which in SharePoint is a "Person or Group" field which Access reinterprets as a "number", the default for a Lookup Field.
Naturally, the data in this field is text...so the basic Append Query I wanted to run hit a Conversion Error and fell over.
However, the data can easily be manually cut and paste from the Access native table (Export Data) to the SharePoint List (Unposted Hours Data) without incident.
I attempted to throw together some VBA to replicate this automatically, however it keeps hitting the same conversion issue.
I have no doubt that the below code isn't the best way of doing it (Access and I have long been mortal enemies), but wondered if someone could point me in the right direction or perhaps let me in on the secret of how this issue is overcome (Google has not been kind in this regard either!)
Any :help is greatly appreciated.
Phel
Sub datacopy()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("unposted hours data", dbOpenDynaset)
Set rs2 = db.OpenRecordset("export data", dbOpenDynaset)
rs.AddNew
rs.Fields("Title") = rs2.Fields("Title")
rs.Fields("Name") = rs2.Fields("Name")
rs.Fields("M Resource Location") = rs2.Fields("M Resource Location")
rs.Fields("Current Month") = rs2.Fields("Current Month")
rs.Fields("Historic") = rs2.Fields("Historic")
rs.Fields("M Resource Team") = rs2.Fields("M Resource Team")
rs.Fields("M Resource Grade") = rs2.Fields("M Resource Grade")
rs.Fields("M Resource Group") = rs2.Fields("M Resource Group")
rs.Fields("Reminder Counter") = rs2.Fields("Reminder Counter")
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub