VBA Download Example

The following is a fully functional VBA coding example for downloading MYOB Data and importing it into a new table in MS Access.
This function has been designed to work with minimal changes but requires an MS Access Import specification to be created for each MYOB Table first. A short guide on how to create this Import Specification is included below.

MS Access Import Specification

The basic steps for creating and using a saved import in MS Access are as follows:

  1. Download a sample of the data you wish to import. In this case, that would be one page of records from ODBC WriteNow for the MYOB Table you wish to import.
  2. In MS Access, from the External Data tab, select the "Import from Text File" option and choose the file from ODBC WriteNow.
  3. Follow through on the Import wizard in MS Access, adjusting field names and data types as needed, e.g., setting a field as datetime.
  4. In the final step of the Import wizard, select "Save Import steps" and name the specification.

If you have followed the above steps, you should now have a saved import specification that you can use to correctly import any future ODBC WriteNow data for that MYOB table.

For a more detailed guide and general information about MS Access saved imports, please visit the link below:

Save the details of an import or export operation as a specification

Download and Import Function

The example below of a Download and Import function uses the following steps:

  1. Download only the table headers from ODBC WriteNow and enter the information into a new temp CSV file.
  2. Loop through sending page requests into ODBC WriteNow and update the received records into the CSV file until ODBC WriteNow returns a "no records" error.
  3. Import the CSV file into MS Access using a premade Access Import Specification.
  4. Clean up and remove the temporary CSV files, ready for the next import.
Public Function DownloadAndImport(what As String, tablename As String, Secification As String) As String
    Dim data As String
    Dim page As Integer
    Dim fs As Object ''FileSystemObject
    Dim tsOut As Object ''TextStream
    
'Your Company's API Key goes below
    apikey = ""
    server = "http://myobsync.accede.com.au/"
    page = 0
    lastPage = False
    
'download the complete file first
    Set fs = CreateObject("Scripting.FileSystemObject")
    tempFileName = "C:windows	empmyobsynctemp_" & tablename & ".csv"
    Set tsOut = fs.CreateTextFile(tempFileName, True)
    
'Get Header Row
    data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=1&apikey=" & apikey, "")
    If InStr(data, "MYOBSync Error") <> 0 Then
        lastPage = True
    Else
        tsOut.WriteLine data
    End If
    
&#39;Get csv data
    While lastPage = False
        SysCmd acSysCmdSetStatus, "Downloading To file " & what & ", Page " & page		
        data = ServerRequest("GET", server & "download/" & what & "/" & page & "?onlyheader=0&apikey=" & apikey, "")		
&#39;Check for a MYOBSync Error, meaning it has returned no records
        If InStr(data, "MYOBSync Error") <> 0 Then
            lastPage = True
        Else
            tsOut.WriteLine data
            page = page + 1
        End If
        DoEvents
    Wend	
    tsOut.Close	
&#39;Import CSV Data into table
    SysCmd acSysCmdSetStatus, "Importing File " & what & " to table " & tablename & ", Page " & page
    DoCmd.TransferText acImportDelim, Secification, tablename, tempFileName, True
    
    SetAttr tempFileName, vbNormal
    SysCmd acSysCmdClearStatus
End Function

Public Function encodeURL(str As String)
    Dim ScriptEngine As Object
    Dim encoded As String
    Set ScriptEngine = CreateObject("scriptcontrol")
    ScriptEngine.Language = "JScript"
    encodeURL = ScriptEngine.Run("encodeURIComponent", str) 
End Function

Public Function ServerRequest(method As String, url As String, data As String)
    Dim objHttp As Object
    Debug.Print "URL:" & url
    
    Set xmlhttp = CreateObject("Msxml2.XMLHTTP")
    xmlhttp.Open method, url, False
    xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    Debug.Print data
    data = encodeURL(data)
    Debug.Print data
    
    xmlhttp.Send "&data=" & data
    
    ServerRequest = xmlhttp.responseText
    Set objHttp = Nothing	
End Function