How do I maintain field formats when exporting an Access 2002 table, using Access 2002 file format) to dbf file?

I have a large table with several of the fields/columns formatted as an integer with no decimal places. When I export records from the table to a dbf format, the dbf fields are formatted as a number with five decimal places. Is there a simple way to maintain the Access table format when exporting to a dbf file?

Thanks in advance for your assistance...
DavidwBurkeAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
1. How are you exporting this data?
If you are using code, are you specifying the correct Dbase format?
dBase III
dBase IV
dBase 5.0


2. Try setting the Datatype to: Number, Long Integer
...and see what happens.

3. I am no Expert at DBase, but can you explicitly state the datatype of the incomming data at any point?
Or can you just change it after the data is imported?
Or can you simply format it for No decilmal places?

JeffCoachman
0
 
DavidwBurkeAuthor Commented:
Jeff,

Thanks. I'm exporting to dbase IV. Long integer with zero decimal places exports with five decimal places. I am trying to avoid having to reformat the dbf file.

Thanks
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then this is an issue on the Dbase side.
This is why I am suggesting that it be resolved on the dbase side.

Your other option is to export the the data to another format first (CSV, Excel, ...ect) then import to Dbase.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DavidwBurkeAuthor Commented:
boag2000,

I've saved as csv and txt and Excel 4.0 datasheet. The numeric field formats are changed from the Access design format. Decimal is truncated to 2 places when two fields are in fact 5 places.

Another problem, the gis software program that will import these files will import only dbf or Excel 4.0.
0
 
Jeffrey CoachmanMIS LiasonCommented:
"Decimal is truncated to 2 places "

I thought we were talking about the "Integer" datatype?

I thought you said that Integer number in Access get converted to 5 decimal places in Dbase,

No you seem to be saying that Deciaml are 2 places but they are really 5 places.

Can you please clarify?
0
 
DavidwBurkeAuthor Commented:
In Access, several fields in the table are defined as integer with zero decimal places. When the file is exported to dbase IV, the fields in new dbf file are numeric with 5 decimal places. I would like to preserve the Access table field formats when exporting to dbf.

Thanks, Dave
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, As far as I know, you can't.
This must be done on the Dbase side (if possible)

Or Export to Excel, if, as you say, the gis software can use that.
Exporting (unformatted) Integers from Access to Excel does not result in any decimals.

;-)

JeffCoachman

0
 
DavidwBurkeAuthor Commented:
Jeff,

Thanks, it does appear that one work-around is to save to Excel. The GIS will import dbf or Excel 4.0 worksheet (a new release of the GIS is to be release soon and this problem will go away).

I will accept that as the solution.

Thanks, Dave
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
 
BALMUKUND KESHAVConnect With a Mentor Commented:
Create dbf file with format as in Access Table.
Connect to dbf file like :
Sub connect1()
  path = "your dbf file path\"
  Set conn = CreateObject("ADODB.Connection")
  conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""DBASE 5.0;"";"
  Set OpenDBFConn = conn
End Sub
Read access table and insert into dbf using DAO.

Regards,

Bm Keshav

0
 
DavidwBurkeAuthor Commented:
I failed to mention earlier that the dbf format must be 4.0, no higher. Can I change the DBASE 5.0 to 4.0?

Dave
0
 
BALMUKUND KESHAVCommented:
There is no difference by data structure in dbase iv & V
thanks
regards,

bm keshav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.