exporting from project to access

[samuelimtech]

hi all,

i cant see whats wrong with the below code, access launches as it should the tasks in project are picked up but when i open the database there are no records in the table.
all the spellings are the same and the table exists with the field name which ive simply reduced to Name and ID for simplicity.

can anyone spot whats wrong? thanks all.



Sub MSProjectUpload()

Dim cn As Object 'ADODB.Connection
Dim rs As Object 'ADODB.Recordset
Dim tsK As Task
Dim dbPath As String
Dim i As Integer
On Error Resume Next


dbPath = "XX/Local Tool.accdb" 'this is where the data is to be sent
'+++Connect to Access Database+++
Set cn = CreateObject("ADODB.Connection") 'New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & dbPath & ";"


'+++Open the Recordset+++
Set rs = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
rs.Open "tbl_New_Upload"


'+++Begin Task Retrieval+++
For Each tsK In ActiveProject.Tasks
i = i + 1
With rs
.AddNew ' add values to each field in the record
' .Fields("UID") = tsK.UniqueID
' .Fields("Task_ID") = tsK.ID
' .Fields("WBS") = tsK.WBS
.Fields("Name") = tsK.Name

' .Fields("Finish") = tsK.Finish

' add more fields if necessary...
.Update ' stores the new record
End With
Next tsK


'+++CleanUp+++
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

[jonh]

You've opened a connection but it's not used.


cn.Open
rs.Open "some sql", cn

or

rs.ActiveConnection = cn
rs.Open "some sql"