Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I solved it -- had to change datatypes in the target table, seems to be working now.

    This was working yesterday! but not today. i have an append query and using

    [codebox] Dim cmd As New ADODB.Command
    Dim rst2 As New ADODB.Recordset

    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "append_query"
    Set rst2 = cmd.Execute(, SelectedRow)[/codebox]

    It occasionally works but most of the time it errors out with

    Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    the append query in question is an append query to another table in the db, and it expects a parameter:

    INSERT INTO <mytable> a whole bunch of values WHERE ((([<other table>].FORM_DLN)=[Enter DLN]));

    The append query was re-edited to map to some different fields in the source data but otherwise not changed. if i run it manually it is fine.

    Is there a way to attach a parameter to RunSQL?

    TIA

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Parameters To Queries in VBA can be a pain.

    If Access can see them on a form using normal syntax they shoulld work with OpenQuery Method of DoCmd

    but the Parameters would need to be pointed to a form.

    An alternative might be to use DAO query Defs.

    Cannot give you exact solution but if you can provide them all at execute time, then something like this
    ought to work.

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDB.OpenQueryDef("Name Of Query Here")

    'Now we'll assign values to the query using the parameters option:

    qdf.Parameters(0) = Provide Parameter 0 here
    qdf.Parameters(1) = Provide Parameter 1 here etc (Allow for All Parameters)

    qdf.Execute

    Set qdf=Nothing

    Worth a go anyway.
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='799584' date='23-Oct-2009 20:09']Parameters To Queries in VBA can be a pain.

    If Access can see them on a form using normal syntax they shoulld work with OpenQuery Method of DoCmd

    but the Parameters would need to be pointed to a form.

    An alternative might be to use DAO query Defs.

    Cannot give you exact solution but if you can provide them all at execute time, then something like this
    ought to work.

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDB.OpenQueryDef("Name Of Query Here")

    'Now we'll assign values to the query using the parameters option:

    qdf.Parameters(0) = Provide Parameter 0 here
    qdf.Parameters(1) = Provide Parameter 1 here etc (Allow for All Parameters)

    qdf.Execute

    Set qdf=Nothing

    Worth a go anyway.[/quote]

    I've worked with querydef in running Stored Procs in SQL Server from Access VBA. The method I outlined above seems to be much the same, but uses ADO instead of DAO. Can't remember which is better!! Don't care right now! Anyhow, I have the thing working. Thanks for the input, and FWIW specifying params using querydef might be a little easier to work with. i assume to order of params is somehow set in the query, say reading left to right....unless it's an arabic query of course.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='steve_skelton13' post='799605' date='23-Oct-2009 23:37']i assume to order of params is somehow set in the query, say reading left to right....unless it's an arabic query of course.[/quote]
    To work with parameters in code, it's best to declare them explicitly.
    You can do this in the design view of a query, by selecting Query | Parameters..., or by specifying a PARAMETERS clause in the SQL string of the query, e.g.

    [sql]PARAMETERS [Which Country] Text (255), [Which Date] DateTime;
    SELECT ...[/sql]
    When you want to set the parameters using code, Parameters(0) corresponds to [Which Country] etc.

Posting Permissions

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