How do I open a Memo field in Access?

[Aeneas]

1. I am changing my database from Paradox to Access and have copied several
files over using the import wizard. However I find that while the wizard
does copy the table information accurately it does not automatically copy
over information stored in the Memos, known as BLOBS Formatted Text in
Paradox. Is there any way to transfer this information.
2. My second qeustion relates to opening the Memo field in Access. In
Paradox I simply clicked on the field and the text opened a new screen,
similar to notepad, with my memo info which I could then add to or edit as I
wised. However in Access I find that I cannot open the text in this way.
Does Access operate in the same way?

 

[Rick Brandt]

1. I am changing my database from Paradox to Access and have copied
several files over using the import wizard. However I find that
while the wizard does copy the table information accurately it does
not automatically copy over information stored in the Memos, known as
BLOBS Formatted Text in Paradox. Is there any way to transfer this
information.
2. My second qeustion relates to opening the Memo field in Access. In
Paradox I simply clicked on the field and the text opened a new
screen, similar to notepad, with my memo info which I could then add
to or edit as I wised. However in Access I find that I cannot open
the text in this way. Does Access operate in the same way?

No. Access treats memo fields (in the user interface) just like a text
field. You see them in the table datasheet (though they are not really
stored in the table) and you bind them to TextBoxes in forms and reports to
view the data and/or edit it.

They are stored differently and have some limitations (can't be indexed for
example), but their behavior in the GUI is just like any other text field.

As for importing your Paradox data I would expect the driver to take care of
that and just have Memo fields created during the import. I have done
similar operations with no problems.

 

[Fred Boer]

Dear Rick:

The following part of your post caught my eye:

No. Access treats memo fields (in the user interface) just like a text
field. You see them in the table datasheet (though they are not really
stored in the table) and you bind them to TextBoxes in forms and reports
to view the data and/or edit it.

Could you explain more about how memo fields are "not really stored in the
table"? I don't think I've heard or read about this before...

Thanks!

 

[Tom Wickerath]

Hi Fred,

The JET database engine stores a pointer in a record for each memo,
hyperlink and OLE Object data type. This pointer points to a hidden system
table of some kind where the data is actually stored for these three data
types. If you look up "Specifications" in Access Help, and then click on the
listing for tables, you should see the following:

Number of characters in a record (excluding Memo and OLE Object fields)
2,000

The reason that Memo and OLE Object fields (and Hyperlink, although the Help
file misses those) are excluded from the count is that only a pointer to this
data is being stored. These three data types are also more suseptible to
corruption due to this pointer mechanism losing track of it's target. Often
times, we will see posts in the newsgroups about strange characters showing
up in a record. These are usually memo fields that are involved.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Fred Boer]

Thanks, Tom! I've learned something new today. Whoo Hoo! Now I don't have to
think again until tomorrow!!

Cheers,
Fred

 

[Aeneas]

Thanks.
1. Re importing from Paradox: my problem is that the import wizard will
import all fields other than Memo fields. The difficulty may lie with my
Paradox .mb files which are stored separately from the .db files and may be
corrupted in some way. The application is more than ten years old and I no
longer have the original installation disc so I cannot refresh it. This is
one of the reasons I am moving to Access. I could copy and paste the Memo
information from Paradox; since I have thousands of records, I don't relish
the prospect of doing that. But there may be no other way.
2. Re opening a Memo field in Access: If I understand you correctly Access
does not have the useful facility in Paradox which allows me to open a Memo
by double clicking on the field (Microsoft might think of copying this).
Many of my Memos run to a page or more and reading and editing them in the
table view is not really feasible. You say that I should "bind them to
TextBoxes" in order to read and edit them. How do I do this?

 

[Rick Brandt]

Thanks.
1. Re importing from Paradox: my problem is that the import wizard
will import all fields other than Memo fields. The difficulty may
lie with my Paradox .mb files which are stored separately from the
.db files and may be corrupted in some way. The application is more
than ten years old and I no longer have the original installation
disc so I cannot refresh it. This is one of the reasons I am moving
to Access. I could copy and paste the Memo information from Paradox;
since I have thousands of records, I don't relish the prospect of
doing that. But there may be no other way.
2. Re opening a Memo field in Access: If I understand you correctly
Access does not have the useful facility in Paradox which allows me
to open a Memo by double clicking on the field (Microsoft might think
of copying this). Many of my Memos run to a page or more and reading
and editing them in the table view is not really feasible. You say
that I should "bind them to TextBoxes" in order to read and edit
them. How do I do this?

