Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: DSN Linked Tables: How to Insert Into SQL Server

    Hi there,

    I have an Access 2007 front-end with a SQL Server 2005 back-end . I have a form to submit an audit plan to SQL Server. The front-end can retrieve data from the tables because they're linked with DSN.

    I am now trying to pass data from the form into SQL Server. Below is my code but it obviously doesn't work. Can anyone help me out please?

    Also, would it be better to use a stored procedure or is an "Insert Into" statement just fine?



    Code:
    Private Sub btnAddAudit_Click()
    'Validate all fields
    If IsNull(cboGroup.Value) Then
        Me.cboGroup.BackColor = vbRed
        Me.cboGroup.SetFocus
    ElseIf IsNull(lstAuditors.ListCount) Then
        Me.lstAuditors.BackColor = vbRed
        Me.lstAuditors.SetFocus
    ElseIf IsNull(lstAuditees.ListCount) Then
        Me.lstAuditees.BackColor = vbRed
        Me.lstAuditees.SetFocus
    ElseIf IsNull(txtYear.Value) Then
        Me.txtYear.BackColor = vbRed
        Me.txtYear.SetFocus
    ElseIf IsNull(cboQuarter.Value) Then
        Me.cboQuarter.BackColor = vbRed
        Me.cboQuarter.SetFocus
    MsgBox ("Audit plan contains missing data. Please verify all fields in red.")
    Else
      Dim dbs As DAO.Database
      Dim strSQL As String
      Dim Group, Quarter, Year As String
      
      Group = Me.cboGroup.Value
      Quarter = Me.cboQuarter.Value
      Year = Me.txtYear
      
      Set dbs = CurrentDb()
      DoCmd****nSQL "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('" & Group"','" & Quarter & "','" & Year & "');"
      dbs.Execute strSQL, dbFailOnError
      dbs.Close
    End If
    (BTW: In the code above, it is "DoCmd.R-u-nSQL...." It is not displaying correctly on the forums for some reason)
    Last edited by tkepongo; 07-15-11 at 18:01. Reason: error

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It depends on the data type of the columns where you try to write, but you enclose each value in single-quotes marks ('" & Group & "','" & Quarter & "','" & Year & "') This is only valid if the column is defined as a Text or Memo data type.

    A concatenation operator (& in green) is missing. Also, the quotes highlighted in blue are wrong (you inverted single and double quotes).

    Finally, a) do you have the necessary rights (as defined at the server level) to write data into the table and b) Is there an Identity column in the linked table? Without it the table is read-only in your Access database.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Sinndho View Post
    It depends on the data type of the columns where you try to write, but you enclose each value in single-quotes marks ('" & Group & "','" & Quarter & "','" & Year & "') This is only valid if the column is defined as a Text or Memo data type.

    A concatenation operator (& in green) is missing. Also, the quotes highlighted in blue are wrong (you inverted single and double quotes).

    Finally, a) do you have the necessary rights (as defined at the server level) to write data into the table and b) Is there an Identity column in the linked table? Without it the table is read-only in your Access database.
    Yes, I have the rights to write data into the table. Since the tables are linked through DSN, they appear in the Access front-end and I can open them to insert data.

    Yes, there is an identity column in the linked table.

    Can you or anyone give me an sample code to write an INSERT into SQL Server with Access front-end? I can bind data into my controls but I have no clue on how to write data from the form into the SQL Server backend. The tables are linked with DSN. Once I figure this out, it will make development much easier.

    Thank you for your time!!

  4. #4
    Join Date
    Jul 2011
    Posts
    63
    Oh here's a connection string (??) from one of the linked tables if it helps.


    ODBC;DRIVER=SQL Server;SERVER=JohnDoe\SQLEXPRESS;APP=2007 Microsoft Office system;DATABASE=cidb;Trusted_Connection=Yes;TABLE= dbo.tbl_Audit

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the controls are bound to the linked table, they behave as they would with a local table and any modification of a value is sent back to the table on the server. Access manages the data transfer from and to the server in the backgroud in this case. There can be a delay, though, before the data are updated. A Requery solves the problem but can slow down the application considerably.

    If you work with unbound forms, the standard SQL statements work:
    Code:
    INSERT INTO <TableName> ( <Field1>, <Field2>, ... ) VALUES ( <Value1>, <Value2> ... )
    for a new row and:
    Code:
    UPDATE <TableName> SET <Field1> = <Value1>, <Field2> = <Value2>, ... WHERE <Id> = <Id Value>
    for changing the values in an existing row. Don't forget the WHERE... otherwise you'll update all rows into the table.
    Have a nice day!

  6. #6
    Join Date
    Jul 2011
    Posts
    63
    When I tried using standard SQL statement I get a syntax error. Below is the standard code.
    Code:
    INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear, AuditStatus) Values ('Call Center', '1','2114', 'Scheduled')
    However, this code below works.
    Code:
    Dim strSQL As String
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear, AuditStatus) Values ('Call Center', '1','2114', 'Scheduled');"
    CurrentDb.Execute strSQL
    Are there drawbacks using the code above for a linked SQL Server backend?

    Thank you!

  7. #7
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by tkepongo View Post
    When I tried using standard SQL statement I get a syntax error. Below is the standard code.
    Code:
    INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear, AuditStatus) Values ('Call Center', '1','2114', 'Scheduled')
    However, this code below works.
    Code:
    Dim strSQL As String
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear, AuditStatus) Values ('Call Center', '1','2114', 'Scheduled');"
    CurrentDb.Execute strSQL
    Are there drawbacks using the code above for a linked SQL Server backend?

    Thank you!
    I found out that it doesn't add a record when it violates foreign key constraints, which is what I intended it to do. However, Access will not notify if the transaction failed. How would I create a messagebox or something that will notify the user that the SQL transaction failed?

    I apologize for my ignorance. I'm an intern and had to start using Access like two weeks ago.
    Last edited by tkepongo; 07-15-11 at 19:57.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the difference between the 2 SQL statements? I don't see any. However, the VBA interpretor of Access cannot directly execute a SQL statement; only a Data Access Component (DAO or ADO), such as the CurrentDB shortcut, can do it.

    Also, when you use CurrentDb.Execute <SQL Statement>, you better add the parameter DbFailOnError:
    Code:
    CurrentDb.Execute strSQL, DbFailOnError
    Otherwise, no error will be returned if, for any reason, the statement fails to execute.

    Did you check the use of quotes, i.e. are ScheduledQuarter and ScheduledYear defined as Text data type? If not, drop the quotes with the corresponding values.
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Sinndho View Post
    Did you check the use of quotes, i.e. are ScheduledQuarter and ScheduledYear defined as Text data type? If not, drop the quotes with the corresponding values.

    They were not Text data types and I removed the extra quotes. I added code to display a message box if an error was to occur. When the INSERT fails, the message box will display the error. However, when the INSERT succeeds, I get the message box displaying "Audit Plan Added" and then a message box displaying "Error: ". I couldn't find an error by stepping through the code. Why is that message box appearing?

    Code:
    On Error GoTo Err_test
    Dim strSQL As String
    strSQL = "INSERT INTO dbo_tbl_Audit (Group_Name, ScheduledQuarter, ScheduledYear) Values ('Call Center', 4, 2122);"
    CurrentDb.Execute strSQL, dbFailOnError
    MsgBox ("Audit Plan Added")
    
    Err_test:
        MsgBox "Error: " & Err.Description

  10. #10
    Join Date
    Jul 2011
    Posts
    63
    Nevermind, problem solved.

Posting Permissions

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