Export to DBF file

[fenhow]

Hello,

I am using his code to export data from a query into a .dbf file. The .dbf file gets created great however the data drops to 5 decimal places when in the Query it shows 10 decimal places.

Any way to make it export with 10 decimal places in this code?

Dim access As access.Application
Set access = CurrentProject.Application
access.DoCmd.OpenQuery "qry_si EXPORT FOR MAP"
access.DoCmd.TransferDatabase acExport, "dBASE IV", "c:\", acTable, "qry_si EXPORT FOR MAP", "MIDDLETON 3D ARCVIEW.DBF"
DoCmd.Close acQuery, "qry_si EXPORT FOR MAP"

Thanks.

Fen How

 

[gavinjb]

I dont know if it will work, but have you tried using format on your columns in your query e.g. format(field,"00.00000000)

 

[namliam]

Decimal places are useless unless filled 13.1 wont be any different from 13.1000000000
If filled access will show the decimals, without fail... why the need?

 

[Atomic Shrimp]

DBF tables allow the explicit definition of the number of decimal places in their structure.

One way to get what you want is to first, create (using dBase) a template dbf with exactly the structure you want, then in your VBA, copy the template to a new filename, link the new file as a linked table, then append your data into it.

 

[Atomic Shrimp]

A couple of other things you might need to know about working with dbf tables from Access...

1. Certain versions of dBase have a philosophical disagreement about dBase file header structure - the second byte in the actual file describes the year the file was last updated. Some implementations/interpretations of the of xBase standard expect this to roll around (so it would contain '09' now), some - including Access* - just expect it to contain the number of years since 1900 (so it would contain '109' now) - this means that in some cases, a dBase file created or linked & manipulated in Access will become unreadable to its native application.
If you encounter this, the following function can be used to fix the file back to the rolling year version (the function needs to be called after any operation on the table):

Public Function FixDBF(PathName As String)
Dim mybyte As Byte
mybyte = Val(Format(date, "YY"))
Open PathName For Binary As #1 Len = 1
Put #1, 2, mybyte
Close 1
DoEvents
End Function

2. If working with linked dBase files (only applies if you're using Access to change their contents) - deleting records doesn't delete them for real - it just marks them for deletion - they won't be gone for good until you open them in dBase and 'pack' them.

That's actually the standard methodology for record deletion anyway in dBase, but Access must be doing it in some way subtly different, because some legacy applications that just wouldn't see records deleted (marked for deletion) in dBase, still see them as present if they were deleted in Access as a linked table. Applications compiled in Clipper, for example, are susceptible to this.

If you need to be able to delete records in a dbase table, via Access, the only safe way to do it is to keep a clean, empty template dBase table, create a copy, link it and append the required recordset into it - never actually deleting records, just appending the ones you want to keep into a virgin copy of the empty table each time.



*This does mean that in the year 2257, Access will have problems interacting with even its own interpretation of the xBase standard.