Solved

Access 2007 updating a SQL 2005 image datatype field

Posted on 2009-04-05
15
642 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
  • 7
  • 5
  • 3
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:RentANerds
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It should not be too difficult to accomplish using a ADO and an Update statement, if you are open to that, that is.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:RentANerds
Comment Utility
Hi Acperkins:

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

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Thank-you very much!  We're rocking now!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now