6 Replies Latest reply: May 15, 2014 10:12 AM by KeyserSoze _ RSS

    Access Database Yes/No field type conversion

    warlok

      Hi all,

       

      It's been a while since I posted any questions, so here's a good one that has me a little stumped.

       

      I'm reading in some information from Monarch form an access database. Inside the access database are some Check box YEs/No logical data type fields. WHen this is read in to Monarch Pro version 10., it gets converted into a numerical field which contains 0 if it's not checked off or -1 if the field is checked off.

       

      This is fine if I wish to keep the data as Numeric but, if I want to rewrite back to an access database, the fields of course are loaded back as numeric type data type fields instead of the checkbox Yes/no fields.

       

      Is there a way to send the report data back to an access database as a yes/no logical data compliant field?

       

      I should state (and I'm waiting for the boos and hisses), I'm working on Access 2003. Also I'm automating the process using a program batch file.

       

      Thanks for any inp-ut you can provide.

       

      Warlok

        • Access Database Yes/No field type conversion
          Olly Bond

          Hello Warlok

           

          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.

           

          Best wishes,

           

          Olly

            • Access Database Yes/No field type conversion
              warlok

              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?

               

              Warlok

                • Access Database Yes/No field type conversion
                  warlok

                  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.

                   

                  Warlok

                    • Access Database Yes/No field type conversion
                      KeyserSoze _

                      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:

                      Public Function UpdateMonarchExport() As Boolean

                           

                          '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)

                           

                          With fld.Properties

                              .Append prp

                              .Refresh

                          End With

                           

                          'update the new Monarch field with the value from the

                          'Monarch export in the Use field, then

                          'delete the exported field Use

                          With db

                              .Execute "UPDATE YesNoTest SET = ;"

                              .Execute "ALTER TABLE YesNoTest DROP COLUMN Use;"

                          End With

                           

                          'rename the Monarch field to Use, the original name

                          fld.Name = "Use"

                           

                          Set fld = Nothing

                          Set prp = Nothing

                          Set tdf = Nothing

                          Set db = Nothing

                           

                      End Function

                      /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.