Does this http://support.microsoft.com/kb/945280[/url] help at all?
It looks like Service Pack 3 is helpful, and that possibly converting the field to "True" or "False" might help Access understand it better.
So after some testing and fiddling around I've come up with that the information will only overwrite information to the table and not append to it, which seems to suggest that the data is in conflict with the way the table is setup.
I suspect that Monarch is converting the data to YES/NO upon reading in the initial table since (from what I've been able to gather) Monarch doesn't understand this type of data. I can create a new field and convert the -1 and the 0 to "Yes" or "No" but to do this I define the field as text.
I'm going to give this a try with an append action on the new table but I suspect I'll run into the same issue where the database can't auto convert the text field into the YEs/No field needed.
Short of that, I'm not sure if there is a solution. SInce I'm not using SQL and relying on the batch for application communication.
I'm wondering if anyone else has found a way to write data into a YES/NO type files using Access 2003 SP3. I'm hoping yes that there is a way to get it to work. I didn't see anything else in the forumsthat would help.
Out of curiosity, I'd be interested to know if this same issue would apply to Access 2007 and/or 2010?
Okay so the above testing failed completly and I could not generate the new table at all.
I did some further testing and was able to successfully overwrite the table manually through Monarch. The problem is though the returning data being sent is read as data type Number by Access. If I flip the type to YES/NO in the design screen the outlook is all correct. Of course I don't want to have to manually open the table and reflip the output data to data typpe Yes/No every time I run the batch.
So I've validated the data and the data is correct. The problem is identifying the type of data it is correctly. I'm still hopeful someone will have an idea on how to overcome this challenge.
As this is somewhat of a unique situation, I don't believe that this will be resolved by Datawatch any time soon. As such, we're on our own, and left to our own devices.
My solution involves adding some VBA code to the Access database created by Monarch, via a new module.
I developed this code using some web resources and have tested it. It seems to work just fine, as you need per your description, unless I've misunderstood something.
Here's the code to copy/paste into your VBA module:
'A nice enhancement to this code would be to
'not hard code the name of the Use field, but
'rather make the field name a function variable
'so that it could be changed easily as necessary.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Dim fld As DAO.Field
Set db = CurrentDb
'add a new field for the Monarch export value
db.Execute "ALTER TABLE YesNoTest ADD COLUMN Monarch YESNO;"
'set the checkbox for the table design
Set tdf = db.TableDefs("YesNoTest")
Set fld = db.TableDefs("YesNoTest").Fields("Monarch")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
'update the new Monarch field with the value from the
'Monarch export in the Use field, then
'delete the exported field Use
.Execute "ALTER TABLE YesNoTest DROP COLUMN Use;"
'rename the Monarch field to Use, the original name
fld.Name = "Use"
Set fld = Nothing
Set prp = Nothing
Set tdf = Nothing
Set db = Nothing
/CODENext, add an Access macro. Use a single command, RunCode, specifying the function name UpdateMonarchExport(). Now it is critical that you save this macro with the name AutoExec.
When you open the database (I used the mdb format for my test), the AutoExec macro will kick in, and will invoke the VBA code.
In my testing I used a field named "Use" as the test field with -1 and 0 values, so you'll need to revise that particular in the code to match with your field(s).
I used Access 2010 to develop this solution.
I don't see a non-programmatic solution to this challenge. Not to say that one may not exist; I just don't know what it may be.
EDIT: I used the table name YesNoTest for my Monarch export test, so you'll need to change that in the code too for your purposes.
I've been busy the last couple of weeks with other work tasks and haven't had the chance to check the boards out. I'm definitly going to give this solution a try.
I did want to thanks you for the time and effort into this. It goes far beyond what I was expecting and is very much appreciated.