We help IT Professionals succeed at work.

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

Medium Priority
809 Views
Last Modified: 2013-12-21
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...
Comment
Watch Question

MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
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

Author

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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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.

Author

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.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
"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?

Author

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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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

Author

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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
;-)
CERTIFIED EXPERT
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

Author

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
CERTIFIED EXPERT

Commented:
There is no difference by data structure in dbase iv & V
thanks
regards,

bm keshav

Explore More ContentExplore courses, solutions, and other research materials related to this topic.