Insert large base64 into memo field

[Mike Iacovou]

Hi all.
starting out in access... i would like to store & retrieve a large (base64)
string in an access table field... i understand that it is possible to store
1Gb data programmatically (or at least 64K) via VBA...

If anyone has the time, please could you:

1. confirm that memo fields can store > 64K in Acess 2000+
2. illustrate the correct method to store & retrieve a large string in a
memo field - for example field 'Base64' in table 'BinaryData' - this table
only has this single field.

Constructing an INSERT statement and executing it seems to limit entry to
64K...

Many thanks

Mike

 

[Gary Walter]

"Mike Iacovou"wrote:

starting out in access... i would like to store & retrieve a large
(base64)
string in an access table field... i understand that it is possible to
store
1Gb data programmatically (or at least 64K) via VBA...

If anyone has the time, please could you:

1. confirm that memo fields can store > 64K in Acess 2000+
2. illustrate the correct method to store & retrieve a large string in a
memo field - for example field 'Base64' in table 'BinaryData' - this table
only has this single field.

Constructing an INSERT statement and executing it seems to limit entry to
64K...

Hi Mike,

Yes (at least in Access 97-2002) you can store a string > 64K
in a memo field.

Open a recordset and either AddNew or Edit the recordset field

something like

Dim rs AS DAO.RecordSet

Set rs = CurrentDb.OpenRecordset("BinaryData")

With rs
.AddNew
!Base64 = strYourBigString
.Update
End With


So...it can be done.

You should be able to get the big string back
with a query as long as you do not Group By
(or sort on?) the Base64 field which will truncate
the field to 255 chars (256?).

I cannot help but ask if this is the "best way."

In a form textbox, you won't be able to edit it
(and probably not even scroll through it).

I am not familiar with Base64 strings, but is there
something equivalent to LF or CR. For example,
in one project I worked on, each "line" of a big blob
of characters appeared to end in "%0d%0a"

If that were the case, I might parse your big string
into "lines" for each record, then concatenate these
lines back together when you need the full string.

If these "lines" turned out to be less than 256 chars,
then you could use type Text which could also help you
with "tendency" of memo fields to corrupt (that has
never happened to me, but some here might say "yet").

Then, maybe that is irrelevant to your purposes...

good luck,

gary

 

[Mike Iacovou]

thanks gary.

i'm a noob with access... comfortable with referencing in excel vba... will
get there in the end...

I get an 'unknown type' with the DIM DAO.Recordset - I obviously need to set
some references somewhere... This is a vanilla standard Access install... in
the past have have had inter-system problems based on references - will this
be an issue if I add references in this project (and what am i missing ?)

Appreciate the help... great

Mike

 

[Pieter Wijnen]

Not as long as you always prefix ambigous Objects (Recordset for one)

Fastest way to add a reference is pressing Ctrl+G (Immediate Window)
Select Tools/References from the Menu
Select 'Microsoft DAO 3.6 Object Library'

Pieter

 

[Douglas J. Steele]

Sounds as though you're using either Access 2000 or 2002, neither of which
included the reference to DAO by default.

Go into the VB Editor and select Tools | References from the menu bar.
Scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it, then close the dialog.

DAO is a fundamental library to Access, so you shouldn't run into issues on
other machines.