Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: ADO inconsistent btw Excel 2K3 and Access 2K

    mkggoh is running ADODB calls from Excel to an Access 2000 database. Worked dandy until some XP machines with Excel 2003 came into the mix.
    Quote Originally Posted by mkggoh
    a function which make a call to

    Command As ADODB.Command
    Command.CommandText = CommandText 'here is where i provide the SQL statement
    Command.Execute(....)

    here is where it prompt the error when i executing the SQL statement, and the weird thing is, not every time it fails, it will success accessing some tables inside the same database, and the failure accessing table will be differ from machines.

    eg. machines A might only can access table A, but machines B can only access table B, but successful to get data from table A (in the same database), my guess is the new version of dll (version 11, if i m not mistaken, this dll is for office 2003) cannot be used on office 2000 (to use access 2000 db might have a problem)
    The original thread is HERE.

    This doesn't look like a DLL problem to me as explained in my last reply:
    Quote Originally Posted by tcace
    What you described does not make sense to me - I have not ever seen it happen as you spelled out. I don't think it is simply the dll because you said PC 1 can get tables A but not B while PC 2 can get B but not A. The dll would choke on the SAME tables in each case if that was causing a compatibility issue.
    Anybody have an idea?

    tc
    Last edited by tcace; 07-21-06 at 00:58.

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Just want to add in new info..

    In addition, if i tried to insert SQL statement, it will insert the record i wanted even it prompted me error occur when inserting. (ie, the function successful insert record but still the return value is indicating failed), and this does not happen when i use office2000 to run my program

    if i only use 'select' SQL statement, it will fail on some tables, but not all.
    Last edited by mkggoh; 07-21-06 at 04:00.

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In such case, the Office version should not matter at all, as one is not connecting to an "Access database", but a Jet database. One would not be using Access, but the Jet engine through the OLEDB provider.

    I quickly scanned the thread.

    There is little/no code to be able to reproduce the behaviour.
    There is no actual errormessage, we don't know what error.
    My guess, is that there's some (unnecessary?) automation going on, which might give reference hassle - but again, very hard to spot when there's no code to make us able to reproduce it.

    Would there be any difference between running pure SQL queries vs queries with VBA functions?

    Edit: Ensure also all computers are running latest Jet SP/MDAC http://msdn.microsoft.com/data/ref/mdac/downloads/
    Last edited by RoyVidar; 07-21-06 at 03:14.
    Roy-Vidar

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    to help on further clearification, here is part of the code that i have on my excel marco, this only one insert function. My tempalte will

    Public Const DBSource As String = "C:\db\ServiceFailure.mdb"

    Public Function InsertRecordForFailureFromInputSheet(ByVal Command As ADODB.Command, ByVal rowNum As Long) As Boolean
    On Error GoTo errorHandler

    Dim DateLog, DateInc, DateRes As String
    Dim Wk1 As String
    Dim dummyDate As String

    dummyDate = "01/01/1900"
    Wk1 = "Input"
    Worksheets(Wk1).Select
    Dim departmentResponsible As String 'department
    If Worksheets(Wk1).range("C9").Value <> "" Then
    departmentResponsible = Worksheets(Wk1).range("C9").Value
    Else
    departmentResponsible = Worksheets(Wk1).range("C11").Value
    End If

    Dim dateLogged As Date

    If ActiveSheet.LogD.Value <> "" And ActiveSheet.LogM.Value <> "" And ActiveSheet.LogY.Value <> "" Then
    DateLog = (ActiveSheet.LogD.Value & "/" & ActiveSheet.LogM.Value & "/" & ActiveSheet.LogY.Value) 'date log
    Else
    DateLog = dummyDate
    End If 'Date logged
    dateLogged = CDate(DateLog)

    Dim description As String

    ActiveSheet.Incident.Value = filterOutChrInStr("'", CStr(ActiveSheet.Incident.Value))
    description = ActiveSheet.Incident.Value
    Dim SOCFailure As String
    SOCFailure = ActiveSheet.range("F9").Value
    Dim statusOfIncident As String
    statusOfIncident = ActiveSheet.range("F11").Value
    Dim errorCategories As String
    errorCategories = ActiveSheet.range("F13").Value
    Dim remitter As String
    Worksheets(Wk1).range("I11").Value = filterOutChrInStr("'", CStr(Worksheets(Wk1).range("I11").Value))
    remitter = ActiveSheet.range("I11").Value

    Dim staffLogging As String 'Staff Logging
    If ActiveSheet.range("C19").Value <> "" Then
    staffLogging = ActiveSheet.range("C19").Value
    Else
    staffLogging = ActiveSheet.range("C21").Value
    End If

    Dim customerSegment As String 'Customer Segment
    customerSegment = ActiveSheet.range("F19").Value
    Dim informedBy As String
    Worksheets(Wk1).range("I19").Value = filterOutChrInStr("'", CStr(Worksheets(Wk1).range("I19").Value))
    informedBy = ActiveSheet.range("I19").Value

    Dim responsibleStaff As String 'Responsible Staff
    If Worksheets(Wk1).range("C23").Value <> "" Then
    responsibleStaff = Worksheets(Wk1).range("C23").Value
    Else
    responsibleStaff = Worksheets(Wk1).range("C25").Value
    End If

    Dim actionTaken As String 'action taken to resolved
    ActiveSheet.ResolveAction.Value = filterOutChrInStr("'", CStr(ActiveSheet.ResolveAction.Value))
    actionTaken = Worksheets(Wk1).ResolveAction.Value
    Dim processType As String 'process type
    processType = Worksheets(Wk1).range("F23").Value

    If ((Worksheets(Wk1).IncD.Value) <> "" And (Worksheets(Wk1).IncM.Value) <> "" And (Worksheets(Wk1).IncY.Value) <> "") Then
    DateInc = CDate(Worksheets(Wk1).IncD.Value & "/" & Worksheets(Wk1).IncM.Value & "/" & Worksheets(Wk1).IncY.Value) 'date inc
    Else
    DateInc = dummyDate
    End If 'date inc
    Dim dateOfIncident As Date
    dateOfIncident = CDate(DateInc)

    Dim CFSSNo As String
    Worksheets(Wk1).range("I9").Value = filterOutChrInStr("'", CStr(Worksheets(Wk1).range("I9").Value))
    CFSSNo = CStr(Worksheets(Wk1).range("I9").Value) 'CFSSNo
    Dim impactedCustomerCount As String
    Worksheets(Wk1).range("I25").Value = filterOutChrInStr("'", CStr(Worksheets(Wk1).range("I25").Value))
    impactedCustomerCount = CStr(Worksheets(Wk1).range("I25").Value) 'impacted customer count

    If ((Worksheets(Wk1).ResD.Value) <> "" And (Worksheets(Wk1).ResM.Value) <> "" And (Worksheets(Wk1).ResY.Value) <> "") Then
    DateRes = CDate(Worksheets(Wk1).ResD.Value & "/" & Worksheets(Wk1).ResM.Value & "/" & Worksheets(Wk1).ResY.Value) 'date res
    Else
    DateRes = dummyDate
    End If 'date res
    Dim dateOfResolution As Date
    dateOfResolution = CDate(DateRes)

    Dim complaints As String
    complaints = Worksheets(Wk1).range("I31").Value
    Dim actionToPrevent As String
    ActiveSheet.PreventAction.Value = filterOutChrInStr("'", CStr(ActiveSheet.PreventAction.Value))
    actionToPrevent = Worksheets(Wk1).PreventAction.Value
    Dim awardedStars As String
    awardedStars = Worksheets(Wk1).range("C37").Value
    Dim managerComment As String
    ActiveSheet.Manager.Value = filterOutChrInStr("'", CStr(ActiveSheet.Manager.Value))
    managerComment = Worksheets(Wk1).Manager.Value
    Dim CaseID As String
    CaseID = CStr(ActiveSheet.range("C6").Value) 'Case ID / Snags

    Dim complaintNo As String
    complaintNo = generateComplaintNo
    If complaintNo = "" Then
    MsgBox "Fail to generate the complaint No."
    Exit Function
    End If

    'If (Worksheets(Wk1).range("B4").Value = "SOC-CSU") Then
    ' complaintNo = ("CSU-" & (AddressRow - 3)) 'Complaint No
    'Else
    ' complaintNo = (Mid(dept, 5, Len(dept)) & "-" & (AddressRow - 3)) 'Complaint No
    'End If

    'departmentResponsible
    'Dim successfulRefreshFlag As Boolean

    'successfulRefreshFlag = GetDeptRecordCount("Failure", "Number Of Today Records", "A1", departmentResponsible)
    'If Not successfulRefreshFlag Then
    ' MsgBox "refresh records failed"
    ' Exit Function
    'End If
    'Dim deptLogging As String
    'deptLogging = Worksheets("Input").range("D4").Value

    'If deptLogging = "" Then
    ' complaintNo = departmentResponsible & "-" & CStr(Worksheets("Number Of Today Records").range("A1").Value) + 1
    'Else
    ' complaintNo = deptLogging & "-" & departmentResponsible & "-" & CStr(Worksheets("Number Of Today Records").range("A1").Value) + 1
    'End If

    Dim CommandText As String

    CommandText = "INSERT INTO Failure (" & _
    "Department, DateLogged, DescriptionOfIncident, SOCFailure, StatusOfIncident, FailureCategories, CustomerName, StaffLogging, CustomerSegment," & _
    "InformedBy, ResponsibleStaff, ActionTakenToResolved, Process, IncidentDate, CFSSNo, ImpactedCustomerCount," & _
    "DateOfResolved, Losses, Complaints, ActionTakenToPrevent, AwardedStars, LineManagerComment, Snags, ComplaintNo" & _
    ") Values ('" & _
    departmentResponsible & "','" & dateLogged & "','" & description & "','" & SOCFailure & "','" & statusOfIncident & _
    "','" & errorCategories & "','" & remitter & "','" & staffLogging & "','" & customerSegment & "','" & informedBy & _
    "','" & responsibleStaff & "','" & actionTaken & "','" & processType & "','" & dateOfIncident & "','" & CFSSNo & _
    "','" & impactedCustomerCount & "','" & dateOfResolution & "','" & losses & "','" & complaints & "','" & actionToPrevent & "','" & awardedStars & _
    "','" & managerComment & "','" & CaseID & "','" & complaintNo & "')"

    'MsgBox CommandText
    'Const Description As String = "Error Executing UPDATE statement"

    Call ExecuteCommand(Command, CommandText, description)
    InsertRecordForFailureFromInputSheet = True
    MsgBox "Record " & complaintNo & " has been added into the database!"
    Exit Function
    errorHandler:
    MsgBox "Fail to insert failure table from input sheet"
    InsertRecordForFailureFromInputSheet = False
    End Function

    Public Sub ExecuteCommand(ByVal Command As ADODB.Command, _
    ByVal CommandText As String, _
    ByVal description As String)
    Dim RecordsAffected As Long
    Command.CommandText = CommandText
    Call Command.Execute(RecordsAffected, , _
    CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
    Call checkerror(RecordsAffected, 1, description)
    End Sub

    Public Property Get ConnectionString() As String
    ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=" & DBSource & ";Persist Security Info= False"
    End Property
    maybe is the provider need a new Jet oledb version?

  5. #5
    Join Date
    Jun 2006
    Posts
    103
    Public Property Get ConnectionString() As String
    ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=" & DBSource & ";Persist Security Info= False"
    End Property
    but if it is the jet version causing the problem as royVidar predicted, then how to explaint some other program which use the same version of jet sp, has no problem on accessing Access 2000, and the vba code that used are the same, because is written by me also. the only difference is different database (i mean different file name .mdb but same version of access db, is 2k).

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agree with everything Roy said. The office version is irrelevent. Check you have the latest MDAC and JET packs. Also - check both apps are referencing the same ADO library.

    Once you have checked everything is spot on could you confirm that the same code (copied and pasted - identical) run on the same PC in both apps still errors. Basically strip out all the extraneous stuff (writing to worksheets and things) and just try to populate a recordset.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2006
    Posts
    103
    If i want to check the pc's latest version of Jet pack, how can i do so?

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Though ridiculed by other developers for it, it seems Access people usually confirm to something closer to the Leszynski/Reddick naming convention. See for instance http://msdn.microsoft.com/archive/de...n_20naming.asp for more info. Regardless of what one thinks about this naming convention, it does help in avoiding reserved words as names of variables etc, which VBA can be a bit "ticlish" about.

    A couple of lists
    http://support.microsoft.com/?kbid=248738
    http://support.microsoft.com/kb/286335/

    Among your variable names, several are reserved words (Description).

    Also, the variable names on you ADO objects ... for instance Command is a function, as well as an ADO command object (I'll usually use cmd, or do some prefixing with something like objCommand, adoCommand, myCommand...). CommandText is a property of the command object, connectionstring is a property of the ado connection ...

    From your SQL, it seems every field is of datatype text (you're using single quotes) - is that true, also for the dates?

    Anyway, you still haven't told us the errormessages ...
    Roy-Vidar

  9. #9
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by RoyVidar
    Though ridiculed by other developers for it, it seems Access people usually confirm to something closer to the Leszynski/Reddick naming convention. See for instance http://msdn.microsoft.com/archive/de...n_20naming.asp for more info. Regardless of what one thinks about this naming convention, it does help in avoiding reserved words as names of variables etc, which VBA can be a bit "ticlish" about.

    A couple of lists
    http://support.microsoft.com/?kbid=248738
    http://support.microsoft.com/kb/286335/

    Among your variable names, several are reserved words (Description).

    Also, the variable names on you ADO objects ... for instance Command is a function, as well as an ADO command object (I'll usually use cmd, or do some prefixing with something like objCommand, adoCommand, myCommand...). CommandText is a property of the command object, connectionstring is a property of the ado connection ...

    From your SQL, it seems every field is of datatype text (you're using single quotes) - is that true, also for the dates?

    Anyway, you still haven't told us the errormessages ...
    To answer your questions, I used to be a C programmer, although this is not the first time i use VB but I am not expert.

    using Command as a variable name, oh well, i didnt know it is a reserve word, i thought VB will stop me for doing so if it does not allow, description, yes, it is a reserver word in Access, but not VB i think, thats why i didnt use description as my table field name, because i encountered problem on inserting last time.

    and for the date, i used ', every time i inserting text, number, and date, i know that text should put ', number should not (from my last experience, long time, 6 years + ago), i forgot how date should be, but seems like the database allowed me to do that, so i just let it be, i am not sure if it will cause problem when turning into new version, i thought the new version should be better than the old.

    Now the last question, error message, thanks for pointing this out, i tried to print out the error message, it seems is not i fail on accessing the database, is the excel fail to print the retrieve data back to the sheet. the error message is

    for err.Description = Method 'CopyFromRecordset' of object 'Range' failed.
    for err.Number = -2147467259

    thats explain why i still can insert or update when error occur, insert and update are successful, but then refresh will cause problem, then maybe this is because of new version of excel use different

    here is the function which print data to excel sheet

    'To pull information from the database
    Public Function GetDatabaseFast(ByVal Queryline As String, ByVal sheetName As String, startCell As String) As Boolean

    Dim ConnectionString As String
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=" & DBSource & ";Persist Security Info= False"

    Dim Recordset As ADODB.Recordset
    Set Recordset = New ADODB.Recordset

    Call Recordset.Open(Queryline, ConnectionString, CommandTypeEnum.adCmdText)

    Dim Field As Field
    Dim rg As Range

    Worksheets(sheetName).Activate
    'ActiveSheet.range("A2").Activate
    Set rg = ActiveSheet.Range(startCell)

    If Not Recordset.EOF Then
    rg.CopyFromRecordset Recordset
    rg.CurrentRegion.Columns.AutoFit
    Else
    ActiveCell.FormulaR1C1 = "There is no record in the table"
    If (Recordset.State And ObjectStateEnum.adStateOpen) Then
    Recordset.Close
    End If
    Set Recordset = Nothing
    GetDatabaseFast = False
    Exit Function
    End If

    Worksheets(sheetName).Activate

    If (Recordset.State And ObjectStateEnum.adStateOpen) Then
    Recordset.Close
    End If
    Set Recordset = Nothing
    GetDatabaseFast = True
    End Function
    but one thing for sure, i am able to refresh a table as many time as i wanted using the same function

    Any idea why?

    Thanks in advance

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RoyVidar
    My guess, is that there's some (unnecessary?) automation going on, which might give reference hassle - but again, very hard to spot when there's no code to make us able to reproduce it.
    Cigar for Roy.

    mkggoh - is the last set of code you posted in Access? If so - I think we have found the problem
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh blx - its not is it.

    Can you check if the recordset has recrods before calling the copy from recordset method? It sounds like ADO works fine but the Excel method is failing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by pootle flump
    Oh blx - its not is it.

    Can you check if the recordset has recrods before calling the copy from recordset method? It sounds like ADO works fine but the Excel method is failing.
    isnt this already checked?

    If Not Recordset.EOF Then
    rg.CopyFromRecordset Recordset
    rg.CurrentRegion.Columns.AutoFit
    Else

    end if

  13. #13
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Same with Recordset and Field <g>

    replace with "rs", and see if it helps. It might be that it's the copyfromrecordset method being the culprit. I'm not sure I recall, but perhaps one needs to specify clientside cursor? And I think there were some issues with some MDACS, perhaps needing 2.6 or higher???

    Anyway, to test whether it is the recordset failing, or the copyfromrecordset method, just issue a
    debug.print rs.getstring, which should drop the contents of the recordset into the immediate pane (ctrl+g)

    Also, you seem pass the commandtypeenum as third arguement, which I thought was cursortype, so when passing 1 (adcmdtext), I think you're really passing CursorTypeEnum.adOpenKeyset (quite OK, though - or perhaps static). Anyway, sometimes you need a bit of those, perhaps

    with rs
    .cursortype = adopenkeyset
    .locktype = adlockoptimistic
    .CursorLocation = adUseClient
    .open <sql string>,,,,adcmdtext
    end with
    rg.copyfromrecordset rs
    Roy-Vidar

  14. #14
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by pootle flump
    Cigar for Roy.

    mkggoh - is the last set of code you posted in Access? If so - I think we have found the problem
    no. is in excel marco, i didnt use any coding in Access. all my report, will be printed out on excel sheet.

  15. #15
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by RoyVidar
    Same with Recordset and Field <g>

    replace with "rs", and see if it helps. It might be that it's the copyfromrecordset method being the culprit. I'm not sure I recall, but perhaps one needs to specify clientside cursor? And I think there were some issues with some MDACS, perhaps needing 2.6 or higher???

    Anyway, to test whether it is the recordset failing, or the copyfromrecordset method, just issue a
    debug.print rs.getstring, which should drop the contents of the recordset into the immediate pane (ctrl+g)

    Also, you seem pass the commandtypeenum as third arguement, which I thought was cursortype, so when passing 1 (adcmdtext), I think you're really passing CursorTypeEnum.adOpenKeyset (quite OK, though - or perhaps static). Anyway, sometimes you need a bit of those, perhaps

    with rs
    .cursortype = adopenkeyset
    .locktype = adlockoptimistic
    .CursorLocation = adUseClient
    .open <sql string>,,,,adcmdtext
    end with
    rg.copyfromrecordset rs
    i tried to use debug.print rs.getstring, but then where the computer put the data from the recordset. after executing this code, my excel sheet has no records?


    then i tried using this bit of the code

    with rs
    .cursortype = adopenkeyset
    .locktype = adlockoptimistic
    .CursorLocation = adUseClient
    .open <sql string>,,,,adcmdtext
    end with
    rg.copyfromrecordset rs

    this will totally cannot retrieve any data from any table. the function i have used to get one table as many times as i wanted, but fails on other tables

    changes from Recordset to rs, has no difference

    but at least a good try. thanks

    any more suggestion is most appreciated

    by the way, i m using Microsoft ADO 2.5 Liabary
    Last edited by mkggoh; 07-21-06 at 06:56.

Posting Permissions

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