Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Access SQL Error

    HI:

    Win2k, XP
    Access2k

    I have an SQL that runs well but sometimes for unknown reasons, at one point I get the attached error.

    Here is the code:
    Code:
    Sub UpdateDB()
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim AppPath, app As String
        
        AppPath = "c:\LOGCALL\LOGCALL.mdb"
        Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
        conn.ConnectionTimeout = 30
        conn.Open
        
        Set cmd.ActiveConnection = conn
        'When the call is RESOLVED
        If Range("C" & CStr(ActiveCell.Row)).Value = "X" And Range("G" & CStr(ActiveCell.Row)).Value <> "DUPLICATE CALL" Then
            cmd.CommandText = "INSERT INTO LOGCALL_table VALUES ('" _
            & Range("B" & CStr(ActiveCell.Row)).Value & _
            "','" & Range("C" & CStr(ActiveCell.Row)).Value & _
            "',NULL,NULL,NULL,NULL,'" & Format(Range("H" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Range("C1").Value & "','" & Format(Date, "MM/DD/YYYY") & "');"
        End If
    
    cmd.Execute , , adCmdText
        conn.Close
    
    End Sub
    The DB has no primary keys, no required fields and logcall_table is the only table in the dB. With the exception of this one error, it inserts, update and deletes without difficulties.

    Does anyone have any idea why I got this?
    Attached Thumbnails Attached Thumbnails dberror.bmp  
    Last edited by Alexxx12; 09-21-04 at 12:09.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hey, maybe you should not blank out the query so we can see what part is erroring out? It's a problem somewhere around here:
    VALUES ('" _
    & Range("B" & CStr(ActiveCell.Row)).Value & _
    "','" & Range("C" & CStr(ActiveCell.Row)).Value & _
    "',NULL,NULL,NULL,NULL,

    Dump that command text to a text box or msgbox so you can see what you're dealing with. It's a problem with the query.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Access SQL error

    HI,

    I got the same error with this SQL update statement: The problem is with the date in the last field. I tried it without it and it worked. However, I need the date in order to be specific.



    Code:
    Sub UPDATE_ENTRY_IN_DB()
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim AppPath, app As String
        Dim MyDate As Date
        
        MyDate = Format(Date, "MM/DD/YYYY")
        
        AppPath = "c:\LOGCALL\LOGCALL.mdb"
        Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
        conn.ConnectionTimeout = 30
        conn.Open
        
        Set cmd.ActiveConnection = conn
        cmd.CommandText = "UPDATE LOGCALL_table SET ResolvedCall = 'X' WHERE ClientName = '" & Range("B" & CStr(ActiveCell.Row)).Value & "'AND Representative = '" & Range("C1").Value & "' AND DateOnCall = '" & Format(Date, "MM/DD/YYYY") & "';"
        cmd.Execute , , adCmdText
        conn.Close
    End Sub
    I tried to dim MyDate as date variable and replace it :
    Code:
    .
     .....AND DateOnCall = '" & Format(Date, "MM/DD/YYYY") & "'
    
    .....AND DateOnCall = '" & MyDate & "'
    
    .
    with Mydate but that did not work either.

    Any input would be greatly appriciated.



    Alexxx

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    have you try this :
    AND DateOnCall = #" & MyDate & "#"

Posting Permissions

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