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
665 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

743 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

12 Experts available now in Live!

Get 1:1 Help Now