Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    41

    Unanswered: Updateable queries

    Does anyone have a quick list of exactly what Access 2000 is expecting in a query for it to qualify as Updateable? On certain occasions, I can't get an updateable query to save my life. The latest:

    SELECT TblBrianPkg.[Pack ID], TblBrianPkg.[Pack Number], TblBrianPkg.Description, TblBrianPkg.[Item Number], TblBrianPkg.Carton, TblBrianPkg.Filler, TblBrianPkg.ProdClass, TblBrianPkg.Loc, TblBrianPkg.PkgNotes, TblBrianPkg.L, TblBrianPkg.W, TblBrianPkg.H, TblBrianPkg.Wt, TblBrianPkg.Packaging, TblBrianPkg.DatePkg, qryJewelry_Anklet.[Pack ID]
    FROM TblBrianPkg LEFT JOIN qryJewelry_Anklet ON TblBrianPkg.[Pack ID] = qryJewelry_Anklet.[Pack ID]
    WHERE (((qryJewelry_Anklet.[Pack ID]) Is Not Null));

    [Pack ID] is the PK for TblBrianPkg

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Generally speaking, an updateable query is one based on a single table AND has the primary key as 1 of it's columns (in order to identify EXACTLY which row to update) ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not exactly sure of all the situations, but I do know that if you are joining two tables and you want to perform an action (Delete, Update) then at least one side of the join has to be a Unique Index. There are times when I will create a table (Make Table query), add a unique index and then perform the action query. This is typically what happens if I am grouping on a field.

  4. #4
    Join Date
    Mar 2004
    Posts
    41
    I understand. I've been trying to use a join based on a table and a query rather than two tables as in the past. As soon as I went to two tables, the query was updateable. Thank you!

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Open the query maker in design view. Select "Update Query" from the Query menu. Do a sample query then View -> SQL View. Take your sample and hopefully you can use that to make what you are doing above

    Good Luck
    Ryan
    My Blog

Posting Permissions

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