Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert Access OLE Object to SQL Server

Status
Not open for further replies.
LindaH

LindaH

Programmer
Dec 12, 2001
42
0
0
US
MS Access allows an OLE Object to link to an MS Word document. How can I do this in SQL Server? I need to store formatted text.
When I converted the Access database to SQL Server, it converted the OLE Object to an Image datatype...but I have no idea how to gain access to the data it converted so I can either display it or update it! If anyone can point me in the right direction, I'd appreciate it!
Thanks.
 
Sort by date Sort by votes
Yes, data will be accessed thru ADO.
 
Upvote 0 Downvote
use the AppendChunk method of field object to update the data; use the Getchunk method to read the data. Let me know if it helps
 
Upvote 0 Downvote
I'm just getting binary-looking characters on the screen.

My code:
strText = objRs.Fields("WordText").GetChunk(8000)
response.write strText
 
Upvote 0 Downvote
Since u are saying its an image object u can't just write like that.
To help u, I need to know more info
1. from where u are getting data to store and
2. where u are displaying it after reading from SQL
 
Upvote 0 Downvote
Original source of data is MS Access with two fields:
ID (autonumber)
WordText (OLE Object, Object Type=MS Word)

I imported the Access database into a SQL Server database called, "MSWordTest." It imported like so:
ID (int 4)
WordText (image 16)

My code using Visual Interdev 6.0 (SP5):

------- Begin Code Sample --------------------------------

<!-- METADATA TYPE=&quot;typelib&quot; FILE=&quot;C:\Program Files\Common Files\System\ado\msado15.dll&quot; -->
<%@ Language=VBScript %>
<html>
<head>
<title>Test SQL Image datatype</title>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft Visual Studio.NET 7.0&quot;>
</head>
<body>

<%
Dim Conn, objRs
Dim SqlStr, strText
SqlStr =&quot;SELECT * FROM Table1&quot;

Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
set objRs=Server.CreateObject(&quot;ADODB.recordset&quot;)
Conn.ConnectionTimeout=60
Conn.Open &quot;DSN=MSWordTestSQL&quot;

objRs.Open SqlStr, Conn

Do Until objRs.EOF
response.contenttype = &quot;application/vnd.ms-word&quot;
Response.AddHeader &quot;Content-Disposition&quot;, &quot;filename=wordtest.doc&quot;

strText = objRs.Fields(&quot;WordText&quot;).GetChunk(8000)
response.write &quot;wordtext = &quot;
response.write strText

response.write &quot;<br><br>binary write = &quot;
response.binarywrite objRs(&quot;WordText&quot;).GetChunk(8000)

objRs.MoveNext
Loop

objRs.Close
set objRs=Nothing
Conn.Close
Set Conn=Nothing
%>
</body>
</html>

------- End Code Sample -----------------------------------

The results:

wordtext = ?-

binary write = ì¥Á M ð ¿
bjbjâ=â= &quot;
€W €W ÿÿ ÿÿ ÿÿ l ¨ ¨ ¨ ¨ ¨ ¨ ¨ Ü ö ö ö ö

...and so on!
I tried it without the contenttype & the .AddHeader & get similar results.
===========================================================
Maybe I'm taking a totally wrong approach to what I'm trying to accomplish. My user has some WordPerfect documents containing Job Description/Job Duty information. I want to create a database (first MS Access, then SQL Server in about 6 months) with this information. Some of the document lends itself nicely to database fields such as Job Title, Job Description, Job Classification, etc., but there are a couple parts of the document (job duties & qualifications) which are very textual and I need to maintain bolding, underlining, indentation, and carriage returns. I want a web front-end for the user to maintain this data. The data will also be displayed on the internet and will mimick their current WordPerfect document. I'm trying to keep conversion an easy task if possible, like doing a cut-and-paste operation into each data field I set up in the database. Is there a better approach to handling this task rather than the approach I'm trying here?

I appreciate your help!
 
Upvote 0 Downvote
try declaring the variable as type variant and see

Also one more thought.
If ur text is without any format(just simple text) and not more than 8000 characters, try to use Varchar type

Let me know if it helps
 
Upvote 0 Downvote
I need to maintain bolding, underlining, and carriage returns in my text.
 
Upvote 0 Downvote
the data u write to the web, need to be editable or just for display?
 
Upvote 0 Downvote
Display & edit. (The bottom of the 7th thread of this discussion explains fully what I'm trying to do. Look under the string of &quot;=====.&quot;)
 
Upvote 0 Downvote
I am sorry, I lost there. But could u check the stream object of ADO. Hope that should help u out
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
202
Volvere
V
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
207
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
284
mikrom
mikrom
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
166
pjw001
pjw001
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
119
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top