Solved

Access 2007 updating a SQL 2005 image datatype field

Posted on 2009-04-05
15
649 Views
Last Modified: 2013-11-27
I'm not much of a SQL guy here, so this may be really easy to do but I cant figure it out or find help online.

I'm programming in Visual Basic within a Microsoft Access 2007 database.  I have a SQL 2005 server.

My database needs to update the contents of an "image" datatype field on a database on the SQL server.  I dont think that it matters but the content Im trying to save is RTF text.

I do not need to READ anything from this field  simply WRITE my update to it.

I already have an ODBC link setup and I can already set the image field to null or to a string value (although that, of course, comes out garbled).  Ive attached code that I have that will successfully set the field to null.

I need to know how to take a visual basic string (which contains my RTF text & RTF codes) and convert it so that it can be stored in this field.

The creators of the database gave me the following query that will allow me to pull the data OUT of the field correctly if I use SQL Management Studio:

select convert(varchar(max),convert(varbinary(max),COMMENTS)) from EA7STUDENTGRADES

I thought I could easily reverse engineer this query and apply it to Visual Basic, but it appears to be beyond me!

Dim vCurrentTable
Set vCurrentTable = CurrentDb().OpenRecordset("SELECT dbo_EA7STUDENTGRADES.* FROM dbo_EA7STUDENTGRADES, dbOpenDynaset, dbSeeChanges)
vCurrentTable.Edit
vCurrentTable!COMMENTS = null
vCurrentTable.Update
vCurrentTable.close

Open in new window

0
Comment
Question by:RentANerds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072395
RentANerds,

The RTF file format is a Word Processing format.
But you want to store a Word Processing File as an image?

Are you just looking to convert the RTF file to an Image file?
There are many utilities tha will do this:
http://www.google.com/search?hl=en&q=convert+rtf+to+image&aq=f&oq=

Or do you somehow want to take the RTF raw data and somehow manipulate it to be stored as an image?

Or are you just trying to store the rtf file as a Binary file (Not necessarily an image).


... In other words what do you actually need the file to be in the SQL database.
And what would these "Files" actually be when extracted from the SQL database, an rtf file again, or an images that look like the rtf file?

JeffCoachman
0
 

Author Comment

by:RentANerds
ID: 24072705
Thanks for your reply Jeff.

My Microsoft Access database is modifying the content of an existing SQL 2005 database.  The original creators of the SQL 2005 database are the ones who chose to take RTF formatted text and store it has a binary image fieldtype.  I have no idea why but I can only guess that, with some previous version of SQL, it was an easy way to store the data.  But I don't know for sure.

So long story short, I'm looking to take a string variable (that happens to contain RTF data) in Access' Visual Basic and put it into a binary image field of a SQL database.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24074233
There is no documentation on how this was done previously?

Do a Google search of "Convert RTF to Binary"

Many of the utilities that clain to "Convert RTF to an Image" will make an image out of the rtf file, not really read in the rtf tags as you are requesting.

JeffCoachman
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:RentANerds
ID: 24076583
Hi Jeff.

I've done numerous Google searches, including that one.  So far, none have brought me up anything overly helpful for this question.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24077576
Questions:

1. Again, no istructions were ever provided for this system?
2. Are there existing "Converted" files currently in the SQL table?
3. What happens to these files after they are stored in the SQL table? What is their purpose? Are they ever retrieved and/or displayed?
4. Are you bound to this mysterious system, or do you have the freedom to persue another storage option?
0
 

Author Comment

by:RentANerds
ID: 24079374
Hi Jeff:

1 - Correct.  The only instructions I have were the SQL query as I posted previously.  The only problem/question I have is with the datatype conversion.  The format of the RTF information as well as getting it into the table have both been accomplished.  All I need to know is how to typecast or convert my data to the correct SQL format prior to saving it into the SQL Table.

2 - These aren't really files.  They are raw data.  Having said that, yes there are data in the SQL table.  Using the SQL query that was provided to me, I can succesfully see the data.  All I need to know is how to reverse the process with Visual Basic in Access.

3 - The data (again, not files) are used by an entirely different program for multiple tasks.  None are too relevant here to this I don't think.  I very specifically need the data I have converted to put into this field.

4 - 100% bound and stuck.  I need to figure out this exact problem.  :-)


Thanks for your reply,

-Mike
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24079785
It should not be too difficult to accomplish using a ADO and an Update statement, if you are open to that, that is.
0
 

Author Comment

by:RentANerds
ID: 24080126
Hi Acperkins:

Whatever gets the job done reliabily works for me!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24083295
1. Add a reference to MS ADO
2. Add this code (untested)

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Your connection string goes here"

cn.Execute "UPDATE YourTableName Set Comments = 'Your text goes here' Where  YourConditionGoesHere"

cn.Close
Set cn = Nothing
0
 

Author Comment

by:RentANerds
ID: 24083617
Hi acperkins:

I'll try that code tomorrow at the office.  I'll be quite happy if it works!

Does the ADODB automatically convert the text to binary & then to the image format?

Amazingly simple!  I'll let you know the result.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24087083
>>Does the ADODB automatically convert the text to binary & then to the image format?<<
No.  ADO just passes the query to the SQL Server engine.  But SQL Server should implicitly convert it.  Otherwise try converting it explicitly, as in:
cn.Execute "UPDATE YourTableName Set Comments = CAST('Your text goes here' as Image) Where  YourConditionGoesHere"

And I also concur with the past comments.  Image is not only a lousy choice for what is essentially text, but is also a deprecated data type and will be removed in future versions.  It is a shame that there are still "professionals" who obviously know nothing about RTF, let alone SQL Server data types.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24087251
This is how I tested it:

1. With implict conversion.
Declare @dbo_EA7STUDENTGRADES table (COMMENTS image)

Insert @dbo_EA7STUDENTGRADES(COMMENTS)
Values ('First Image goes here')

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

UPDATE @dbo_EA7STUDENTGRADES
SET COMMENTS = 'Updating First Image'

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

2. With explicit conversion
Declare @dbo_EA7STUDENTGRADES table (COMMENTS image)

Insert @dbo_EA7STUDENTGRADES(COMMENTS)
Values (CAST('First Image goes here' as image))

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES

UPDATE @dbo_EA7STUDENTGRADES
SET COMMENTS = CAST('Updating First Image' as image)

Select COMMENTS, CONVERT(varchar(MAX), CONVERT(varbinary(MAX), COMMENTS))
From @dbo_EA7STUDENTGRADES
0
 

Author Comment

by:RentANerds
ID: 24089735
Hi acperkins:

I think we may have it.  Based on what you sent, I've attached the function I've created to update the table.

vEA7StudentGradesID is the table's primary key so it allows me to find the correct row.
vNewActualComment is the new RTF data to save in the table.

I found out I have to replace a single quote (') with two single quotes ('') in my data, which is fine.

From what I can see, this all appears to work.  Do you happen to see anything wrong with what I did?

If my users don't report any problems with the data & database today, I'll count this issue as solved!

Thanks for all your help.
Function SaveUpdatedComment(vEA7StudentGradesID, vNewActualComment)
  Dim cn As ADODB.Connection
 
  Set cn = New ADODB.Connection
  
  cn.Open "BB-LIVE"
  
  cn.Execute "UPDATE EA7STUDENTGRADES SET EA7STUDENTGRADES.COMMENTS = CAST('" & Replace(vNewActualComment, "'", "''", 1, -1, vbBinaryCompare) & "' as image) WHERE EA7STUDENTGRADES.EA7STUDENTGRADESID=" & vEA7StudentGradesID
  
  cn.Close
  Set cn = Nothing
End Function

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24092761
>>I found out I have to replace a single quote (') with two single quotes ('') in my data, which is fine.<<
That is correct.  Or you can parametize the query or even better still convert it to a stored procedure and you do not need to escape the single quotes.

>>Do you happen to see anything wrong with what I did?<<
The only thing I would change is this:
SET EA7STUDENTGRADES.COMMENTS =
For:
SET COMMENTS =

And
WHERE EA7STUDENTGRADES.EA7STUDENTGRADESID =
For:
WHERE EA7STUDENTGRADESID =


And I would keep the Replace simple as in:
Replace(vNewActualComment, "'", "''")
Instead of:
Replace(vNewActualComment, "'", "''", 1, -1, vbBinaryCompare)
0
 

Author Closing Comment

by:RentANerds
ID: 31566787
Thank-you very much!  We're rocking now!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question