VBA to print to access tables

Question

I am building a library database and i have a working script to probe a web database using the ISBN number and return data about the book. I have successfully made the data print to the immediate window using debug.print and then the specific property of the object. I am now wanting to print the data retrieved straight in to the database.

here is my code for the ISBN search:

Option Compare Database
    Dim BookTitle As String
    Dim BookTitleLong As String
    Dim BookAuthorsText As String
    Dim BookPublisherText As String
    Dim BookSummary As String
    Dim BookNotes As String
    Dim accessKey As String

Private Sub Class_Initialize()
    'Your isbnDB access key'
    accessKey = "xxxxxx" 'Working access key here
End Sub
Property Get Title() As String
    Title = BookTitle
End Property
Property Get TitleLong() As String
    TitleLong = BookTitleLong
End Property
Property Get AuthorsText() As String
    AuthorsText = BookAuthorsText
End Property
Property Get PublisherText() As String
    PublisherText = BookPublisherText
End Property
Property Get Summary() As String
    Summary = BookSummary
End Property
Property Get Notes() As String
    Notes = BookNotes
End Property

Public Function Lookup(ISBN As String) As Boolean
    Lookup = False
    Dim xmlhttp
    Set xmlhttp = CreateObject("MSXML2.xmlhttp")
    xmlhttp.Open "GET", "https://isbndb.com/api/books.xml?access_key=" & accessKey & "&results=texts&index1=isbn&value1=" & ISBN, False
    xmlhttp.send
    'Debug.Print "Response: " & xmlhttp.responseXML.XML'
    Dim xmldoc
    Set xmldoc = CreateObject("Microsoft.XMLDOM")
    xmldoc.async = False
    'Note: the ResponseXml property parses the server's response, responsetext doesn't
    xmldoc.loadXML (xmlhttp.responseXML.XML)
    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") = 0) Then
        MsgBox "Invalid ISBN or not in database"
        Exit Function
    End If
    If (xmldoc.selectSingleNode("//BookList").getAttribute("total_results") > 1) Then
        MsgBox "Caution, got more than one result!"
        Exit Function
    End If
    BookTitle = xmldoc.selectSingleNode("//BookData/Title").Text
    BookTitleLong = xmldoc.selectSingleNode("//BookData/TitleLong").Text
    BookAuthorsText = xmldoc.selectSingleNode("//BookData/AuthorsText").Text
    BookPublisherText = xmldoc.selectSingleNode("//BookData/PublisherText").Text
    BookNotes = xmldoc.selectSingleNode("//BookData/Notes").Text
    BookSummary = xmldoc.selectSingleNode("//BookData/Summary").Text
    Lookup = True
End Function

and here is the code i have used to print to the immediate window

Public Function t()
    Dim book
    Set book = New ISBN
    book.Lookup ("0007102968")
    Debug.Print book.Title
    Debug.Print book.PublisherText
    Debug.Print book.AuthorsText
    Debug.Print book.TitleLong
    Debug.Print book.Summary
    Debug.Print book.Notes

End Function

this is all based off this question asked a few years back: ISBN -> bookdata Lookup to fill in a database

i would also like to be able to input the ISBN through a form if anyone can help with that :)

Answer 1

You can try the following approach.

First of all, create a user-defined data type to store the book data:

Public Type Book
    ISBN As String
    Title As String
    TitleLong As String
    AuthorsText As String
    PublisherText As String
    Summary As String
    Notes As String
End Type

Then create an insert query and pass the book values as parameters. Let's name the query qryAdd.

PARAMETERS prmISBN Text (255), 
           prmTitle Text (255), 
           prmPublisherText Text (255), 
           prmAuthorsText Text (255), 
           prmTitleLong Text (255), 
           prmSummary LongText, 
           prmNotes LongText;

INSERT INTO T ( ISBN, Title, PublisherText, AuthorsText, TitleLong, Summary, Notes )
SELECT prmISBN AS ISBN, 
       prmTitle AS Title, 
       prmPublisherText AS PublisherText, 
       prmAuthorsText AS AuthorsText, 
       prmTitleLong AS TitleLong, 
       prmSummary AS Summary, 
       prmNotes AS Notes;

'Change T to the name of your table and update the field names.

Lastly, the function to call the insert query where we pass the book to be inserted.

Public Function InsertToDatabase(b As Book) As Boolean
    With CurrentDb().QueryDefs("qryAdd")
        .Parameters("[prmISBN]").Value = b.ISBN
        .Parameters("[prmTitle]").Value = b.Title
        .Parameters("[prmTitleLong]").Value = b.TitleLong
        .Parameters("[prmPublisherText]").Value = b.PublisherText
        .Parameters("[prmAuthorsText]").Value = b.AuthorsText
        .Parameters("[prmSummary]").Value = b.Summary
        .Parameters("[prmNotes]").Value = b.Notes
        .Execute dbFailOnError
    End With

    'all good
    InsertToDatabase = True
End Function

To test it:

Sub Test()
    Dim b As Book
        b.ISBN = "aaa"
        b.Title = "bbb"
        b.TitleLong = "ccc"
        b.PublisherText = "ddd"
        b.AuthorsText = "eee"
        b.Summary = "fff"
        b.Notes = "ggg"

    If InsertToDatabase(b) Then MsgBox "Done!"
End Sub