VB5 to MySQL via MyODBC Transfer Problem

[edwardfinlayson]

Please Help,
I am trying to synchronise two databases using VB5 as a transfer agent. I have connected to both the databases, one of which a jet connected msaccess 7.0 database the other a remote MySQL database. It is with this remote database that i am having trouble.
I have no problem retriveing any record / recordset and can manipulate - interogate any field except the memo type which responds although a wrkSpc.dbs.rst.field("fieldName&quot.type does indeed respond with the correct data type [12 - dbMemo] when I try to read from these fieldss I recieve an 'Invalid data type' error. I have no difficulty writing to these fields as I am building SQL statments 'on the fly' and then issuing an sql Execute command. This method carries out update type proceedures without error. Bellow is shown a code snippet and the line which causes the error to be thrown is marked with '##' at the start. :-Sub SyncData()
Set wrkRemote = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set wrkJet = CreateWorkspace("", "admin", "&quot
'Get Local Database Location
Let VOTSStr = GetSetting(appname:="VOTS", section:="Startup", _
Key:="LocalDB", Default:="no&quot
If VOTSStr = "no" Then Exit Sub
'Set Database Object To This DataBase.
Set dbs = wrkJet.OpenDatabase(VOTSStr, False)
'Get VOTS Database Location
Let remoteStr = GetSetting(appname:="VOTS", section:="Startup", _
Key:="VOTSDB", Default:="no&quot
'Debug.Print "VOTS Connection String: "; remoteStr
If remoteStr = "no" Then Exit Sub
Debug.Print remoteStr
'Set dbsVOTS = wrkRemote.OpenDatabase("VOTS", dbDriverNoPrompt, False, _
"ODBC;DSN=VOTS - HOMER;DATABASE=VOTS;SERVER=homer.flyin-bytes.com;UID=fin;PWD=l1nux_;PORT=3306;OPTION=147466;STMT=;&quot
Set dbsVOTS = wrkRemote.OpenDatabase("VOTS", dbDriverPrompt, False, remoteStr)
SyncBilling
....
dbs.Close
dbsVOTS.Close
wrkJet.Close
wrkRemote.Close
SaveSetting appname:="VOTS", section:="Startup", _
Key:="LASTUPDATE", setting:=Format(Str(Now()), "yyyy/mm/dd hh:mm:ss&quot
Debug.Print "Synchronisation Complete At: " + Str(time())
End Sub


Sub SyncBilling()
'Pull remote Billing Table
Let sqlStr = "SELECT BILLING.* FROM BILLING WHERE ((UPDATEDT BETWEEN '" + _
GetSetting(appname:="VOTS", section:="Startup", Key:="LASTUPDATE", _
Default:="1980/01/01 00:00:00&quot + "' AND '" + Format(Str(Now()), "yyyy/mm/dd hh:mm:ss&quot + "')" + _
"AND AGENTID = '" + GetSetting(appname:="VOTS", section:="Startup", Key:="AGENTID", Default:="&quot + "');"
Debug.Print "BILLING Pull Phase Started"
' Debug.Print sqlStr
Set rmtRst = dbsVOTS.OpenRecordset(sqlStr, dbOpenDynamic)
Do While Not rmtRst.EOF
Set lclRst = dbs.OpenRecordset("SELECT * FROM BILLING WHERE (BILLING.AGENTID = '" + _
rmtRst!AGENTID + "' AND BILLING.BILLID = '" + rmtRst!BILLID + "');&quot
If Not lclRst.EOF Then
'Update Existing Local Record
lclRst.Edit
If rmtRst!BILLID <> &quot;&quot; And Not IsNull(rmtRst!BILLID) Then Let lclRst!BILLID = rmtRst!BILLID
If rmtRst!ABSRETID <> &quot;&quot; And Not IsNull(rmtRst!ABSRETID) Then Let lclRst!ABSRETID = rmtRst!ABSRETID
If rmtRst!FOXPROCL <> &quot;&quot; And Not IsNull(rmtRst!FOXPROCL) Then Let lclRst!FOXPROCL = rmtRst!FOXPROCL
If rmtRst!COMPANY <> &quot;&quot; And Not IsNull(rmtRst!COMPANY) Then Let lclRst!COMPANY = rmtRst!COMPANY
If rmtRst!BILLNAME <> &quot;&quot; And Not IsNull(rmtRst!BILLNAME) Then Let lclRst!BILLNAME = rmtRst!BILLNAME
If rmtRst!CONTACTT <> &quot;&quot; And Not IsNull(rmtRst!CONTACTT) Then Let lclRst!CONTACTT = rmtRst!CONTACTT
If rmtRst!CONTACTF <> &quot;&quot; And Not IsNull(rmtRst!CONTACTF) Then Let lclRst!CONTACTF = rmtRst!CONTACTF
If rmtRst!CONTACTL <> &quot;&quot; And Not IsNull(rmtRst!CONTACTL) Then Let lclRst!CONTACTL = rmtRst!CONTACTL
If rmtRst!ADDRESS1 <> &quot;&quot; And Not IsNull(rmtRst!ADDRESS1) Then Let lclRst!ADDRESS1 = rmtRst!ADDRESS1
If rmtRst!ADDRESS2 <> &quot;&quot; And Not IsNull(rmtRst!ADDRESS2) Then Let lclRst!ADDRESS2 = rmtRst!ADDRESS2
If rmtRst!ADDRESS3 <> &quot;&quot; And Not IsNull(rmtRst!ADDRESS3) Then Let lclRst!ADDRESS3 = rmtRst!ADDRESS3
If rmtRst!ADDRESS4 <> &quot;&quot; And Not IsNull(rmtRst!ADDRESS4) Then Let lclRst!ADDRESS4 = rmtRst!ADDRESS4
If rmtRst!POSTTOWN <> &quot;&quot; And Not IsNull(rmtRst!POSTTOWN) Then Let lclRst!POSTTOWN = rmtRst!POSTTOWN
If rmtRst!COUNTY <> &quot;&quot; And Not IsNull(rmtRst!COUNTY) Then Let lclRst!COUNTY = rmtRst!COUNTY
If rmtRst!REGION <> &quot;&quot; And Not IsNull(rmtRst!REGION) Then Let lclRst!REGION = rmtRst!REGION
If rmtRst!PCODE <> &quot;&quot; And Not IsNull(rmtRst!PCODE) Then Let lclRst!PCODE = rmtRst!PCODE
If rmtRst!PHONENUM <> &quot;&quot; And Not IsNull(rmtRst!PHONENUM) Then Let lclRst!PHONENUM = rmtRst!PHONENUM
If rmtRst!EXT <> &quot;&quot; And Not IsNull(rmtRst!EXT) Then Let lclRst!EXT = rmtRst!EXT
If rmtRst!FAXNUM <> &quot;&quot; And Not IsNull(rmtRst!FAXNUM) Then Let lclRst!FAXNUM = rmtRst!FAXNUM
If rmtRst!EMAILADD <> &quot;&quot; And Not IsNull(rmtRst!EMAILADD) Then Let lclRst!EMAILADD = rmtRst!EMAILADD
##
If rmtRst!NOTES <> &quot;&quot; And Not IsNull(rmtRst!NOTES) Then Let lclRst!NOTES = rmtRst!NOTES
##
' If rmtRst!AGENTID <> &quot;&quot; And Not IsNull(rmtRst!AGENTID) Then Let lclRst!AGENTID = rmtRst!AGENTID
If rmtRst!SAGENTID <> &quot;&quot; And Not IsNull(rmtRst!SAGENTID) Then Let lclRst!SAGENTID = rmtRst!SAGENTID
If rmtRst!CLOSED <> &quot;&quot; And Not IsNull(rmtRst!CLOSED) Then Let lclRst!CLOSED = rmtRst!CLOSED
If rmtRst!Updated <> &quot;&quot; And Not IsNull(rmtRst!Updated) Then Let lclRst!Updated = rmtRst!Updated
If rmtRst!UPDATEDT <> &quot;&quot; And Not IsNull(rmtRst!UPDATEDT) Then Let lclRst!UPDATEDT = rmtRst!UPDATEDT
lclRst.Update
DISPLAYRST (rmtRst)
Debug.Print &quot;Updated Local Record&quot; + rmtRst!BILLID, &quot;'&quot;; rmtRst!NOTES; &quot;'&quot;
...

If Any of you could be of assistance I would be most greatfull.

Fin.