Tip No Table Link Database (1 Viewer)

Bergman

New member
Local time
Today, 02:35
Joined
Nov 11, 2014
Messages
5
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 -
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
[/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.


 
Last edited:

Users who are viewing this thread

Top Bottom