Form RecordSource non-linked tables

[Bergman]

Due to security requirements of my application, linked tables are not allowed.

I can create a pass through query to SQL Server and retrieve results.
I can set a forms record source to an ADODB connected query IF related table is linked. The same query does not work if the linked table is removed.

• Form is unbound

Private Sub Form_Load()
'
Dim strSQL As String
10 On Error GoTo ErrorHandler

20 CurrentISO.ISOID = 2
30 SetConnection

40 strSQL = "SELECT * FROM tblISOComponent WHERE ISO_ID = " & CurrentISO.ISOID
50 RecordSource_rs.Open strSQL, RecordSource_Cnn, adOpenDynamic
60 Me.RecordSource = RecordSource_rs.Source

70 On Error GoTo 0
80 Exit Sub

ErrorHandler:
Dim strErrMsg As String
90 strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
"In procedure: Form_Load of Form_frmComponents" & vbCrLf & _
"Error Line: " & Erl

100 SendError strErrMsg

End Sub



Public RecordSource_Cnn As ADODB.Connection
Public RecordSource_rs As ADODB.Recordset

Public Function SetConnection()
Dim strPWD As String
strPWD = "MyPassword"
AppState.ConnectionString = "driver={SQL Server};server=SomeUser-PC\SQLEXPRESS;uid=db-RetroPMI_rw;pwd=" & strPWD & ";database=RFMSApplicationServer"
Set RecordSource_Cnn = New ADODB.Connection
RecordSource_Cnn.ConnectionString = AppState.ConnectionString
RecordSource_Cnn.Open

Set RecordSource_rs = New ADODB.Recordset

End Function



This is the error message:

[FONT=&quot]Error 2580 (The record source 'SELECT * FROM tblISOComponent WHERE ISO_ID = 2' specified on this form or report does not exist. ) [/FONT]

 

[jdraw]

Due to security requirements of my application, linked tables are not allowed.

If your security is that stringent, then you probably are not allowed to use Access.

 

[Bergman]

If your security is that stringent, then you probably are not allowed to use Access.

I agree. Except that they want to use an existing app I wrote 2 years ago. They've decided they want to use it long term and have set new parameters (encryption, no linked tables etc.) The application is already using ADS security.

I'm not finding anything in my online searches and may have come to a dead end... just taking one last stab at it before presenting the bad news.

 

[Bergman]

Discoveries:
Created a local bogus table (one field, no records) with the same name as the SQL Server table. Opened the ADODB recordset as OpenKeyset and set the form's recordset to the ADODB recordset. I get the records to display. Unfortunately this is static data...

 

[Bergman]

Found the issue:
I can use OpenDynamic if I set the cursor location to client

Private Sub Form_Load()

Dim strSQL As String
10 On Error GoTo ErrorHandler

20 CurrentISO.ISOID = 2
30 SetConnection

40 strSQL = "SELECT * FROM tblISOComponent WHERE ISO_ID = " & CurrentISO.ISOID

50 With RecordSource_rs
60 Set .ActiveConnection = RecordSource_Cnn
70 .Source = strSQL
80 .CursorLocation = adUseServer
90 .LockType = adLockOptimistic
100 .CursorType = adOpenDynamic
110 .CursorLocation = adUseClient
120 .Open
130 End With
140 Set Me.Recordset = RecordSource_rs

150 On Error GoTo 0
160 Exit Sub

ErrorHandler:
Dim strErrMsg As String
170 strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
"In procedure: Form_Load of Form_frmComponents" & vbCrLf & _
"Error Line: " & Erl

180 SendError strErrMsg


End Sub

I will just need a data entry form form new records.