Findfirst VBA for SQL table
-
Hi All,
So I am transferring an access BE to SQL. One of the things I've noticed is that some code is much slower to use for example
Note: Tbl_MyTable is a linked table to an SQL db. in a previous DAO table, this code ran fast and had no trouble (given any mistakes in posting and editing)Dim rsRecordset as DAO.recordset
Set rsrRecordset = CurrentDb.OpenRecordset("Tbl_MyTable", dbOpenDynaset, dbSeeChanges)
With rsrRecordset
strSQL = _
"[OrtherTableID] = " & ngOTID & " AND " & _
"[ActivityID] = 1 AND " & _
"[RelatedReferenceID] = " & lngRRID & " AND " & _
"[TableType] = 'Account' AND " & _
"[RelatedReferenceType] = '" & strRRType & "'"
.FindFirst strSQL ' Takes a long time to find the record hereIf .NoMatch = True Then
'...etc, etc
End ifSo is there a fast way to challenge the table?
- barry.nielson - Tuesday, August 14, 2018 5:16 PM
DAO was designed specifically to interact with the Jet database engine.
For SQL Server tables, a parameterized stored procedure is likely faster for your query. And using ADO is something to consider when changing the database to SQL Server.Sue
- Sue_H - Wednesday, August 15, 2018 6:48 AM
+100 to that bit about using ADO instead. DAO was an old method that only effectively works with the Jet database engine, and is horrible with SQL Server. Do please update your code to use the ADO objects in "References" and learn about the object model, because it IS rather different than DAO. I had to go through that transition with VBA and MS Access back in the mid 1990's, and it was challenging because I had a LOT of the older DAO code. However, using ADO is much easier in the long run.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
Let's not forget about the possibility of SQL Injection. This code seems wide open to it.
Bobby Tables: A guide to preventing SQL injection -
Pass the query through to SQL Server and let it find the row.
Dim rsRecordset as DAO.recordset, Sql as String
Set Sql = "SELECT * FROM Tbl_MyTable WHERE [OrtherTableID] = " & ngOTID & " AND " & _
"[ActivityID] = 1 AND " & _
"[RelatedReferenceID] = " & lngRRID & " AND " & _
"[TableType] = 'Account' AND " & _
"[RelatedReferenceType] = '" & strRRType & "'"
Set rsrRecordset = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbSeeChanges)
With rsrRecordset
.Open
If .BOF = .EOF Then
'...etc, etc
End if
End With -
Thanks to all who responded.
I have learned that ADO is the best option. It works just fine. I wanted to avoid it because so much of my Db is DAO coded and so I must now go through and activity crossgrade it. Weeks of work involved in modifying a db that took 3 years to build. Ho hum.... well best get started.
Thanks again all
- barry.nielson - Wednesday, August 15, 2018 4:27 PM
I feel your pain. Went through this kind of thing in the mid-1990's. Was VERY glad I invested the time and updated that code. It worked Soooo much better in the long run.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
Ouch. My sympathies.
When I encountered a similar situation my solution was to replace not only the back end but the front end as well. In other words a complete re-write from VBA/Jet to VB.Net/SQL Server.
It was a rather extreme learning curve.
I have to say for all the hate Access engenders MS got a LOT of it right. The only real problems were the lack of scalability and the (utter) lack of robust security (i.e. you can read Access passwords with programs freely available on the net. Bad show, MS!).
If you dived deeply into DAO and VBA you could make Access applications truly formidable, aside from scaling and security. Of course in todays world... Shrug.
- roger.plowman - Friday, August 17, 2018 6:48 AMOuch. My sympathies.
When I encountered a similar situation my solution was to replace not only the back end but the front end as well. In other words a complete re-write from VBA/Jet to VB.Net/SQL Server.
It was a rather extreme learning curve.
I have to say for all the hate Access engenders MS got a LOT of it right. The only real problems were the lack of scalability and the (utter) lack of robust security (i.e. you can read Access passwords with programs freely available on the net. Bad show, MS!).
If you dived deeply into DAO and VBA you could make Access applications truly formidable, aside from scaling and security. Of course in todays world... Shrug.
And truth be told, you still CAN do those things with ADO. The best way to use ADO and MS Access with VBA is as a fairly quick way to mock up a web application. MS Access makes for a much quicker mock-up, and can even be used for near final testing if you bring all your object references with you...
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) -
DAO is still the object model of choice when working with Access objects. You can use both if you want to and I have and do. Certain things are much simpler to write using ADO working with SPs and other SQL Server objects. For a time MS recommended ADO for Access VBA however more recently they have gone back to recommending DAO for Access Applications.
- Joe Torre - Monday, August 27, 2018 11:36 AM
Is that for all types of backends - SQL Server, Oracle, etc. or is that specific to using Access for the data?
Sue
-
DAO for Access objects specifically is easier. To get the same functionality you would need both ADO and ADOX. So say I want to write a script to link every table in a SQL Server or Oracle database. It is way easier in DAO. I haven't been working that much with Access anymore apart from providing tech support within our organization, however in my experience DAO is the better object model for working with Access.
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother/dao-versus-ado-in-vba-for-access/41bdd1fc-924e-4aad-984a-cc2f766179ef - Joe Torre - Monday, August 27, 2018 12:50 PM
With this being a SQL Server backend, I was wondering if Microsoft recommends using DAO as I didn't think that was the case. That's more of what I was asking about.
Sue
-
I would agree with you on that. ADODB is better for SQL Server objects. I would like it better if MS made use of .net framework in VBA which I like better than ADO. I wish MS didn't drop support for Access ADP files. That was the bomb.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply