Driving me bonkers...
The problem is one of speed.
I have a working SQL Server linked to a client accessible website, which I am wanting to add an Access front end to enable us office bound staff to better support both client and field staff.
The current form I have constructed is a main form with five sub forms contained within it, giving us all the relevant client information in one view. This works however is taking 24 seconds to load a single clients complete records.
I have looked at the SQL Server and found the absence of indexes, fixed this and got the time down to 24 seconds with consequent loads closer to 18 seconds depending on the client (some have a lot more records). This might be okay, as whilst this is a relative eternity in computing time in real world time its okay...but not great. I would like to see if I can get a better load by changing the way I connect and how the form is bound to the records etc.
In looking at the various ideas and reading a lot I found: https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/bind-a-form-to-an-ado-recordset?source=docs
Which appealed to me as I am more inclined to use ADO, seldom if ever to I use DAO. ADO I understood originally was intended to use with SQL and so on, and it seems like a sensible idea.
Again as I understand it if I can get this to work it will act as a pass through query returning only one record over the net and should consequently speed my form up considerably. However it wont work.
My code is:
Private Sub cssSetForm(lngID As Long)
Dim cnn As New ADODB.Connection
Dim Rs1 As New ADODB.Recordset
Dim strSQL As String
Dim strR As String
cnn = "Provider=MSOLEDBSQL;Server=Server;Database=DatabaseName;UID=UserName; PWD=Password;"
cnn.Open
strSQL = "SELECT Clients.Clientid, Clients.AccountContact, Clients.AccountEmail, Clients.Address, Clients.Name, Clients.OfficePhone, Clients.PostCode, " & _
"Clients.ShentonAcc, Clients.Suburb FROM Clients WHERE (((Clients.Clientid)=" & lngID & "));"
With Rs1
Set .ActiveConnection = cnn
.Source = strSQL
.LockType = adLockPessimistic
.CursorType = adOpenKeyset
.Open
End With
Debug.Print Rs1.RecordCount
Me.Recordset = Rs1
End sub
Now I am getting no errors until Me.Recordset=rs1 which is generating an error 3251 Operation is not supported for this type of object which is very nice for someone that understands why this is not supported when it is no different than I can see to the example I was copying from.
I don't understand why the form I am working on doesn't support recordsets according to the error message? Is there an error in my code? Is the error in my understanding of the destructions from the linked site? Is the error something else?
Thanks for the help
Set
keyword:Set Me.Recordset = Rs1
– Erik A