MikeSpaans
New member
- Local time
- Today, 05:16
- Joined
- Jul 13, 2011
- Messages
- 3
Hello,
I have been searching the web and this forum extensively, but haven't found a real solution; so any thoughts on this are most welcome.
I'll try to give enough information here; maybe I'm on the wrong track tackling this ..
Case
I work for a publisher and am in the process of transferring the PDF's of our journal articles to the Dutch Royal Library. They require a XML file per article. I have been building a database for the articles and want to export the XML from there.
Goal
Export function that creates XML files per article (that part I sort of solved) with information from different tables. End results should looking something like this (given to me by the library):
Database
The DB consist of 3 [main] tables (article, authors, journal) linked through many-to-many relations via linking-tables. There's a form[frmMain] that uses the article table and has two sub forms with the information from the other tables: authors can have written multiple articles and an article can have more than one author. Just as one journal contains multiple articles.
I export from the form via this VBA code, where I already tried to use CreateAdditionalData:
That now generates this kind of files
Problems
I have been searching the web and this forum extensively, but haven't found a real solution; so any thoughts on this are most welcome.
I'll try to give enough information here; maybe I'm on the wrong track tackling this ..
Case
I work for a publisher and am in the process of transferring the PDF's of our journal articles to the Dutch Royal Library. They require a XML file per article. I have been building a database for the articles and want to export the XML from there.
Goal
Export function that creates XML files per article (that part I sort of solved) with information from different tables. End results should looking something like this (given to me by the library):
Code:
<Article>
<Journal>
<PublisherName>Informa Healthcare</PublisherName>
<Place>Leukemia and Lymphoma</Place>
<JournalTitle>Leukemia and Lymphoma</JournalTitle>
<IssueTitle>Titel van nummer</IssueTitle>
<Volume>51</Volume>
<Issue>2</Issue>
</Journal>
<ArticleTitle>Src-family kinases in the development and therapy of Philadelphia
chromosome-positive chronic myeloid leukemia</ArticleTitle>
<ActicleID>LS5102</ActicleID>
<FirstPage>12</FirstPage>
<LastPage>44</LastPage>
<Language>EN</Language>
<AuthorList>
<Author>
<FirstName>Shaoguang</FirstName>
<LastName>Li</LastName>
</Author>
<Author>
<FirstName>Rianne</FirstName>
<LastName>Koning</LastName>
</Author>
</AuthorList>
</Article>
The DB consist of 3 [main] tables (article, authors, journal) linked through many-to-many relations via linking-tables. There's a form[frmMain] that uses the article table and has two sub forms with the information from the other tables: authors can have written multiple articles and an article can have more than one author. Just as one journal contains multiple articles.
I export from the form via this VBA code, where I already tried to use CreateAdditionalData:
Code:
Private Sub Export_Click()
Dim rsR As DAO.Recordset
Set rsR = CurrentDb.OpenRecordset("tblArticle", dbOpenSnapshot)
Set objOtherTbls = Application.CreateAdditionalData
' Add the tables to the data to be exported.
objOtherTbls.Add "tblAuthor"
Do Until rsR.EOF
Application.ExportXML acExportForm, "frmMain", _
Application.CurrentProject.Path & "\" & rsR.Fields("ArticleID").Value & ".xml", , , , , , _
"[ArticleID] = " & rsR.Fields("ArticleID").Value, objOtherTbls
rsR.MoveNext
Loop
rsR.Close
End Sub
Code:
<tblArticle>
<ArticleID>1</ArticleID>
<ArticleTitle>Vrije tijd in het Rome van de Keizertijd. Plebs en het feest</ArticleTitle>
<FirstPage>3</FirstPage>
<LastPage>23</LastPage>
<Language>1</Language>
<PublicationType>1</PublicationType>
<PubState>OpenAccess</PubState>
</tblArticle>
<tblAuthor>
<AuthorID>1</AuthorID>
<AuthorFirstName>Anita</AuthorFirstName>
<AuthorLastName>Boonekamp-Van Iterson</AuthorLastName>
</tblAuthor>
<tblAuthor>
<AuthorID>2</AuthorID>
<AuthorFirstName>Hans</AuthorFirstName>
<AuthorLastName>Wijn</AuthorLastName>
</tblAuthor>
<tblAuthor>
<AuthorID>3</AuthorID>
<AuthorFirstName>Spaans</AuthorFirstName>
<AuthorLastName>Mike</AuthorLastName>
......
</tblAuthor>
</dataroot>
- The exported XML files do not only contain the authors of that article but the whole list: all the authors in the DB. How do I limit the list to only those shown in the subform? It seems that there is no link now.
Additional: - How can I select the fields to be exported from the aditionalData? i.a. I don't need the AuthorID, just the names.
- Some of the other values [taken from supporting tables] are now showing as the ID's instead of the text values i.a Language.