What am I missing?

[Amy Blankenship]

I have a query that creates a new record but does not populate all the
fields. All fields except the memo field (reportRTF) populate properly.
I'm guessing that it is something about the 8K of data I'm trying to insert
into the field. I'm not sure if I've hit some sort of length restriction or
if something in the data itself is preventing the insert. The query is as
follows:

INSERT INTO StudentReport( StudentID, BaseReportID, ReportRTF ) VALUES (1,
10, '{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0
Verdana;}}~~{\colortbl
;\red255\green255\blue128;\red255\green255\blue153;\red0\green255\blue255;}~~\viewkind4\uc1\pard\li20\sl240\slmult1\qc\tx1530\tx3690\tx5490\cf1\lang1033\b\f0\fs24
Analysis of Exam Topic
Knowledge\par~~\pard\par~~\pard\li20\sl240\slmult1\tx8820\tx10080\tx12528\ul\b0
Topic\ulnone\tab\ul Correct\ulnone\tab\ul Level of Difficulty\ulnone\tab\ul
Time/question\par~~\cf2\ulnone\fs22 Math\cf3
\par~~Measurements (distance, area, volume, units, metric,
and\par~~exponential notation)\tab 0 %\tab\tab 0:00
\par~~Equation knowledge (knowing and applying equations)\tab 0 %\tab\tab
0:00 \par~~Linear and non-linear
relationships (proportionality, inverse,\par~~exponential growth)\tab 6
%\tab\tab 0:07 \par~~Logarithms
and decibels\tab 0 %\tab\tab 0:00
\par~~Trigonometry\tab 0 %\tab\tab 0:00
\par~~\par~~Critical Difficulty\b For the 92 questions which involve
\par~~Math\tab 4 % cor.\tab Critical Difficulty\tab 0:15 \b0
\par~~
__________________________________________________________________________________________\par~~\par~~\cf2
Physical Principles and Ultrasound Instrumentation \cf3
\par~~Physical principles of ultrasound (frequency,
wavelength,\par~~propagation velocity, amplitude, intensity)\tab 18
%\tab\tab 0:07 \par~~Attenuation
effects: absorption, reflection, refraction, acoustic\par~~impedance,
contrast effects, and contrast resolution\tab 0 %\tab\tab 0:00
\par~~Instrumentation controls and affect on image/signal quality\tab 0
%\tab\tab 0:00 \par~~Image
acquisition (scanned versus non scanned modalities, 2-D,\par~~3-D, PRF,
frame rate, temporal resolution)\tab 0 %\tab\tab 0:00
\par~~Doppler and color Doppler theory, Doppler processing,
Doppler\par~~tissue characterization and color flow mapping\tab 15 %\tab\tab
0:08 \par~~Detail resolution
(lateral, axial, and elevational), harmonics and\par~~emerging
technologies\tab 0 %\tab\tab 0:00
\par~~Imaging artifacts and Doppler artifacts\tab 0 %\tab\tab 0:00
\par~~\par~~Critical Difficulty\b For the 132 questions which involve
\par~~Physical Principles and Ultrasound Instrumentation\tab 5 % cor.\tab
Critical Difficulty\tab 0:50 \b0
\par~~
__________________________________________________________________________________________\par~~\par~~\cf2
Clinical Examination Principles \cf3
\par~~Signs and symptoms\tab 12 %\tab\tab 0:16
\par~~Spectral Doppler and Doppler examination\tab 12 %\tab\tab 0:10
\par~~M-mode\tab 0 %\tab\tab 0:00
\par~~Standard plane positions, imaging scan planes and views\tab 0
%\tab\tab 0:00
\par~~Transesophageal, and intraoperative based echo\tab N/A\tab N/A\tab
N/A\par~~Principles and laws of hemodynamics\tab 4 %\tab\tab 0:08
\par~~Pressures, pressure waveforms, and catheterization\tab 0 %\tab\tab
0:00 \par~~Electrical
conduction, ECG, and echoes in associated disorders of\par~~cardiac
rhythm\tab 0 %\tab\tab 0:00
\par~~Mechanical events and heart sounds\tab 0 %\tab\tab 0:00
\par~~Contrast echo\tab 4 %\tab\tab 0:16
\par~~\par~~Critical Difficulty\b For the 42 questions which involve
\par~~Clinical Examination Principles\tab 0 % cor.\tab Critical
Difficulty\tab 0:00 \b0 \par~~
__________________________________________________________________________________________\par~~\par~~\cf2
Valvular Heart Disease \cf3
\par~~Aortic valve, aorta, and subvalvular outflow tract\tab 0 %\tab\tab
0:00 \par~~Mitral valve\tab 0
%\tab\tab 0:00
\par~~Echo-Doppler assessments of prosthetic valves\tab N/A\tab N/A\tab
N/A\par~~Echocardiographic findings in infective endocarditis\tab N/A\tab
N/A\tab N/A\par~~Fluid dynamics, PISA, and color flow imaging of regurgitant
jets\tab N/A\tab N/A\tab N/A\par~~Tricuspid valve\tab 0 %\tab\tab 0:00
\par~~Pulmonic valve\tab 0 %\tab\tab 0:00
\par~~\par~~Critical Difficulty\b For the 82 questions which involve
\par~~Valvular Heart Disease\tab 1 % cor.\tab Critical Difficulty\tab 0:10
\par~~ \b0
__________________________________________________________________________________________\par~~\par~~\cf2
Anatomy, Chamber Size and Function
\cf3 \par~~Coronary
arteries, coronary artery disease and stress\par~~echocardiography\tab 0
%\tab\tab 0:00 \par~~Assessment
of chamber size and function\tab 0 %\tab\tab 0:00
\par~~Quantification of segmental dysfunction\tab 0 %\tab\tab 0:00
\par~~Echocardiographic assessments of cardiomyopathies\tab 0 %\tab\tab 0:00
\par~~Diastolic function\tab 12 %\tab\tab 0:10
\par~~Left atrium, pulmonary veins, and coronary sinus\tab 0 %\tab\tab 0:00
\par~~Right ventricle, right atrium, and IVC\tab 0 %\tab\tab 0:00
\par~~Intraatrial and intraventricular septum\tab N/A\tab N/A\tab
N/A\par~~Doppler calculations of volumetric flow\tab 0 %\tab\tab 0:00
\par~~\par~~Critical Difficulty\b For the 18 questions which involve
\par~~Anatomy, Chamber Size and Function\tab 0 % cor.\tab Critical
Difficulty\tab 0:00 \par~~
\b0
__________________________________________________________________________________________\par~~\par~~\cf2
Congenital Heart Disease
\cf3 \par~~Complex congenital heart
disease\tab 0 %\tab\tab 0:00
\par~~ASDs\tab 0 %\tab\tab 0:00
\par~~VSDs\tab 0 %\tab\tab 0:00
\par~~Fetal echocardiography\tab N/A\tab N/A\tab N/A\par~~\par~~Critical
Difficulty\b For the 29 questions which involve \par~~Congenital Heart
Disease\tab 0 % cor.\tab Critical Difficulty\tab 0:00
\par~~ \b0
__________________________________________________________________________________________\par~~\par~~\cf2
Cardiac Masses and Pericardial Disease
\cf3 \par~~Pericardial disease\tab 0 %\tab\tab 0:00
\par~~Cardiac tumors and masses\tab 0 %\tab\tab 0:00
\par~~Assessment of myocardial perfusion with contrast 2-D echo\tab 0
%\tab\tab 0:00 \par~~Echoes in
cardiac transplantation\tab N/A\tab N/A\tab N/A\par~~\par~~Critical
Difficulty\b For the 29 questions which involve \par~~Cardiac Masses and
Pericardial Disease\tab 0 % cor.\tab Critical Difficulty\tab 0:00
\b0\par~~}~~');

