Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
740 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
12 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 
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 1000 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

578 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