Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query in access mdb but not work with my param...
    In effet i want select DESCR_COD_4 and SETTORE only if SETORE is not null and DESCR_COD_4 like FILIALE....
    The query extract the record but have 403 rset SETTORE blank...????

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sal21' post='770532' date='14-Apr-2009 23:08'][/quote]
    Use the following query :
    SELECT STAFF.DESCR_COD_4, STAFF.SETTORE
    FROM STAFF
    WHERE (((STAFF.DESCR_COD_4) Like '*FILIALE*') AND ((Len([settore]))<>0))
    ORDER BY STAFF.SETTORE;

    See attachment
    [attachment=83326B_PAST_DUE.zip]
    Attached Files Attached Files
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    tK! WORK NOW...

    But, i have maked this piece of code to call the query directlly in access mdb, and when the code loop the RST have the value of other table...!!!!!?????in debug.print

    In effect i want to call the query in access mdb and loop the 2 rset

    module1
    Option Explicit
    Public RST As New ADODB.Recordset
    Public CNSQL As New ADODB.Connection
    Public CMD As New ADODB.Command

    module2
    Option Explicit
    Sub QUERY_ACCESS()

    On Error GoTo errore

    If Not CNSQL.State = adStateClosed Then
    CNSQL.Close
    End If

    CNSQL.CursorLocation = adUseClient
    CNSQL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\APPLICAZIONI\DB_PAST_DUE.mdb;"

    CMD.ActiveConnection = CNSQL
    CMD.CommandText = "STAFF Query"
    CMD.CommandType = adCmdUnknown

    Set RST = CMD.Execute

    Do Until RST.EOF
    Debug.Print Trim(RST!DESCR_COD_4)
    Debug.Print Trim(RST!SETTORE)
    RST.MoveNext
    Loop

    Exit Sub

    errore:
    MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & _
    "Descrizione: " & Err.Description & vbCrLf & _
    "Sorgente dell'Errore: " & Err.Source

    Err.Clear

    End Sub

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sal21' post='770581' date='15-Apr-2009 11:02'][/quote]
    If you want to call the query with ADO, you must change the wildcard character * to % :
    SELECT STAFF.DESCR_COD_4, STAFF.SETTORE
    FROM STAFF
    WHERE (((STAFF.DESCR_COD_4) Like '%FILIALE%') AND ((Len([settore]))<>0))
    ORDER BY STAFF.SETTORE;

    You can store this in Access as a query, but if you run the query, it will give you no records.
    Francois

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' date='15-Apr-2009 12:53' post='770588']
    [quote name='sal21' post='770581' date='15-Apr-2009 11:02']
    If you want to call the query with ADO, you must change the wildcard character * to % :
    SELECT STAFF.DESCR_COD_4, STAFF.SETTORE
    FROM STAFF
    WHERE (((STAFF.DESCR_COD_4) Like '%FILIALE%') AND ((Len([settore]))<>0))
    ORDER BY STAFF.SETTORE;

    You can store this in Access as a query, but if you run the query, it will give you no records.

    OK!
    But to loop all rset DESCR_COD_4 and SETTORE how to?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sal21' post='770590' date='15-Apr-2009 13:22']But to loop all rset DESCR_COD_4 and SETTORE how to?[/quote]
    Like you did for the table but with the query (with the %)

    CMD.CommandText = "RIEPILOGO"
    Set RST = CMD.Execute
    Do Until RST.EOF
    Debug.Print Trim(RST!DESCR_COD_4)
    Debug.Print Trim(RST!SETTORE)
    RST.MoveNext
    Loop
    Francois

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' post='770591' date='15-Apr-2009 13:38']Like you did for the table but with the query (with the %)

    CMD.CommandText = "RIEPILOGO"
    Set RST = CMD.Execute
    Do Until RST.EOF
    Debug.Print Trim(RST!DESCR_COD_4)
    Debug.Print Trim(RST!SETTORE)
    RST.MoveNext
    Loop[/quote]

    NOW WORK PERFECT!!!!!

    ... but is a good idea to loop and work with rset directly with the query stored in access mdb?
    In effect i want to speed up the operation if the mdb is on a lan dir....
    i have tested:
    if i run athe sql query via vb6 code (similar: selet* bbb from ecc...) the operation on rset are very very slow

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A stored query is generally faster than SQL code generated dynamically. The slowness is probably due to the number of records. If this is very large, consider migrating the data from Access to SQL Server.

Posting Permissions

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