If anyone has any ideas, I'd appreciate it.

Thanks;

Amy

 

[Jerry Whittle]

Hi Amy,

It works for me. I ran the query from the SQL view of a query in Access 2003.

One problem with memo fields is corruption.
I suggest opening up a new database file and creating the table.
Then run the query. If it works, I'd be worried that your original
database is corrupt.

I'd also check to see if you aren't bouncing up against the 2GB file
limitation. Doubtful.

 

[Amy Blankenship]

I get exactly the same results in a new database. Everything except the
memo populates. Access 2002. The original DB is less than 2 MB.

Thanks;

Amy

 

[Amy Blankenship]

If I insert 6,000 periods (I've managed to get the query string down below
6K), the query works. So I don't think this is a length issue. It's got to
be something inside the string that is causing the failure.

-Amy

 

[Jerry Whittle]

Strange. How are you running the query? In code or something?

Grasping at straws here. I'm running Access 2003 with the Access 2000 file
format.

Could you run it on some else's computer? I just might be your machine.

 

[Amy Blankenship]

Strange. How are you running the query? In code or something?

For debug purposes, I was running it straight from the query window. But
the ultimate aim is to run it from outside Access using ODBC.


I found that if I deleted any of the longer lines from the RTF (the way it
wraps in the query window), that it would run. So I reduced all instances
of the long underscore line to a single underscore with the idea that they
could be "reconstituted." that worked.

Thanks;

Amy

 

[John Nurick]

Hi Amy,

I found that if I deleted any of the longer lines from the RTF (the way it
wraps in the query window), that it would run. So I reduced all instances
of the long underscore line to a single underscore with the idea that they
could be "reconstituted." that worked.

It shouldn't be a length issue: the Access Specifications help topic
says a SQL statement can have approximately 64,000 characters.

Anyway, I pasted the SQL statement you posted into an Access query and
changed the table and field names to suit my test database, but took
care not to alter the RTF string. The result was

INSERT INTO AddrCentTest( ID, FirstName, M ) VALUES (211, 'Helen',
'{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0

[snip]

Pericardial Disease\tab 0 % cor.\tab Critical Difficulty\tab 0:00
\b0\par~~}~~');

This query executed successfully. I then (a) pasted the contents of the
memo field in the new record into one empty text file; (b) pasted your
original RTF string into another; and (c) used FC.EXE to confirm that
the two are identical.

So it all seems very strange. What versions and service packs of Windows
and Access are you using?

 

[Amy Blankenship]

Access 2002 SP 3
XP Tablet edition 2005 Version 2002 SP 2

Thanks;

Amy

Hi Amy,

I found that if I deleted any of the longer lines from the RTF (the way it
wraps in the query window), that it would run. So I reduced all instances
of the long underscore line to a single underscore with the idea that they
could be "reconstituted." that worked.

It shouldn't be a length issue: the Access Specifications help topic
says a SQL statement can have approximately 64,000 characters.

Anyway, I pasted the SQL statement you posted into an Access query and
changed the table and field names to suit my test database, but took
care not to alter the RTF string. The result was

INSERT INTO AddrCentTest( ID, FirstName, M ) VALUES (211, 'Helen',
'{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0

[snip]

Pericardial Disease\tab 0 % cor.\tab Critical Difficulty\tab 0:00
\b0\par~~}~~');

This query executed successfully. I then (a) pasted the contents of the
memo field in the new record into one empty text file; (b) pasted your
original RTF string into another; and (c) used FC.EXE to confirm that
the two are identical.

So it all seems very strange. What versions and service packs of Windows
and Access are you using?