Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29

    Unhappy Unanswered: What's wrong with my SQL query

    it seems that none of theses work becuse of the date and time format in access:

    INSERT INTO Tracking ( [Date], [Time], [User], [Table], Description, Previous, [Current], ChangeType ) VALUES ( 2/17/2006, 8:59:36 AM, Arelowo.Alao, FTE Allocation, Region 1; Dept: Not Applicable, 11, 10, Edit )

    INSERT INTO Tracking ( [Date], Time, [User], [Table], Description, Previous, [Current], ChangeType ) VALUES ( #2/17/2006#, #9:07:29 AM#, "Arelowo.Alao", "FTE Allocation", "Region 1; Dept: Not Applicable", "11", "11", "Edit" )

    INSERT INTO Tracking ( [Date], [Time], [User], [Table], Description, Previous, [Current], ChangeType ) VALUES ( #2/17/2006 9:16:37 AM#, #2/17/2006 9:16:37 AM#, "Arelowo.Alao", "FTE Allocation", "Region 1; Dept: Not Applicable", "11", "10", "Edit" )

    INSERT INTO Tracking ( [Date], [Time], [User], [Table], Description, Previous, [Current], ChangeType ) VALUES ( #2/17/2006#, #09:15#, "Arelowo.Alao", "FTE Allocation", "Region 1; Dept: Not Applicable", "11", "11", "Edit" )

    INSERT INTO Tracking ( [Date], [Time], [User], [Table], Description, Previous, [Current], ChangeType ) VALUES ( #2/17/2006, #9:16#, #2/17/2006 9:16:37 AM#, "Arelowo.Alao", "FTE Allocation", "Region 1; Dept: Not Applicable", "11", "10", "Edit" )

    What am i doing wrong?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by alaoa
    it seems that none of theses work becuse of the date and time format in access:
    I presume then you can confirm that:
    Code:
    INSERT INTO Tracking ([User], [Table], Description, Previous, [Current], ChangeType ) 
    VALUES 
    ( "Arelowo.Alao", "FTE Allocation", "Region 1; Dept: Not Applicable", "11", "11", "Edit" )
    works and
    Code:
    INSERT INTO Tracking ( [Date], Time) 
    VALUES 
    ( #2/17/2006#, #9:07:29 AM#)
    fails? (assuming there are no constraints to account for )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29
    I figured out why my SQL query dosn't work. It is becuase of the connection that i am using. It says that the current recordset does not support updating. this may be a limitation ofthe provider or selected lock type. Here is what i am using

    Code:
       Set MyConn = New ADODB.Connection
       
       connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source = M:\OCR Applications\FTE Database\FTE Folder\FTE_Backend.mdb;" & _
                 "Jet OLEDB:Database Locking Mode=0;" & _
                 "Jet OLEDB:Database Password=" & quotedStr("password")
       MyConn.CursorLocation = adUseClient
       MyConn.mode = adModeReadWrite
       MyConn.open connstr, "Admin"
    I tried adUseServer and that didn't work either

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can use the supports function to see what your recordset supports.

    Did you see the code I wrote for you a week or two ago? It included writing with ado recordsets. I don't even know what the mode property means lol so I presume (unless you know more than me) that you don't need to worry about that. More problematic is usually the provider and the CursorType.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This the reason why I DO NOT USE hardcoded DB paths ... I go with DSNs ... Ex:
    Code:
    Function EstablishDBConnection() As Integer
        On Error GoTo Err_EDBC
        
        Set MyConnect = New ADODB.Connection
        Set ArchConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
        
        MyConnect.CursorLocation = adUseClient
        MyConnect.Open "DSN=Billing System;"
        
        ArchConnect.CursorLocation = adUseClient
        ArchConnect.Open "DSN=Billing Archive;"
        
        MyRecSet.CursorType = adOpenDynamic
        MyRecSet.LockType = adLockOptimistic
        MyRecSet.CursorLocation = adUseClient
        
    EstablishDBConnection = 1
    Exit_EDBC:
        Exit Function
        
    Err_EDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishDBConnection = 0
        Resume Exit_EDBC
    End Function
    See how much easier ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29
    I just looked at your code again. It seems that i have to disconnect the record set from the backend, update the records locally, reconnect it to the backend, then call an update batch. I have a feeling that this might work becuse when i tried to use .addNew while it was connected to the backend it would give me that same error about the provider does not support updating. I'm goign to try this!!!!!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Grand Poobah
    This the reason why I DO NOT USE hardcoded DB paths ... I go with DSNs ...
    I kind of have the same thing... but not.

    I just use a function to instantiate all my connections.

    Question - how do you handle people not having the correct DSN or is that just a deployment issue?

    If you open a (file - not sure about the others) DSN in notepad it is really no more than a connection string created for you by the wizard.... as such I haven't really got my head round the advantage of using them.

    ???
    Last edited by pootle flump; 02-17-06 at 13:56.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by alaoa
    I just looked at your code again. It seems that i have to disconnect the record set from the backend, update the records locally, reconnect it to the backend, then call an update batch. I have a feeling that this might work becuse when i tried to use .addNew while it was connected to the backend it would give me that same error about the provider does not support updating. I'm goign to try this!!!!!
    Aha - the example was specifically for client side alterations.

    Hang on - my example was for recordsets - apols - totally different thing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2006
    Location
    Washington, D.C
    Posts
    29
    It's still saying the same thing. Do i have to specify the locking type within my connection string?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - attached is an update to the code I posted last time. It includes my latest take on set based updating using ADO & SQL that I've been playing with recently. I'm open to comments - I just like to reduce the # of calls to the database to a minimum. It would be easier, of course, to insert on each pass.

    Anyway - it demonstrates a working ADO remote insert regardless on whether or not you think the SQL is preposterous

    HTH
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I kind of have the same thing... but not.

    I just use a function to instantiate all my connections.

    Question - how do you handle people not having the correct DSN or is that just a deployment issue?

    If you open a (file - not sure about the others) DSN in notepad it is really no more than a connection string created for you by the wizard.... as such I haven't really got my head round the advantage of using them.

    ???
    It's a deployment issue ... There are ways to programmatically create them ... Of course, if you do, I recommend that System DSN be created (for obvious reasons)

    The only real advantage is not having a hardcoded path that has to be changed on each client when the database moves or is renamed ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by alaoa
    It's still saying the same thing. Do i have to specify the locking type within my connection string?
    You shouldn't set the locking on the connection. Do that in your recordset ...
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    The only real advantage is not having a hardcoded path that has to be changed on each client when the database moves or is renamed ...
    But don't you still have the problem of having to alter the DSN? Isn't the point more about having one single file\ string for connection information rather than whether it is contained in an ini, DSN, config file or hardcoded into a function?
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

    File DSNs can be opened with the editor of your choice obviously ... I have never had a need to use one tho (file DSN) ... I've played with them and found them lacking for what I needed. They are useful when you're dealing with a special/custom database architecture not supported by a major DB or DB engine ... Say like a custom flat file DB with a separate offset indexing scheme ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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