Insert data to SQL server from MS Access Form

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.

 Thanks

Answer 1

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.

---

jdweng

Answer 2

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.

Thanks

Answer 3

Start simple.  First make a connection to the database using code like on the attached link.

http://msdn.microsoft.com/en-us/library/ms130978.aspx

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, _
         Options:=adCmdText
      If .EOF <> True Then
         .MoveLast
      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
            .AddNew
            !Task_ID = Task_ID
            ![Client Name] = ClientName
            ![Effective Date] = LaunchDate
            ![Imp Mgr] = ImpMgr
            ![Due Date] = DueDate
            ![Actual Date] = ActualDate
            ![Date Difference] = DateDif
      
            .Update
          End With
      End If
   Next RowCount
End With
Set appAccess = Nothing
End Sub

---

jdweng

Answer 4

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.

Thanks

Answer 5

Other than code, Is there any way to achieve this, i heard the term about OLEDB connection ... is it enough?

Answer 6

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

---

jdweng

Answer 7

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.

Thanks

Answer 8

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

http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database/

Answer 9

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