Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2002
    Posts
    23

    Unanswered: Updating SQL server form Access form?

    I have an access form. Is there a way I can add a button to the form so when pressed it runs a sql insert staement that would then go into a SQL Server table? I would be connecting to the SQL server through an IP address similiar to how I would using an .asp page.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    yes. it works just like on an asp page.

    Create a ADODB.Connection object.
    Open the Connection using your SQL Server Connection String.
    Execute the command.
    Close and Release the connection.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Jun 2002
    Posts
    23
    Where does that code go in access? Is there an example I can look at?
    Last edited by Bspahr75; 09-28-06 at 13:04.

  4. #4
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    If you add the button to your form and double click it, you will be in the code for the button. By default it adds the handler for the button's click event. That's where the code would go.

    If you're going to have different things happening, then I would suggest putting the code that opens and closes the connection into other functions so that you can call it, and don't have to re code it each time, but that's up to you.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you add the button to your form and double click it, you will be in the code for the button. By default it adds the handler for the button's click event
    What version of Access allows you to set a form's command button's OnClick property in this way?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jun 2002
    Posts
    23
    Here is what I pasted into the the Visual Basic Editor after going to the onclick event procedure.

    -------------------------------------------------
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=SQLOLEDB;Data Source=111.111.11.1;" _
    & "Initial Catalog=mydb;User Id=myid;Password=mypassword;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"

    sqlData = "Insert Into TEST (fname, lname) Values (" & fname & "," & lname & ")"

    Set rsData = Conn.Execute(sqlData)
    --------------------------------------------------------


    It is still not working though.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? ...Values ('" & fname & "', '" & lname & "')"
    maybe your next post will mention the error msg you get if it still wont work.

    izy


    LATER: i know nothing at all about ADO, but from my DAO perspective that Set rsData = line is a rather strange thing to do with an action query
    Last edited by izyrider; 10-02-06 at 12:48.
    currently using SS 2008R2

  8. #8
    Join Date
    Jun 2002
    Posts
    23
    Yes, the error said "object required". I know this isn't right - this is similiar to how it would work from an asp page. That is where I am lost.

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Unlike an ASP page you will want to declare your variables. I think all you need is a:

    Dim Conn as New ADODB.Connection


    Then, remove your Set commands for the Connection and the Recordset

  10. #10
    Join Date
    Jun 2002
    Posts
    23
    Ok, I have this kind of working now. I just linked the SQL server table in Access. Then I created an append query with the fields I want to get added to the Sql table. Then I created a form and added a command button using the command button wizard - I set the action to run query. Then selected the append query that I created earlier from the menu. This now will update the Sql table when ran. The only problem is that it copies every row that is already in the database and duplicates it. How can I get it do just update the last row that was entered?

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a 'WHERE" clause.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Bspahr75
    Ok, I have this kind of working now. I just linked the SQL server table in Access. Then I created an append query with the fields I want to get added to the Sql table. Then I created a form and added a command button using the command button wizard - I set the action to run query. Then selected the append query that I created earlier from the menu. This now will update the Sql table when ran. The only problem is that it copies every row that is already in the database and duplicates it. How can I get it do just update the last row that was entered?
    Woah woah woah - backup fella

    Linked table and SQL Appends sounds like worst of both worlds IMHO.
    You created an INSERT query but you want to UPDATE the last row? Inserts and updates are not the same thing - which do you want to do?

    SQL Tutorial - well worth some study:
    http://www.w3schools.com/sql/default.asp

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2002
    Posts
    23
    I guess I am not explaining this very well. I know the difference between an INSERT and UPDATE Query. I want to INSERT into a new row in the SQL table whenever the form button is pressed. I want this to work exactly like a form on an asp page. Basically when you hit submit - the data gets get INSERTED into a new row in the databsae table. I don't know exactly how to make this happen in access.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You were 90% there with this:
    Code:
    sqlData = "Insert Into TEST (fname, lname) 
    Values (" & fname & "," & lname & ")"
    Did you notice Izy's correction? More easy to spot when wrapped in code tags:
    Code:
    Values ('" & fname & "', '" & lname & "')"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    n/m, needs more "me reading the whole thread before posting".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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