Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    81

    Unanswered: SQL Backend Access adp frontend - Stupid QUERY!!!!

    Ok, little frustrated hence the excessive exclamation points.

    I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!

    I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!

    And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?

    Is there something simple i'm missing or is it my table structure????

    Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    BOL Says

    SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view.

    You cannot use data modification statements on more than one underlying table in a single statement.

    Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.

    Does this help ?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Oct 2003
    Posts
    81
    oh man am I over my head here! Anyone want to translate that in English please?

    Ok, I think I understand what your saying... essentially SQL bites and it won't let me do what I want to do? That about right?

    OK, well then, next question...

    I have a form. A data entry form. Essentially this data that needs to be entered is to be stored in several (lets say five) tables. What is the best way of getting this data from the user (through the form) into my database and complying with sql's criteria?

    I use to be able to do that through a query, but obviously sql handles things just a wee bit different!

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I think what it is saying is some views are updateable some are not depending on a number of rules

    Anyway your best way of getting data into yer DB is to pass your update into a stored Procedure as variables which can then

    Do any Business Logic checking
    and
    Put the five updates inside a single transaction to make sure that either all or none of the updates occur.

    Which would generally mean not running Access on the Client Front end which I would Highly recommend that you don't - but you may be commited to this.

    And if you are why not use multuple insert statements which is generally not best practice but you have little choice?

    and yes Toto
    I use to be able to do that through a query, but obviously sql handles things just a wee bit different!
    I don't think we're in Kansas anymore

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have two options.
    1) Define an "intead of" trigger on your SQL Server view that will handle distributing updates and inserts to the underlying tables.
    2) MS Access Data Project forms have a property (updateable table, or something like that) within which you can define which of the underlying tables to update when the forms datasource is an sql statement.

    It sounds like option #1 is your best bet.

    MS Access Data Projects is a fine development platform for small applications. You will run into a problem if the datasources for your forms returns too many records, but other than that you should be OK.
    Last edited by blindman; 09-27-05 at 00:18.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2003
    Posts
    81
    *Stares blankly*

    Ok, firstly, what's an "instead of" trigger?

    Secondly, lets assume I create a stored procedure. It may look something like this:

    CREATE PROCEDURE [insert_tblJobDetails]
    (@JobID [int],
    @Lot [smallint],
    @Street [varchar](50),
    @Suburb [varchar](50),
    @MapBook [char](3),
    @MapNumber [smallint],
    @MapReference [char](2),
    @Price [money],
    @OrderNumber [int],
    @OrderDate [smalldatetime],
    @DesignComplete [bit],
    @DesignCompleteBy [varchar](50),
    @DesignCompleteDate [smalldatetime],
    @DesignChecked [bit],
    @DesignCheckedBy [varchar](50),
    @DesignCheckedDate [smalldatetime],
    @ManufactureDate [smalldatetime],
    @CompleteInYard [bit],
    @ProposedDeliveryDate [smalldatetime],
    @ActualDeliveryDate [smalldatetime],
    @DeliveredOnDD [bit],
    @ConstructionStage [smallint],
    @BuilderID [int],
    @QuoteID [int])

    AS INSERT INTO [TrussSQL].[dbo].[tblJobDetails]
    ( [JobID],
    [Lot],
    [Street],
    [Suburb],
    [MapBook],
    [MapNumber],
    [MapReference],
    [Price],
    [OrderNumber],
    [OrderDate],
    [DesignComplete],
    [DesignCompleteBy],
    [DesignCompleteDate],
    [DesignChecked],
    [DesignCheckedBy],
    [DesignCheckedDate],
    [ManufactureDate],
    [CompleteInYard],
    [ProposedDeliveryDate],
    [ActualDeliveryDate],
    [DeliveredOnDD],
    [ConstructionStage],
    [BuilderID],
    [QuoteID])

    VALUES
    ( @JobID,
    @Lot,
    @Street,
    @Suburb,
    @MapBook,
    @MapNumber,
    @MapReference,
    @Price,
    @OrderNumber,
    @OrderDate,
    @DesignComplete,
    @DesignCompleteBy,
    @DesignCompleteDate,
    @DesignChecked,
    @DesignCheckedBy,
    @DesignCheckedDate,
    @ManufactureDate,
    @CompleteInYard,
    @ProposedDeliveryDate,
    @ActualDeliveryDate,
    @DeliveredOnDD,
    @ConstructionStage,
    @BuilderID,
    @QuoteID)
    GO

    So how do I get my new unbound form to use this stored procedure and insert the values where there supposed to go. And also, I'm also going to have two other stored procedures for the other two tables which I also want to run at the same time.

    My thinking is to have the stored procedures run when I press a button. But how do i get the button to run the stored procedure (whats the syntax) and how do I get the values on my form into those parameters?

    Also, thanks very much to both of you for helping so far!

  7. #7
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    lol

    "Stares Blankley" is something I usually hear from non - IT people. Some IT People are rarely afforded the luxury of this statement ;-)

    Secondly it sounds as though you are at the embryonic stages of your project would I be right in assuming your question is :-

    What is the best way to provide Access Form Data to a SQL Server Backend

    Any mistakes you make at this stage will turn around and bite you on the Ass l8r In terms of "I'm only using access on the client to provide a GUI".

    Thirdly an Instead of Trigger is a Trigger(TSQL Code) that is fired insted of updating the underlying table.

    Use BOL & DYOR

    Fourthly I do like your SP !!! - It is clean & easy to read and looks like a fine template for the Job at hand (any comments on the fieldnaming convention blindman would be greatly appreciated)

    Could now be padded out to add any Business Logic ie:-

    @Price > 0

    And extended with multiple Update statements wrapped in a BEGIN TRANSACTION

    Fithly Unfortunately I don't often work with Access forms so .......... my only thought of passing data to a SP from access would be fire it as a raw SQLStmnt.

    Someone please elaborate.

    Sixthly Your Welcome, it's nice to know if we're helping or not

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by GWilliy
    lol
    Fithly Unfortunately I don't often work with Access forms so .......... my only thought of passing data to a SP from access would be fire it as a raw SQLStmnt.
    That's a negative. A sp sits on and is executed on the server regardless of where it is called from.
    Last edited by Thrasymachus; 09-27-05 at 08:54.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For simplicity sake, I would still go for the Server-side trigger on the view. Do this, and you can treat the view just as if it were a flat table. Access won't know the difference.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    That's a negative. A sp sits on and is executed on the server regardless of where it is called from.
    Have we got crossed wires Thrasy?

    Of course the SP is on the SQL Server - can you call it with parameters from access is what I'm trying to say, as in

    sending the EXECute command to SQL from Access along with it's variables in a string, something like

    SqlString = "EXECUTE = SQLSERVER1.pubs.dbo.insert_tblJobDetails @Lot = " + Accsfrm.txtLot + ", @Street = " + "Accsfrm.txtStreet,..............."

    (Unfortunately I believe the sp may be recompiled each time it is called this way)

    or even

    Using a paramaterized query on the connection pipe/object

    blindman I wonder what err would be passed back to Access if the Before_Update failed to insert into one of the five tables, possibly this operation is automaticaly in a transcation.
    I would fear for potential future complexities if updates to core data in SQL from access were done with your proposed method in this CASE and possibly in many others.

    IMHO

    GW
    Last edited by GWilliy; 09-27-05 at 11:49.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by GWilliy
    (Unfortunately I believe the sp may be recompiled each time it is called this way)
    Why is that? Is this just speculation? An sp can sit in the procedure cache with several possible execution plans. I am not totally sure when it makes the decision to create a new one but I am guessing it does when it can't find an ideal one or one that is good enough but I highly doubt it recompilation happens due to the method you choose to call an sp.

    Got references?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    There are situations where a stored procedure will be recompiled on every call because of different codeblocks being executed, temp table usage in IF statements etc.

    OK in this case I may be wrong about the calling method causing re-compilation, not sure what made me think that.

    Strange

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe there is a page in Kalen's book (Inside SQL 2000) that says that some queries will not be recompiled, if SQL Server concludes the plan is just too simple.

    If all existing plans are in use, a new one will be compiled.

    Connection options like ARITHABORT or QUOTED_IDENTIFIERS can also affect whether an existing plan is usable by the current connection.

    Use of temp tables in a stored procedure usually causes a recompile. But that may be avoidable, if the temp tables are defined up front in the stored procedure, before anything is selected/updated/inserted/deleted.

    EDIT: Sniped!

  14. #14
    Join Date
    Oct 2003
    Posts
    81
    Ok, heres what I have done so far:

    I have the following code bound to a button which is pressed after the user has entered the relevant details on the form. Thus submitting the record and inserting it into the database via a stored procedure (the same one I posted earlier).

    Code:
    '====== ACTIVATE THE UPDATE JOB DETAILS STORED PROCEDURE
    
    ' This function uses the connection to the SQL database "BlahSQL" to run the "insert_tblJobDetails" Stored
    ' Procedure. Quite cool actually! :)
    
    Public Function Update_JobDetails()
    
        Dim Lot As Integer, Street As String, _
        Suburb As String, MapBook As String, MapNumber As Integer, _
        MapReference As String, Price As Currency, OrderNumber As Integer, _
        OrderDate As Date, DesignComplete As Boolean, DesignCompleteBy As String, _
        DesignCompleteDate As Date, DesignChecked As Boolean, DesignCheckedBy As String, _
        DesignCheckedDate As Date, ManufactureDate As Date, FixDate As Date, _
        CompleteInYard As Boolean, ProposedDeliveryDate As Date, ActualDeliveryDate As Date, _
        DeliveredOnDD As Boolean, ConstructionStage As Integer, BuilderID As Integer
    
        Dim Conn1 As ADODB.Connection
        Dim Cmd1 As ADODB.Command
        Dim strConn As String
        
        Set Cmd1 = New ADODB.Command
        
        ' Establish connection.
        Set Conn1 = New ADODB.Connection
        strConn = "Driver={SQL Server};" & _
                  "Server=Blah;" & _
                  "Database=BlahSQL;" & _
                  "UID=Blah;PWD=password"
        Conn1.Open ConnectionString:=strConn
        
        ' Get the values from the form
        Lot = Me.txtLot
        Street = Me.cboStreet
        Suburb = Me.cboSuburb
        MapBook = Me.txtMapBook
        MapNumber = Me.txtMapNumber
        MapReference = Me.txtMapReference
        Price = Me.txtPrice
        OrderNumber = Me.txtOrderNumber
        OrderDate = Me.txtOrderDate
        DesignComplete = Me.chkDesignComplete
        DesignCompleteBy = Me.cboDesignCompleteBy
        DesignCompleteDate = Me.txtDesignCompleteDate
        DesignChecked = Me.chkDesignChecked
        DesignCheckedBy = Me.cboDesignCheckedBy
        DesignCheckedDate = Me.txtDesignCheckedDate
        ManufactureDate = Me.txtManufactureDate
        FixDate = Me.txtFixDate
        CompleteInYard = Me.chkCompleteInYard
        ProposedDeliveryDate = Me.txtProposedDeliveryDate
        ActualDeliveryDate = Me.txtActualDeliveryDate
        DeliveredOnDD = Me.chkDeliveredOnDD
        ConstructionStage = Me.cboConstructionStage
        BuilderID = Me.cboBuilderID
        
        ' Open command object
        Set Cmd1 = New ADODB.Command
        Cmd1.ActiveConnection = Conn1
        Cmd1.CommandText = "insert_tblJobDetails"
        Cmd1.CommandType = adCmdStoredProc
                
        Cmd1.Parameters("@Lot") = Lot
        Cmd1.Parameters("@Street") = Street
        Cmd1.Parameters("@Suburb") = Suburb
        Cmd1.Parameters("@MapBook") = MapBook
        Cmd1.Parameters("@MapNumber") = MapNumber
        Cmd1.Parameters("@MapReference") = MapReference
        Cmd1.Parameters("@Price") = Price
        Cmd1.Parameters("@OrderNumber") = OrderNumber
        Cmd1.Parameters("@OrderDate") = OrderDate
        Cmd1.Parameters("@DesignComplete") = DesignComplete
        Cmd1.Parameters("@DesignCompleteBy") = DesignCompleteBy
        Cmd1.Parameters("@DesignCompleteDate") = DesignCompleteDate
        Cmd1.Parameters("@DesignChecked") = DesignChecked
        Cmd1.Parameters("@DesignCheckedBy") = DesignCheckedBy
        Cmd1.Parameters("@DesignCheckedDate") = DesignCheckedDate
        Cmd1.Parameters("@ManufactureDate") = ManufactureDate
        Cmd1.Parameters("@FixDate") = FixDate
        Cmd1.Parameters("@CompleteInYard") = CompleteInYard
        Cmd1.Parameters("@ProposedDeliveryDate") = ProposedDeliveryDate
        Cmd1.Parameters("@ActualDeliveryDate") = ActualDeliveryDate
        Cmd1.Parameters("@DeliveredOnDD") = DeliveredOnDD
        Cmd1.Parameters("@ConstructionStage") = ConstructionStage
        Cmd1.Parameters("@BuilderID") = BuilderID
        Cmd1.Execute
        
        ' Display message box signalling successful entry of job
        MsgBox "Job submitted successfully.", vbInformation + vbOKOnly, "Submit Job"
        
        ' Disable the button to prevent duplicate job entries
        Me.txtJobID.SetFocus ' needed due to inability to disable a currently selected control
        Me.cmdSaveChanges.Enabled = False
        
        ' Terminate the connection
        Conn1.Close
        Set Conn1 = Nothing
    Now that works nicely for inserting new records. However, I would also like to add the functionality to edit records that are already in the database. Now with the form unbound, how could I go about showing the records, then when the user tries to edit something, switch to another stored procedure to make the update?

    Cheers
    Last edited by 3.14; 09-28-05 at 19:59.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by GWilliy
    blindman I wonder what err would be passed back to Access if the Before_Update failed to insert into one of the five tables, possibly this operation is automaticaly in a transcation.
    I would fear for potential future complexities if updates to core data in SQL from access were done with your proposed method in this CASE and possibly in many others.
    Before_Update? Who said anything about Before_update? GWilliy, your posts seem to be getting more and more heavily infected with non-sequiturs. Get some rest.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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