making and adding Word-OLE object to DB

[Triion]

Hi,

I need a small vb-program that will read data from Excel and store it in Access-db... Problem being that one field needs to be an Word-OLE-object so that needs to be created first... But I don't know how to do that plus how do you write the SQL-query then for inserting the data?

My feable attempt:

    Dim cnt As ADODB.Connection
    Dim stSQL As String, stCon As String, stDB As String
    
    stDB = "C:\db.mdb"
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
    
    Set cnt = New ADODB.Connection
    With cnt
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=C:\Test.xls;" & _
        "Extended Properties=Excel 8.0;"
        .Open
    End With
    Dim strQuery As String
    
    strQuery = "SELECT * FROM [Sheet1$]"
    
    Dim objRS As ADODB.Recordset
    
    Set objRS = New ADODB.Recordset
    objRS.Open strQuery, cnt, adOpenKeyset, adLockReadOnly
    
    'set connection variable
    Set cnt = New ADODB.Connection
    
    Dim i As Integer
    Dim N, vraagNaam, vraagType, vraagtekst, onderdeel, hoofdstuk, SQL As String
    Dim objWord As Object
    Do While Not objRS.EOF
        Set vraagNaam = objRS(0).Value  // <-- ERROR
        Set vraagType = objRS(1).Value
        Set vraagtekst = objRS(2).Value
        Set onderdeel = objRS(3).Value
        Set hoofdstuk = objRS(4).Value
        
        'Make Word document object
        Set objWord = CreateObject("Word.Application.8")
        objWord.Documents.Add
        objWord.Documents(1).Content.InsertAfter _
          Text:=vraagtekst & vbCrLf
    
        SQL = "INSERT INTO vraag (" & vraagNaam & ", " & vraagType & ", " & objWord & ", " & onderdeel & ", " & hoofdstuk & ")"
        
        Text1.Text = SQL
        
        'open connection to Access db and run the SQL
        With cnt
            .Open stCon
            .CursorLocation = adUseClient
            .Execute (SQL)
        End With
        'close connection
        
        objRS.MoveNext
    Loop
    
    cnt.Close
    
    'release object from memory
    Set cnt = Nothing

Anyone that can help?

Thanx in advance !
triion

 

[tsuji]

Look into these first?

[1] Take out all the set keywords for the lines with objrs(0)...objrs(4).

[2] Move the two lines
[tt]cnt.open stCon[/tt]
and
[tt]cnt.cursorLocation=adUserCliet[/tt]
outside of the do loop and immediately after the second [tt]set cnt=new adodb.connection[/tt] line, effectively keeping only the [tt]cnt.execute sql[/tt] within the do loop.

[3] The thorough clean up after all the work after the do loop is supposed to be there only not showing.