VBA Download Example

Importing into Access

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 MS Access are as follows.

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.
In MS Access from the External Data tab select the import from Text File option and select the file from ODBC WriteNow.
Follow through on the Import wizard in MS Access adjusting field names and data types as needed, e.g. setting a field as datetime.
In the final step of the Import wizard select save Import steps and give the specification a name.

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 import please visit the link below.

https://support.office.com/en-us/article/Save-the-details-of-an-import-or-export-operation-as-a-specification-6b94e183-2b10-4333-a31a-001fe75321b5

Download and Import Function

The example below of a Download And Import function uses the following steps to fully download and import the data from a MYOB table into a new MS Access table.

Download only the Table headers from ODBC WriteNow and enter the information into a new temp CSV file.
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.
Import the CSV file into MS Access using a premade Access Import Specification.
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\temp\myobsynctemp_" & 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
	
'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, "")		
'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	
'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