August 14, 2018 at 5:16 pm
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 here
If .NoMatch = True Then
'...etc, etc
End if
So is there a fast way to challenge the table?
August 15, 2018 at 6:48 am
barry.nielson - Tuesday, August 14, 2018 5:16 PMHi 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?
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
August 15, 2018 at 8:33 am
Sue_H - Wednesday, August 15, 2018 6:48 AMDAO 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
+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)
August 15, 2018 at 8:50 am
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
August 15, 2018 at 2:08 pm
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
August 15, 2018 at 4:27 pm
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
August 16, 2018 at 6:54 am
barry.nielson - Wednesday, August 15, 2018 4:27 PMThanks 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
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)
August 17, 2018 at 6:48 am
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.
August 27, 2018 at 8:06 am
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)
August 27, 2018 at 11:36 am
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.
August 27, 2018 at 12:40 pm
Joe Torre - Monday, August 27, 2018 11:36 AMDAO 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.
Is that for all types of backends - SQL Server, Oracle, etc. or is that specific to using Access for the data?
Sue
August 27, 2018 at 12:50 pm
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
August 27, 2018 at 1:22 pm
Joe Torre - Monday, August 27, 2018 12:50 PMDAO 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
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
August 27, 2018 at 4:32 pm
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