Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    22

    Unanswered: INSERT data without SQL

    Hi

    I have a large amount of data that i need to insert into a memo field.

    The data is too big for an SQL statement, and i am executing it from VBA, this is the current statement

    "INSERT INTO tblCandidate ([CV Details]) VALUES ('" & wordRange.Text & "') WHERE CandID = " & CandID

    Does anyone know how can i replace this SQL Statement.

    Thanks Si.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Create a Parsed query.
    In code, dynamically change the where clause before running your query.

    Hope this helps.

    set dbs as currentdb()
    set qdf = dbs.querydefs("YourParsedQuery")

    if instr(qdf.sql, " WHERE ") > 0 then
    qdf.sql = left(qdf.sql, instr(qdf.sql," WHERE ") -1) & " WHERE " & stYourWhereClause & ";"
    else
    qdf.sql = left(qdf.sql, len(qdf.sql) - 1) & " WHERE " & stYourWhereClause & ";"
    endif
    qdf.execute
    qdf.close

  3. #3
    Join Date
    Oct 2003
    Posts
    22
    Hi

    I dont understand how this will help me..

    Sorry i may be missing the point.

    When I execute an SQL statement using query defs, ado or dao i get the "missing semi colon" error. Which i usually get when the SQL is too long.

    Thanks Si

  4. #4
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    Are you exceeding the amount of data you can put into a memo field?

    Just a thought

  5. #5
    Join Date
    Oct 2003
    Posts
    22
    Sorry,

    The problem is due to the character length of the SQL statement.

    Im not an access expert, but i know there is some sort of way of updating a record without using SQL...

    Any ideas?

    Thanks Si

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    A parsed query can handle longer sql statements than can running sql in vb code. If your problem is indeed that your sql statement is too long, then my solution may just do the trick. I don't remember ever getting a Missing semi colon error, but maybe others have.

  7. #7
    Join Date
    Oct 2003
    Posts
    22
    Sorry, i didnt realise that the parsed query would do this.

    i have used this code, but i think i am very wrong:

    Dim qdf As QueryDef
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryInsertCVText")
    strSQL = "INSERT INTO " & strDatabase & " ([CV Details]) VALUES ('" & wordRange.Text & "')"
    qdf.SQL = strSQL
    If InStr(qdf.SQL, " WHERE ") > 0 Then
    qdf.SQL = Left(qdf.SQL, InStr(qdf.SQL, " WHERE ") - 1) & " WHERE " & " CandID = " & CandID & ";" & ";"
    Else
    qdf.SQL = Left(qdf.SQL, Len(qdf.SQL) - 1) & " WHERE " & " CandID = " & CandID & ";" & ";"
    End If
    qdf.Execute

    Thanks Si

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You would have to create the qryInsertCVText query with either a generic Where clause or no where clause. You can use the Query By Example tool for this.

    You can then delete these two lines:

    strSQL = "INSERT INTO " & strDatabase & " ([CV Details]) VALUES ('" & wordRange.Text & "')"
    qdf.SQL = strSQL

    You would end up with this:

    Dim qdf As QueryDef
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryInsertCVText")
    If InStr(qdf.SQL, " WHERE ") > 0 Then
    qdf.SQL = Left(qdf.SQL, InStr(qdf.SQL, " WHERE ") - 1) & " WHERE " & " CandID = " & CandID & ";" & ";"
    Else
    qdf.SQL = Left(qdf.SQL, Len(qdf.SQL) - 1) & " WHERE " & " CandID = " & CandID & ";" & ";"
    End If
    qdf.Execute

  9. #9
    Join Date
    Oct 2003
    Posts
    22
    Following that i have this code, and on the line after the ELSE Statement i get the errror: "Characters found after end of SQL statement":

    wordRange.Text = Replace(wordRange.Text, "'", "")
    wordRange.Text = Replace(wordRange.Text, ";", "")
    Dim qdf As QueryDef
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryInsertCVText")
    If InStr(qdf.SQL, " WHERE ") > 0 Then
    qdf.SQL = Left(qdf.SQL, InStr(qdf.SQL, " WHERE ") - 1) & " WHERE " & " CandID = " & CandID & ";"
    Else
    qdf.SQL = Left(qdf.SQL, Len(qdf.SQL) - 1) & " WHERE " & " CandID = " & CandID & ";"
    End If
    qdf.Execute

  10. #10
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The Len(qdf.SQL) - 1 may need to be Len(qdf.SQL) - 2 or Len(qdf.SQL) - 3

    pause during code execution and type ? qdf.sql then press enter in the debug window and check what is at the end of the sql statement. Adjust the -1 accordingly.

    Or you could post the last 20 characters here if you need more help.

  11. #11
    Join Date
    Oct 2003
    Posts
    22
    Hi,

    Sorry about the delay in replying

    This is the end of the SQL:

    MANAGING THE DEVELOPMENT TEAM OF TWELVE PROGRAMMERS BUILDING A CONTAINER SHIPPING MANAGEMENT SYSTEM. ALSO CARRIED OUT A REVIEW OF THE SUITABILITY OF SQL FOR END USERS. (VME/VSAM/IMS).

    ');

    The only problem that i can see is that the change in the query comes in two areas:

    "INSERT INTO tblCandidate ([CV Details]) VALUES ('" & wordRange.Text & "') WHERE CandID = " & CandID

    firstly: the large amount of data comes from: wordRange.Text

    Secondly about 4 chars from CandID

    I cant see wordRange.Text in the parse function

    Thanks Si.

  12. #12
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    I think in an access memo field you can only store and get "normal" access to the maximum of 256 chars.

  13. #13
    Join Date
    Oct 2003
    Posts
    22
    256 is for a text field, isnt it?

    I do have a field in access with well over 1000 chars in it.

    I think there is a limit to SQL statement size at about 9020 characters.

    Thanks Si

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Simon,

    To update/insert a memo field your best bet is to use a recordset. Example:

    Code:
        Dim MyConnect As ADODB.Connection
        Dim MyRecSet As ADODB.Recordset
    
        Set MyConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
    
        SQLString = "SELECT * FROM [My Table Name Here] WHERE (1=0);"
        MyRecSet.Open SQLString, MyConnect
        MyRecSet.AddNew
        MyRecSet.Fields(0).AppendChunk (TextFieldTxt & "")
        MyRecSet.Update
        MyRecSet.Close
    
        MyConnect.Close
        Set MyRecSet = Nothing
        Set MyConnect = Nothing

  15. #15
    Join Date
    Oct 2003
    Posts
    22
    I had to change the tables slightly and used a similar method that you have posted.

    Thanks everyone who helped me!

    Cheers Si

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •