Connect To A Database & Submit (From VBA Form)

This code allows connection to a database on a server. The database is an access database and in this case is password protected. You need to select Tools > References and add in Microsoft ActiveX Data Objects 2.7 Library (Or Similar)

The following example also includes the code to submit to a database

Dim cn As ADODB.Connection, rs As ADODB.Recordset ' connect to the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\\C900000FSL0001\Hxd$\HCA\10. MPT Scotland\Shared\Abs\Data\A_DB.mdb;" & _ "Jet OLEDB:Database Password=thrufelt;" 'Open Complaints Tracker Table and add entry Set rs = New ADODB.Recordset rs.Open "tblAMR_Tracker", cn, adOpenKeyset, adLockOptimistic, adCmdTable With rs           .AddNew ' create a new record ' add values to each field in the record .Fields("Date Logged") = Now .Fields("Personnel Number") = txtPnum.Value .Fields("Status") = "Open" .Fields("AMR Stage") = cbAMR.Value .Fields("Date Held") = dtHeld.Value .Fields("Review Date") = dtReview.Value .Fields("Date AMR Expires") = dtExpire.Value .Fields("HR Case No") = txtHRCaseNo.Value .Fields("WMD Referral") = cbWMDReferral.Value .Fields("Medical Consent Form Signed") = cbForm_Signed.Value .Fields("Date Medical Requested") = dtRequested.Value ' save the new record .Update 'Gets the Master case ID for this case CaseID = rs![ID] 'Add the AMR Code to the CaseID CaseID = "AMR" & CaseID 'confirm to user that task complete & provide a Case ID       response = MsgBox("New AMR Added. Your Case ID is :" & CaseID) 'closing current table rs.Close Set rs = Nothing 'closing DB connection cn.Close Set cn = Nothing End With

Code Snippits