Insert data to SQL server from MS Access Form RRS feed

  • Question

  • Hi i need to insert data in to SQL server from MS acccess form. Here Access has been used only for fill the form not for storage. Please suggest me. i am using MS Access 2010 and SQl server 2008.


    Tuesday, March 13, 2012 11:07 AM


All replies

  • Rather than re-invent the wheel can you provide the code you are presently using?  There are lots of methods that can be used, and I think it is better to start with what presently is working.


    Tuesday, March 13, 2012 11:27 AM
  • Hi thanks for your reply, right now i have not using any code. i am new to MS Access, so i dont know how to start, If you provide any useful steps or link would be really appreciated.


    Tuesday, March 13, 2012 11:33 AM
  • Start simple.  First make a connection to the database using code like on the attached link.


    The code below I use to upload data from an excel worksheet, you will need to replace the excel worksheet data with your form data.  The process I uses is to open a recordset and move to the last row of the database.  Then insert the new data into the recordset and perform an update to store the data in the database.

    Sub GetRecordset()
    'filename of database is with MakeDatabase macro
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    strDB = Folder & "\" & FName
    ClientName = "Test"
    If Dir(strDB) = "" Then
       MsgBox ("Database Doesn't Exists, Create Database" & strDB)
       MsgBox ("Exiting Macro")
       Exit Sub
    End If
    ConnectStr = _
       "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & Folder & "\" & FName & ";" & _
       "Mode=Share Deny None;"
    cn.Open (ConnectStr)
    strSQL = "SELECT Submissions" & vbCrLf & _
             "FROM `\\oshrgv23\shared\IMP_Team\Database\NBNF3_Implementation_Tracking_DB`.q_Transition" & vbCrLf & _
             "WHERE (q_Transition.ID=" & NBNFID & ")"
    strSQL = "Select * from Submissions" & _
       " where 'Submission.client Name'='Test'"
    With rs
          .Open Source:=strSQL, _
             ActiveConnection:=cn, _
             CursorType:=adOpenDynamic, _
             LockType:=adLockOptimistic, _
          If .EOF <> True Then
          End If
    End With
    With Sheets("Internal Project Plan")
       ClientName = .Range("B4")
       ImpMgr = .Range("B5")
       LaunchDate = .Range("C4")
       LastRow = .Range("K" & Rows.Count).End(xlUp).Row
       For RowCount = 7 To LastRow
          If UCase(.Range("K" & RowCount)) = "X" Then
             DueDate = .Range("E" & RowCount)
             ActualDate = .Range("F" & RowCount)
             DateDif = .Range("M" & RowCount)
             Accurate = .Range("L" & RowCount)
             Task_ID = .Range("B" & RowCount)
             With rs
                !Task_ID = Task_ID
                ![Client Name] = ClientName
                ![Effective Date] = LaunchDate
                ![Imp Mgr] = ImpMgr
                ![Due Date] = DueDate
                ![Actual Date] = ActualDate
                ![Date Difference] = DateDif
              End With
          End If
       Next RowCount
    End With
    Set appAccess = Nothing
    End Sub


    Tuesday, March 13, 2012 11:49 AM
  • Hi i got one clue... For my recuirement i need to move forward to MS Access Project not Access database. So can you provide detail about creating Access Project.


    Tuesday, March 13, 2012 11:50 AM
  • Other than code, Is there any way to achieve this, i heard the term about OLEDB connection ... is it enough?
    Tuesday, March 13, 2012 11:53 AM
  • The first step is to make a connection.  Depending on the backend and front end applications the connection method is going to be different.  In your case the font end is Access and the backend is a SQL Server.  ADO and OLEDB are two different connection methods.  That is why I refered you to the webpage.

    No matter what you do you are going to need to write some instructions either SQL statements or macro statement. The code isn't very complicated.  There are only three steps

    1) Make a connection

    2) Move to the last record

    3) Write the data and perform an update

    4) Close the connection


    Tuesday, March 13, 2012 12:03 PM
  • Yeah, the code will be easy, but my client alreadt developed this using macro builder..

    can u have any samples to insert data to sql using macro statement.


    Tuesday, March 13, 2012 12:28 PM
  • Why not simply create an ODBC linked table and set that as the recordsource for your form?

    There is plenty of info available for this.

    Here is the first that came up with a Google search


    • Proposed as answer by Andrey Artemyev Tuesday, March 13, 2012 5:27 PM
    • Marked as answer by Bruce Song Wednesday, April 4, 2012 6:26 AM
    Tuesday, March 13, 2012 12:48 PM
  • Follow Alphonse's suggestion. Just link all the tables you need and create bound forms with Wizards. You'll need no code at the first step. The further steps depend on how deep you're going to dive. 

    ADO and OLEDB are two different connection methods.

    BTW, Joel, http://en.wikipedia.org/wiki/OLE_DB and http://en.wikipedia.org/wiki/ActiveX_Data_Objects

    A bit closer: http://msdn.microsoft.com/en-us/library/windows/desktop/ms722784(v=vs.85).aspx and http://msdn.microsoft.com/en-us/library/windows/desktop/ms675532(v=vs.85).aspx 

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 6:26 AM
    Tuesday, March 13, 2012 5:36 PM