problems importing a Paradox file into Access 2003

[DerekP]

Hi,
Does anyone have advice concerning problems I'm having importing an old
Paradox database file with a large Memo field into Access 2003? I'm able to
"get external data" by either "importing" or "linking tables" but, in the new
Access file, many of the records show "#Error" in the Memo field. I'm pretty
sure that the records where this is occurring are only the ones with very
large amounts of text in them. I'm not very familiar with this whole subject
but I've done some reading online and it looks to me like the Access Memo
field doesn't support large amounts of text, as Paradox did. I think I read
that the field type, in the new Access database, should be set to OLE to
accommodate large amounts of text... but I'm not sure how to import the old
Paradox file into an Access file where the field types don't match up...?
Any assistance would be greatly appreciated.
Thanks.

 

[Albert D. Kallal]

Access memo files can be absolute HUGE...nearly as large as the capacity of
ms-access for a whole file...

However, I believe that the user interface can only display/show/edit to a
max of 64,000 characters in length.

So, perhaps the "error" is for that reason?

You could write some code to "test" the length of those access memo
field?

If you have a record that you know has a bad memo, in the debug window, you
could type in:


debug.print len( dlookup("memo field name", "table name","id = 1545") )

The above would display/show the length (number of characters) in that memo
field.

It is possible that no data is moving If that is the case, then you might
have to do some type of export on the paradox side.

I never used paradox, and I did not know/realize that is also had some type
of memo field (are you sure about this????).

Most systems have a max of 255 characters in a standard text field.

Access memo fields support lengths up to 65,000 with the user interface,
but can hold up to 1 billion if you use code. 65,000 characters is
still quite large...at least 10-20 pages of typed text.

So, do "some" of the memo fields import ok?

In this case, you might have to do a export on the Paradox side (assuming
you have that ability).

 

[DerekP]

Hi Albert,

Thanks for your help with this!

I’m not sure where to find (or how to use) the “debug window†so I haven’t
been able to try your suggestion about determining the length of the text in
the Paradox memo field. I have checked the Paradox “help†files and they say
that the memo fields are of unlimited length. However, I know from using this
particular database table for the last 16 years that the table will freeze
once a certain amount of text has been entered into the memo field, in any
record. To unfreeze the table, I’ve had to delete the most recent text
entered and then create a new, successive record into which I have continued
to add more text.

Yes, the memo fields for most of the records import properly; I’d estimate
that maybe 3-5% are showing “#error†and they seem to be the records with
the most text in them. Also, these 3-5% do seem to be importing some of the
data in the memo field, but not all of it.

There is an “export†function within my version of Paradox but the only
options are:
Export File Type:
Delimited Text
Fixed Length Text
Quattro Pro Win
Quattro Pro Dos
Quattro
Lotus 2.X
Lotus 1.A
Excel 3.0/4.0
I didn’t think that any of these formats are likely to be able to handle the
memo field – do you think there’s any way to export/import using these
options?

Thanks again,
Derek

 

[Albert D. Kallal]

Hi Albert,

Thanks for your help with this!

I'm not sure where to find (or how to use) the "debug window" so I haven't
been able to try your suggestion about determining the length of the text
in
the Paradox memo field.

You can hit ctrl-g while in ms-access. that will jump you into the
programming/developers part of ms-access

(there is forms + user part in ms-access, and then there is the code +
development part of ms-access).

Yes, the memo fields for most of the records import properly; I'd estimate
that maybe 3-5% are showing "#error" and they seem to be the records with
the most text in them.

Sounds like large memo fields are the problem. However, as mentioned, they
MAY have correctly imported into ms-access due to that "error " display.
since most of the data imported just fine, then we have a good possible hope
that the "error" display is the larger then 64,000 characters issue, and in
fact your data DID import correct.

If this is the case, they were really home free. What I would do is simply
write a code loop that takes any memo field that's larger than say maybe
50,000 characters, and take the contents chop it in half, and split the
results over two memo fields ( in other words I'm suggesting, that we simply
add a second memo field, and therefore we can chop the existing memo field
in half.

Also, these 3-5% do seem to be importing some of the
data in the memo field, but not all of it.

Right, but as I mentioned the user interface only supports editing of up to
65,000 characters, after that it can't display it.

so let's get that little test of the length field thing working, if
it's>65,000 characters, then the data has imported correctly, but we simply
just can't display in edit it

There is an "export" function within my version of Paradox but the only
options are:
Export File Type:
Delimited Text
Fixed Length Text
Quattro Pro Win
Quattro Pro Dos
Quattro
Lotus 2.X
Lotus 1.A
Excel 3.0/4.0
I didn't think that any of these formats are likely to be able to handle
the
memo field - do you think there's any way to export/import using these
options?

No I don't think any of them will help you at all here, I'm really
surprised that there's no dBase export or FoxPro export, as those also
support memo type fields.


So at this point I'm actually thinking that you data did import correctly.
If you can get that sample expression in the debug.window to work and the
resulting value is longer than 65,000 characters, then we've nailed this
problem down. If this turns out to be the case, then I would simply run a
loop to find the largest memo field in the whole system. Once we have that
number, then we can determine if this max length will fit into two memo
fields, or three (65, 000 characters maximum for each). Then we simply write
a little piece of code to split out the memo field into the 2 or 3 memo
fields.

So lets deterrmine the above, if it turns out to be true, then I'll post a
little piece of code for you that you can use to split this data out, and
you then be able to view and edit the memo data inside of access.

So as mentioned, access memo fields are really large, but they only support
that maximum 65,000 characters for display in editing on the screen. I think
this is a reasonable compromise to split out the memo into two (or 3) memo
fields. This would allow at least you to view, report, seach and work ith
this memo data in ms-access.

 

[DerekP]

Hi Albert,
From my Paradox table, I've chosen one of the records with a large amount of
text in the memo field, I've copied it and then have tried to paste it into
the memo field of the Access table - it is not all accepted and it gets
cropped. That seems to make sense with what you've said about Access not
supporting the display & editing of large numbers of characters.
Doing this has made me realize that I shouldn't be trying to switch from
Paradox to another database which doesn't work as well. I realize that it
may be possible to work around this problem but I'm not very good at this
stuff and I think I'd be spending too much time trying to simply make it all
work. I'm going to either find a system that I can run Paradox on (I just
bought a new computer with Vista Ultimate - it won't run my Paradox) or I'll
try to find another database withoutthese issues.
Thank you very much for all of your help - hope you have a great summer!