For added security I was requested to build an Access application using SQL Server with no linked tables.
I was unable to find an example anywhere so here is my solution.
(The code references a procedure 'SendError' This procedure sends an email to me via Outlook when a user encounters an error. This allows me to stay informed without reliance on users. Lines are numbered so I know exactly where the error occurred)
To accomplish this the following are required:
Open connection to SQL Server -
Create the SQL Statement for the Form's RecordSource
[/SIZE][/FONT]
Set the Form's Recordset = to the OpenRecordset return value on the Form's On_Open event
Set the Form's RecordSource = "" on the Form's On_Close event.
Loading Subforms works the same way.
Note:
If the Subform is a datasheet or Continuous Form using the same Recordset as the Parent form, set the Subform's Recordset = Parent.Recordset
DO NOT USE the Link Master/Child Fields. The two forms will auto sync.
To update the child Form records of a Parent form, use the Parent Form's On_Current event to update the Child Form's recordset.
I was unable to find an example anywhere so here is my solution.
(The code references a procedure 'SendError' This procedure sends an email to me via Outlook when a user encounters an error. This allows me to stay informed without reliance on users. Lines are numbered so I know exactly where the error occurred)
To accomplish this the following are required:
Open connection to SQL Server -
Code:
Public rs_cnn as ADODB.Connection
Public rs_temp as ADODB.Recordset
Code:
Public Sub OpenSQLConnection()
Dim strPWD As String
5 On Error GoTo ErrorHandler
10 If Not rs_cnn Is Nothing Then
15 Exit Sub
20 End If
25 strPWD = [My Password]
30 AppState.ConnectionString = _
"driver={SQL Server};server=[My Server Name];database=[My Database Name];uid=[SQL Server User];pwd=" & strPWD
35 Set rs_cnn = New ADODB.Connection
40 rs_cnn.ConnectionString = AppState.ConnectionString
45 rs_cnn.Open
50 Set rs_Temp = New ADODB.Recordset
55 On Error GoTo 0
60 Exit Sub
ErrorHandler:
Dim strErrMsg As String
65 strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
"In procedure: OpenSQLConnection of modADO" & vbCrLf & _
"Error Line: " & Erl
70 SendError strErrMsg
End Sub
Create the SQL Statement for the Form's RecordSource
Code:
[FONT=Arial][SIZE=2]Open ADODB Recordset -
[/SIZE][/FONT][FONT=Courier New][SIZE=2][CODE]Public Function OpenSQLRecordset(Optional ByVal SQLStatement As String) As ADODB.Recordset
5 On Error GoTo ErrorHandler
10 If Not rs_Temp Is Nothing Then
15 If rs_Temp.State = adStateOpen Then rs_Temp.Close
20 End If
25 If Not OpenSQLRecordset Is Nothing Then
30 If OpenSQLRecordset.State = adStateOpen Then OpenRecordset.Close
35 End If
40 OpenSQLConnection
45 Set rs_Temp = New ADODB.Recordset
50 With rs_Temp
55 Set .ActiveConnection = rs_cnn
60 .Source = SQLStatement
65 .LockType = adLockOptimistic
70 .CursorType = adOpenDynamic
75 .CursorLocation = adUseClient
80 .Open
85 End With
90 Set OpenSQLRecordset = rs_Temp
95 On Error GoTo 0
100 Exit Function
ErrorHandler:
105 If Not Err.Number = 3710 Then
Dim strErrMsg As String
110 strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
"In procedure: OpenSQLRecordset of modForms" & vbCrLf & _
"Error Line: " & Erl
115 SendError strErrMsg
120 End If
End Function
Set the Form's Recordset = to the OpenRecordset return value on the Form's On_Open event
Set the Form's RecordSource = "" on the Form's On_Close event.
Loading Subforms works the same way.
Note:
If the Subform is a datasheet or Continuous Form using the same Recordset as the Parent form, set the Subform's Recordset = Parent.Recordset
DO NOT USE the Link Master/Child Fields. The two forms will auto sync.
To update the child Form records of a Parent form, use the Parent Form's On_Current event to update the Child Form's recordset.
Last edited: