Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    17

    Unanswered: Run query from code

    I this means open a recordset then I'm opening a recorsdset based on the query:
    Dim MyDB as Database,
    Dim Myquery as recordset

    Set MyDB = CurrentDb()
    Set Myquery = MyDB.OpenRecordset("Temp", DB_OPEN_RECORDSET)

    Thanks
    Korado

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Run query from code

    Originally posted by Korado
    I this means open a recordset then I'm opening a recorsdset based on the query:
    Dim MyDB as Database,
    Dim Myquery as recordset

    Set MyDB = CurrentDb()
    Set Myquery = MyDB.OpenRecordset("Temp", DB_OPEN_RECORDSET)

    Thanks
    Korado
    This is what I usually use :

    Code:
    Dim rs As New ADODB.Recordset
        
    rs.Open "myQueryName", CurrentProject.Connection, adOpenStatic, adLockReadOnly
    You an also write your SQL-statement in VB:

    Code:
    Dim strSQL as String
    Dim rs As New ADODB.Recordset
    
    strSQL = "SELECT * FROM table"
       
    rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

  3. #3
    Join Date
    Jun 2003
    Posts
    17

    Run Query from code

    If I use your code I get the following message:
    "Object or provider is not capable of performing requested action"
    when try to delete record with following sentence:
    rs.delete

    Thanks
    Korad

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Run Query from code

    Originally posted by Korado
    If I use your code I get the following message:
    "Object or provider is not capable of performing requested action"
    when try to delete record with following sentence:
    rs.delete

    Thanks
    Korad
    A recordset contains more then one record.
    If you want it deleted then you write :

    rs.close
    set rs = nothing

    Here is a small example of the use of a recordset:

    There is a table (tblTEST) containing 3 records :

    ID Name Age

    1 Bill 18
    2 John 36
    3 Lisa 54


    Code:
    dim strSQL as sytring
    dim rs as new ADODB.RecordSet
    
    strSQL = "SELECT ID, Name, Age FROM tblTEST"
    
    rs.open strSQL, currentProject.Connection, ,
    
    do while not rs.EOF
         debug.print rs.Fields(0)
         debug.print rs.Fields(1)
         debug.print rs.Fields(2)
    
         rs.moveNext
    Loop
    If ypou want to delete one of these records you have to create a SQL-statement :
    strSQL = "DELETE FROM tblTEST WHERE ID = " & theIDOfTheRecordYouWantToDelete

    docmd.RunSQL strSQL

  5. #5
    Join Date
    Jun 2003
    Posts
    17

    Run Query from code

    Thanks

    The delete is working.

    The problem is that if I add a criteria to the Query (WHERE.....) is still not working.
    The message:
    No value given for one or more required patrameters.
    at line
    rs.Open str, CurrentProject.Connection,

    Thanks very much for your help
    Korado

  6. #6
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Run Query from code

    Originally posted by Korado
    Thanks

    The delete is working.

    The problem is that if I add a criteria to the Query (WHERE.....) is still not working.
    The message:
    No value given for one or more required patrameters.
    at line
    rs.Open str, CurrentProject.Connection,

    Thanks very much for your help
    Korado
    rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

  7. #7
    Join Date
    Jun 2003
    Posts
    17

    Run Query from code

    Actually to have the rs.DELETE working I must have the following:

    Choose.Open Str, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, 1

    Why is not working if I use the query name:

    Choose.Open "qryName", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, 1

    I get the message:
    INVALID SQL Statement;expected 'SELECT','DELETE',......


    Thanks
    Korado

  8. #8
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Run Query from code

    Originally posted by Korado
    Actually to have the rs.DELETE working I must have the following:

    Choose.Open Str, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, 1

    Why is not working if I use the query name:

    Choose.Open "qryName", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, 1

    I get the message:
    INVALID SQL Statement;expected 'SELECT','DELETE',......


    Thanks
    Korado
    Till now I always used strSQL for my recordset instead of the name of a query, so I don't know the answer right now.

    Sorry.

Posting Permissions

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