VBA Export Access Table as dBase File

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 4099 Views - Last Post: 10 June 2013 - 08:36 PM Rate Topic: -----

#16 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 07:15 AM

View Postandrewsw, on 07 June 2013 - 02:07 PM, said:

Quote

Fair idea. Just for kicks I just tried manually selecting the table in access and performing the gui-route of export to dBase, but (thankfully?) received the same error about field not fitting in record.

You could perhaps concentrate on getting the gui-method (or macro) to work - it is quicker and easier to test than the coded version. Once you've achieved this then you'll know that the coded version can be made to work, and you will be more aware of the issues involved.


Sounds like a plan. I have 69 total fields (not including the ID field) all set as Text - 255 characters. When I deleted down to only having 11 fields at 255 characters, the gui-export worked!?

After reading through some of these links I'm starting to get the suspicion there are dBase field length/file size limits I am unaware of:

http://www.mrexcel.c...a-dbf-file.html

http://www.mrexcel.c...-madness-2.html

http://www.okstate.e...pc/z0214453.htm
Was This Post Helpful? 0
  • +
  • -

#17 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 07:22 AM

Making progress! :clap:

Since I got the 11 fields to export through Access-gui and create my "TEST.DBF" file, I modified my code to only export those 11 fields from Excel to Access run the .TransferDatabase() method (success!). It looks like part of the issue was indeed the file not existing previously in the listed filepath!

Now I just have to figure out these limitations so that I can get all 69 fields into the file...
Was This Post Helpful? 0
  • +
  • -

#18 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3459
  • View blog
  • Posts: 11,711
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 07:28 AM

The steps from 11 to 69 are much smaller than that from 0 to 1 ;)

Quote

thousand miles to be travelled, start with foot (placed) down

This post has been edited by andrewsw: 07 June 2013 - 07:33 AM
Reason for edit:: Laozi

Was This Post Helpful? 1
  • +
  • -

#19 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 08:35 AM

15 TEXT fields at 255 characters plus my ID field seems to be the limit. Even one more field I receive the "Field will not fit in record" error... back at a brick wall. :hammer: :hammer: :hammer:
Was This Post Helpful? 0
  • +
  • -

#20 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 09:17 AM

Got it thanks to some help over at StackOverflow! Via Gord Thompson:

http://stackoverflow...tions/16985750/

Quote

dBASE_III and dBASE_IV files apparently have a maximum record length of 4,000 bytes (ref: here). This suggests that a dBASE_IV file could not hold more than 15 Text(254) fields. Possible workarounds include:

change the table structure in Access to shorten Text(255) fields if they are not using all 255 characters, or

convert Text() fields to Memo prior to exporting, since dBASE, like Access, only stores a link to the Memo field in the main table (10 bytes for dBASE).


While I'm severely lack in test data, after shrinking all the fields down from being Text - 255 my vba code now executes without err and when viewing the .DBF file in access, all appears to be coming across as it should! :clap:
Was This Post Helpful? 0
  • +
  • -

#21 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: VBA Export Access Table as dBase File

Posted 10 June 2013 - 08:36 PM

View PostAnalyticLunatic, on 06 June 2013 - 03:51 PM, said:

View Postandrewsw, on 06 June 2013 - 08:43 PM, said:

I believe this "filePath\Personal Project Notes\" should be pointing at an existing database file. I've never touched dBase files but I doubt that TransferDatabase will build a database for you.

Added: I dunno though. Is a dBase database a collection of .dbf files/tables?


I honestly have not a clue. I've never worked with dBase nor .DBF Files.

To Andy's question, yes. Some older ISAM databases (notably dBase and its derivatives Clipper and Foxpro, as well as Paradox) aren't contained in a single file. Rather, all the files in a single folder are the database. (You'll notice that if you use a JET connection to access a DBF file, you reference the folder that the file is in rather than the DBF file itself. Then all the table names are the names of all the DBF files in the folder.) One DBF file is the equivalent of a table. An index would have an .NDX extension, and there are other file types as well.

Also, dBase records are limited to 4000 bytes. Text fields are limited to 254 bytes, and yes, they can be sized to less. Field names differ with different flavors of xBase, but the one I've seen the most is 32 characters.

This post has been edited by BobRodes: 10 June 2013 - 08:43 PM

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2