One really should not try to use the table datasheet as an interface, but
when you are in a field you can open a Zoom box to see more of large text
fields either from the menu or with Shift-F2.

Building a bound form with bound textboxes is a lot easier than you think.
With your table selected in the db windows press the AutoForm button. One
second later you have a form.

 

[Aeneas]

Thanks. The zoom facility is handy, a bit like the double click in Paradox,
but the box is quite small. Sorry to be so obtuse but where do I find the
AutoForm button?Unfortunately "Help"was not much help with this.

 

[David W. Fenton]

One really should not try to use the table datasheet as an
interface, but when you are in a field you can open a Zoom box to
see more of large text fields either from the menu or with
Shift-F2.

It might be possible to use:

Screen.ActiveDatasheet

to assign an the doubleclick event of a memo field, but I can' seem
to figure it out. It is much easier if you *want* to work with
datasheets to just use the form wizard to create a datasheet form,
and then you can easily assign a DoubleClick event to the memo
field's control that opens the Zoom box, using this code:

Public Function ZoomBox(strObjName As String, _
strCtlName As String, strCurrValue As String, _
Optional strFontName As String, _
Optional intFontWeight As Integer, _
Optional intFontSize As Integer) As Variant
Select Case SysCmd(acSysCmdAccessVer)
Case "8.0"
ZoomBox = Application.Run("UTILITY.BuilderZoom", _
strObjName, strCtlName, strCurrValue, strFontName, _
intFontWeight, intFontSize)
Case "9.0", "10.0"
' THE ARGUMENTS HAVE CHANGED in A2K/A2K2;
' THE FIRST THREE STILL WORK
ZoomBox = Application.Run("UTILITY.BuilderZoom", _
strObjName, strCtlName, strCurrValue)
End Select
End Function

You need only call it with the first three arguments in the
OnClick() event, something like this:

Call ZoomBox(Me.Name, Screen.ActiveControl.Name, _
Screen.ActiveControl.Value)

This will open the ZOOM box just the same as SHIFT-F2 will.

 

[Aeneas]

I am happy with the shift/F2 method of opening the zoom box. What I am less
than happy with is the size of the zoom box. Many of my memos run to a page
or more and I would like to be able to read and edit them using rich text.
Is it possible to increase the size of the box to make reading and editing
easier (as it was in Paradox)?

 

[Rick Brandt]

I am happy with the shift/F2 method of opening the zoom box. What I
am less than happy with is the size of the zoom box. Many of my
memos run to a page or more and I would like to be able to read and
edit them using rich text. Is it possible to increase the size of the
box to make reading and editing easier (as it was in Paradox)?

If you use a form (as you should) then you can control all of that.

 

[Aeneas]

I see what you mean. The form view allows me to do most of what I want to
with my memos. Many thanks for all the advice.

 

[David W. Fenton]

The zoom facility is handy, a bit like the double click in
Paradox, but the box is quite small. Sorry to be so obtuse but
where do I find the AutoForm button?Unfortunately "Help"was not
much help with this.

Key point: Access is not Paradox.

The fact is that the ZoomBox is actually just a form in a library
database. You could easily create one for yourself that is whatever
size you like.

But why you'd bother will all of that, I don't know, as it doesn't
seem to me that such a generic solution is justifiable when you
should actually be building forms for each of your tables, where you
can size the memo field according to how large you'd like it to be.

 

[David W. Fenton]

Many of my memos run to a page
or more

I don't understand the concept of "a page or more" when you're
discussing onscreen display.

It seems to me that you are carrying an awful lot of baggage from
other database programming environments and that's a huge stumbling
block to your ability to use Access successfully.

 

[David W. Fenton]

I see what you mean. The form view allows me to do most of what I
want to with my memos. Many thanks for all the advice.

That's the advice you've been given repeatedly in this thread!

Why did it sink in *this* time?

 

[Rico Bandeira]

I see what you mean. The form view allows me to do most of what I want to
with my memos. Many thanks for all the advice.

 

[Aeneas]

Ah well! I did apologise above for being a bit obtuse!. I guess some of us
need to have things repeated a few times for the penny to drop! But I do
appreciate the advice.

 

[Aeneas]

I used the page simile as the best way I could find to convey the quantity of
data I have in my memo fields in case there were limitations in Access that I
did not know about. You are right about baggage from other dbase
environments. My excuse is that I am not creating databases from scratch but
working on transferring about 12 years of material into Access and adapting
them to the new environment. It will probably take a little time to find my
way around. But it's good to know I have people out there who can help me.