0

First I created a table in a SQL Server database with columns like document id, docname and docdata.

Second I managed to upload a document into that table using MS Access and ole object and now I have a word document there, by double click the ole object in the MS Access form interface I could open the document.

What is interesting about this is that I can edit the document and get the changes saved to the document inside the server.

The idea is that I want to use the SQL Server over our LAN-network as an alternative to OneDrive and make collaborative working possible to my coworkers with no internet service.

Now I am trying to open that document using using VBA in MS Word.

Here is my vague attempt:

Option Explicit
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub CommandButton1_Click()
' my own connection string
cn.Open "provider=sqloledb;server=127.0.0.1,1433;database=accessdb;UID=sa;PWD=111111"

With rs
  Set .ActiveConnection = cn
  .Source = "select * from docs where did=1"
  .LockType = adLockOptimistic
  .CursorType = adOpenKeyset
  .CursorLocation = adUseClient
  .Open
End With

Dim WordObj As Word.Application
'rs![docdata].Verb = -2
'rs![docdata].Action = 7  ' Activates the application.
Dim obdoc As Word.Document
Set obdoc = rs![docdata] ' what property to attach here ??
obdoc.SaveAs2 "D:\thisdoc.doc"
WordObj.Documents.Open obdoc
WordObj.Activate
End Sub

A mismatch error pops up and I did not know how to deal with this, what type of property should retrieve the data from that field and assign it to the newly made document.

Update 20-2-2020

Um, Now I recognized that the Ms-word document isn't a simple RTF file.

It is a zipped folder ! see this ! and in order to verify this I removed the .docx extension and replaced it with .zip, then I unzipped it and got some folders inside of them a few .xml files..

This means that the upper approach is not compatible with the nature of this file. type.

Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Isma'el
  • 23
  • 6
  • 1
    Instead of adding a comment to clarify your question, you should edit your actual question. – STLDev Jan 17 '20 at 05:37
  • 2
    Save the document to disk from `rs![docdata]` and open it from the save location. – Tim Williams Jan 17 '20 at 06:12
  • https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/ – Tim Williams Jan 17 '20 at 06:17
  • Well , MsgBox VarType(rs![docdata]) tells it is of 8209 data type, saving it as is produce unknown file format for MS word. – Isma'el Jan 17 '20 at 06:47
  • It's unclear whether there's still a question open, but if there is, the approach in Tim Williams's comment is what would work... – Cindy Meister Jan 21 '20 at 11:06
  • The approach in Tim Williams's comment would work with the saved version, what I need is to save changes to the file inside the database in Sql Server, just like when you open it from ole object in Ms access as I explained earlier. – Isma'el Jan 21 '20 at 11:17

0 Answers0