Solved

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

Posted on 2009-03-30
17
673 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...
0
Comment
Question by:DavidwBurke
  • 5
  • 5
  • 2
17 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 24083145
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
 

Author Comment

by:DavidwBurke
ID: 24086613
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24087362
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
 

Author Comment

by:DavidwBurke
ID: 24088093
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24090029
"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
 

Author Comment

by:DavidwBurke
ID: 24090541
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24090729
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
 

Author Comment

by:DavidwBurke
ID: 24090822
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24092755
;-)
0
 
LVL 6

Assisted Solution

by:BALMUKUND KESHAV
BALMUKUND KESHAV earned 250 total points
ID: 24128110
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
 

Author Comment

by:DavidwBurke
ID: 24129301
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
 
LVL 6

Expert Comment

by:BALMUKUND KESHAV
ID: 24144637
There is no difference by data structure in dbase iv & V
thanks
regards,

bm keshav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

24 Experts available now in Live!

Get 1:1 Help Now