As a preference, I have 3 items in my Access file for each drawing
that I need to pull over: Item No., Category and Description. Each
item will differ for each drawing. I am not sure how this code will
know which information goes with which drawing. Perhaps if someone
gets this code to work, than they can explain it further to me. I've
been banging my head against a wall with this one, so any help is
greatly appreciated!!
Here is the code that I borrowed from another user and am trying to
tweak for my own usage:
Sub test()
'Create SQL Query to extract records
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim Filename As String
Filename = "C:\Documents and Settings\lfine\Drawings.mdb"
rst.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51; " & _
"Data Source=" & Filename
rst.Source = "SELECT * FROM Test"
rst.Open Options:=adCmdText
rst.MoveFirst
While Not rst.EOF
'MsgBox (rst.Fields("RECord_Number"))
Dim File
File = Trim(rst.Fields("RECord_Number")) & ".tif"
Dim oDocument As PKMCDO.KnowledgeDocument
Set oDocument = New PKMCDO.KnowledgeDocument
Dim sPath As String
Dim sErr As String
On Error Resume Next
sPath = "http://aec-portal/Test_Workspace/Documents/Drawings/"
& File & _
""
oDocument.DataSource.Open sPath, , adModeReadWrite
If Err.Number = 0 Then 'Error if you don't have
permission or if
'document doesn't exist
'Bind to the WSS (web storage system) copy of the document
Dim oDoc1 As PKMCDO.KnowledgeDocument
Set oDoc1 = New PKMCDO.KnowledgeDocument
oDoc1.DataSource.Open sPath, , adModeReadWrite, , , "", ""
'Create KnowledgeVersions object. This will be used to
'Checkout, Checkin, and Publish the document from the WSS
Dim oVersion As PKMCDO.KnowledgeVersion
Set oVersion = New PKMCDO.KnowledgeVersion
'Check Out the document. Checking out a document places an
'editing reservation on the document
Dim oRS As ADODB.Recordset
Set oRS = oVersion.Checkout(sPath)
'Get the working copy name
'The urn used is = String property containing a URL to the
working copy
'of the document
Dim sWorkingCopy As String
sWorkingCopy = oRS.Fields("urn:schemas-microsoft-
com:publishing:WorkingCopy")
'Bind to the working copy of the document
Dim oDoc2 As PKMCDO.KnowledgeDocument
Set oDoc2 = New PKMCDO.KnowledgeDocument
oDoc2.DataSource.Open sWorkingCopy, , adModeReadWrite
'Set properties on object
oDoc2.Property("Item Number") = "" & rst.Fields("ITEM NO") & ""
oDoc2.Fields("urn:schemas-microsoft.com:office:office#Item
Number") = "" _
& rst.Fields("ITEM NO") & ""
oDoc2.Property("Category") = "" & rst.Fields("CATEGORY") & ""
oDoc2.Fields("urn:schemas-
microsoft.com:office:office#Category") = "" _
& rst.Fields("CATEGORY") & ""
oDoc2.Property("Product Description") = "" & rst.Fields
("DESCRIPTION") & ""
oDoc2.Fields("urn:schemas-microsoft.com:office:office#Product
Description") = "" _
& rst.Fields("DESCRIPTION") & ""
oDoc2.Fields.Update
'Save changes
oDoc2.DataSource.Save
'Checkin and publish document to WSS
'A checkin releases the editing reservaton placed on the
document when
'it was checked out, and updates the content of the document.
oVersion.Checkin sPath
'This method submits the document for publication. If there is
aWorkflow
'defined for the document, the workflow is initiated. If there
is
'noworkflow, the document is approved as the new default
version.
oVersion.Publish sPath
'Set oDoc1 = Nothing (not really required for visual basic)
'Set oDoc2 = Nothing
'Set oVersion = Nothing
'Set oRS = Nothing
End If
rst.MoveNext
Wend
rst.Close
MsgBox ("Finished")
End Sub
Private Sub Command1_Click()
Call test
End Sub