Solved

Importing to Access 2010

Posted on 2012-03-28
12
399 Views
Last Modified: 2012-04-09
I have imported a .dbf file to Access 2010.  The table has a number of Yes/No fields.  They are all set to Yes in Access after the import regardless of how they were set in the original .dbf file.  How do I import and have them retain their correct values?
0
Comment
Question by:jacobblack555
  • 6
  • 6
12 Comments
 

Author Comment

by:jacobblack555
ID: 37777753
I need to modify my explanation of the records that have been imported to Access.  They have all imported as -1 not Yes.  My mistake.  Sorry!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778012
Some app will use other values from what Access likes.

In *MS Access*, No is 0 and Yes is *any other value*.


So I suspect that there are no "0" values in the original data
;-)

Import the table as is (with the original values)
(Do not change the DataType to Yes No, yet!)
Then create a translation table that says what value is true or False in the original data with what Access needs.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778056
..Sorry...

Then run an update query like this to change the values to what Access needs to see...

For example.
If in the source data 1=True and 2 =false, you would run a query like this:

UPDATE YourTable SET YourTable.YourYesNoField = IIf([YourYesNoField ]=2,0,-1);

So here I am locking onto the "defined" value in Access ( 0=False), and anything else will be true (-1)

Make sense?

;-)

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37778102
Sorry again.

Then verify that the new (updated) table values are eithe -1 or 0 (in step with the original Yes/No values.)
the you can go ahead and change the dataType of the field to Yes/No.

When you see the scary message about "Some data May Be Lost", ...simply click "Yes"

All that error means is that if there were still any numbers other than 0 or -1 now in the table, (which there should not be...) that would be converted to:
0=No
*any other Number=-1

;-)

jeffCoachman
0
 

Author Comment

by:jacobblack555
ID: 37801699
I'm sorry I've neglected this answer for so long.  Have been unavoidable tied up.
Thank you for answering.  I am fairly new to Access and don't understand some things you are talking about.  First - there are both YES & NO values in the original table.
When you say "Do not change the DataType to Yes No, yet!", do you mean leave that field in Access as a text field until everything has imported?  Will it then import as Yes/No?  What do you mean by a "translation table?"  I do not understand that term.  Thank you for your help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37802175
Import the DBF file to Excel and to Text.
Post them here
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:jacobblack555
ID: 37811604
After reading the explanation a couple more times, it is making sense.  I think I understand what to do now.  It may be a while until I can get back to this project to try it, so I will close this question now and if I have more trouble, I'll post another question.  Thank you for your help!
0
 

Author Comment

by:jacobblack555
ID: 37811757
I've requested that this question be closed as follows:

Accepted answer: 0 points for jacobblack555's comment #37811604

for the following reason:

I understand the instructions of the solution even if I haven't had the chance to work on it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37811758
<After reading the explanation a couple more times, it is making sense.  I think I understand what to do now. >
If the explanation you are referring to is my post, then shouldn't you accept my post as the solution?

I don't mind waiting for you to fully evaluate it...

JeffCoachman
0
 

Author Closing Comment

by:jacobblack555
ID: 37813810
Sorry, Jeff!  I clicked the wrong button.  This was my first venture into Experts Exchange.
0
 

Author Comment

by:jacobblack555
ID: 37813816
Jeff - If I wanted to contact you later, do I just come back to this thread?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37826299
yep.

My contact info is also in my profile
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now