Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Strange problems, only works sometimes

    I have a little vb code that I wrote that goes through linked tables to my MS Outlook e-mails and sorts them to print all the new ones. The code works fine until it gets to an e-mail it doesn't like, then it gives me run-time error 3075 and stops. I can't fingure out what it is in the text of the email that it doesn't like (special characters maybe). Any help would be appreciated, here is the code:

    Private Sub Command0_Click()
    DoCmd.SetWarnings False

    Dim intRowTables As Integer
    Dim strTable As String
    Dim dateLastRun As Date
    dateLastRun = DLookup("tblLastRun.LastRun", "tblLastRun")
    Dim strFrom, strTo, strCC, strSubject, strAttachments, strContents As String
    Dim dateReceived As Date

    Dim rsItems
    Set rsIems = New ADODB.Recordset
    rsItems.ActiveConnection = CurrentProject.Connection

    For intRowTables = 0 To Me.lsbTables.ListCount - 1 'This is a table that has the other tables names in it
    DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp"
    strTable = Me.lsbTables.Column(0, intRowTables)
    rsItems.Open "SELECT * FROM " & strTable & ""

    intRowEmails = 0
    Do While Not rsItems.EOF
    If rsItems![Received] > dateLastRun Then
    Me.Text21 = rsItems![Contents]
    DoCmd.RunSQL " INSERT INTO tblTemp ( [From] , [To] , [CC] ," & _
    " [Received] , [Subject] , [Has Attachments] , [Contents] )" & _
    " VALUES ('" & rsItems![From] & "','" & rsItems![To] & "'," & _
    "'" & rsItems![CC] & "',#" & rsItems![Received] & "#,'" & rsItems![Subject] & "'," & _
    "'" & rsItems![Has Attachments] & "','" & rsItems![Contents] & "')"
    End If
    rsItems.MoveNext
    Loop
    DoCmd.OpenReport "rptEmails", acViewPreview, , , acWindowNormal
    rsItems.Close
    Next

    DoCmd.RunSQL "UPDATE tblLastRun SET tblLastRun.LastRun = Now();"
    Set rsItems = Nothing
    DoCmd.SetWarnings True
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What line does it bomb on, and what values does your recordset hold when it breaks?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    DoCmd.RunSQL " INSERT INTO tblTemp ( [From] , [To] , [CC] ," & _
    " [Received] , [Subject] , [Has Attachments] , [Contents] )" & _
    " VALUES ('" & rsItems![From] & "','" & rsItems![To] & "'," & _
    "'" & rsItems![CC] & "',#" & rsItems![Received] & "#,'" & rsItems![Subject] & "'," & _
    "'" & rsItems![Has Attachments] & "','" & rsItems![Contents] & "')"

    is where it craps out. I found the problem thought, it was special characters. Whenever there is an apostrophy or single quote (') in the text it doesn't like it. Any thoughts on how to avoid this problems?

    Thanks

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by accessnoobie
    DoCmd.RunSQL " INSERT INTO tblTemp ( [From] , [To] , [CC] ," & _
    " [Received] , [Subject] , [Has Attachments] , [Contents] )" & _
    " VALUES ('" & rsItems![From] & "','" & rsItems![To] & "'," & _
    "'" & rsItems![CC] & "',#" & rsItems![Received] & "#,'" & rsItems![Subject] & "'," & _
    "'" & rsItems![Has Attachments] & "','" & rsItems![Contents] & "')"

    is where it craps out. I found the problem thought, it was special characters. Whenever there is an apostrophy or single quote (') in the text it doesn't like it. Any thoughts on how to avoid this problems?

    Thanks
    Yeah ... Use a recordset.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    Can you elaborate? Are you saying run a report on a record set? How do you do that?

    Thanks

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I think he means instead of using RunSQL command, you should open another recordset to tbltemp insert VALUES straight into it with fields controls. quotes have a special use in SQL and this is the best way you can avoid that problem.
    ghozy.

  7. #7
    Join Date
    Feb 2004
    Posts
    42
    I don't suppose you could help me out with what the code would look like for that?!?! I think I get the concept, just not sure how that would actually work.

    Thanks!

  8. #8
    Join Date
    Dec 2004
    Location
    Maryland
    Posts
    5
    It might look something like this:

    Dim Db As Database
    Dim rsTemp As DAO.Recordset
    Dim strSQL As String

    Set Db = CurrentDb

    strSQL = "Select * From tblTemp"

    Set rsTemp = Db.OpenRecordset(strSQL)

    'Of course rsItems has to be set in here somewhere
    'If it contains more than one record you might want to put it in a loop

    With rsTemp
    .AddNew
    !From = rsItems!From
    !To = rsItems!To
    !CC = rsItems!CC
    !Received = rsItems!Received
    !Subject = rsItems!Subject
    ![Has Attachments] = rsItems![Has Attachments]
    !Contents = rsItems!Contents
    .Update
    .Close
    End With
    Last edited by gearybish; 01-19-05 at 20:01.

  9. #9
    Join Date
    Jan 2005
    Posts
    8
    If you have the chance of a ' in your field, then you also can try enclosing the field with """ """

    ie:

    INSERT INTO pyaudi (XYZXYZXYZX) VALUES (""" & cAuditString & """)

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    yes, but it also leads the problems if there is double quote's in the subject or email field. which very likely.
    ghozy.

Posting Permissions

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