assign recordset as form record source (1 Viewer)

[ahmedjamalaboelez]

Hi,
I'm Using following code to connect to sql server , how could assign my recordset as ( Continious form Record Source ),
connection is successful But I need help in recordset!

Private Sub Command0_Click()
    Dim cnn    As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM USERS"
    Set cnn = New ADODB.Connection
    cnn.Provider = "SQLOLEDB.1"               
    cnn.ConnectionString = "driver={SQL Server};" & _
                           "server=192.168.1.156;uid=sa;pwd=123654;database=testdb"
    Set rsData = New ADODB.Recordset
    cnn.Open
    If cnn.State = adStateOpen Then
        MsgBox "connected server"
        DoCmd.OpenForm "test1"                            ' here is the error message i  need help to assign this recordset (rsData as form record source)
        Set Forms![test1].Recordset = rsData
        cnn.Close
        rsData.Close
    Else
        MsgBox "Not connected server"
        Exit Sub
    End If
End Sub

 

[CJ_London]

Please use the code tags to maintain indentation - highlight the code and click the '</>' button. Otherwise your code is difficult to interpret. I've done it for you on this occasion.

Set Forms![test1].Recordset = rsData is perfectly valid as code goes but so far as I can see you have not assigned a recordset to it. You've defined the sql but then not applied it

As far as the error message is concerned - what is it the message?

It's a while since I've used ADO with access but pretty sure if you close your connection, the form recordset will be closed as well. If this is the case you will need to use an ADO disconnected recordset. But this generates further problems if your form allows edits as when you come to update sql server you will need to check if someone else has modified the data in the meantime.

And finally - I presume you are aware that access forms find/filter/sort functionality is based on DAO and not ADO. Sometimes there is a 'match' but If you need this functionality you will need to write your own routines.