index & seek

[zezo2021]

Hello

this code works great on the local table in access without any problem

when upgrading from access to SQL server Azure
index and seek on this code not accept any solution from my side
I changed the dbOpenDynaset etc but it does not work also

the code is very long and depends on 2 line index and seek
the code is created by the previous programmer

I don't what I should do. thanks in advance

On Error GoTo Err_CheckPedigree_Click
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAnimals", DB_OPEN_TABLE)
Set Irs = db.OpenRecordset("tblImports", DB_OPEN_TABLE)
'Set Ors = db.OpenRecordset("tblOtherCodes", DB_OPEN_TABLE)
'Set crs = db.OpenRecordset("tblColours", DB_OPEN_TABLE)
Set brs = db.OpenRecordset("tblBreeds", DB_OPEN_TABLE)
rs.Index = "Animals"
'crs.Index = "ColourCode"
brs.Index = "PrimaryKey"

Sp = " "

Me!ss = ""
Me!ssbc = ""
Me!sd = ""
Me!sdbc = ""
Me!ds = ""
Me!dsbc = ""
Me!dd = ""
Me!ddbc = ""
Me!sss = ""
Me!sssbc = ""
Me!ssd = ""
Me!ssdbc = ""
Me!sds = ""
Me!sdsbc = ""
Me!sdd = ""
Me!sddbc = ""
Me!ddd = ""
Me!dddbc = ""
Me!dds = ""
Me!ddsbc = ""
Me!dsd = ""
Me!dsdbc = ""
Me!dss = ""
Me!dssbc = ""
Me!ssno = ""
Me!sdno = ""
Me!dsno = ""
Me!ddno = ""
Me!sssno = ""
Me!ssdno = ""
Me!sdsno = ""
Me!sddno = ""
Me!dssno = ""
Me!dsdno = ""
Me!ddsno = ""
Me!dddno = ""
Me!DDDSX = ""
Me!ddssx = ""
sbc = ""
sn = ""

    If IsNull(Forms!frmPedigree!AnmlRegnNo) Then
    Exit Sub
    Else
    rs.Seek "=", Forms!frmPedigree!AnmlOrigHerdBook, Forms!frmPedigree!AnmlRegnNo, Forms!frmPedigree!AnmlSex
    If rs.NoMatch Then
         Forms!frmPedigree!AnmlName1 = ""
         Exit Sub

 

[pbaldy]

It's very inefficient to open a recordset on the entire table and then look for a record. That will be even more of a problem with Azure; you can sometimes get away with it when the tables are local. Most of us would open the recordset on an SQL statement that searched for the specific record. You'd test for EOF to determine if a record or records were returned. This addresses a different topic but shows how the code might look:

Debugging

 

[xavier.batlle]

IMO the best and fastest option is to use a Pass-through query (if you don't need to update the tables)

 

[zezo2021]

thanks, friends;
but this required rebuilding the db, which had been built for years.
I need a solution for the old code if possible

 

[pbaldy]

From:

Recordset.Seek method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

 

[xavier.batlle]

From:

Recordset.Seek method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. However, if you use the OpenDatabase method to directly open an installable ISAM (non-ODBC) database, you can use Seek on tables in that database.

The OP said that he Back End is stored on SQL Server Azure, so this link doesn't apply.

 

[pbaldy]

The OP said that he Back End is stored on SQL Server Azure, so this link doesn't apply.

From Access's point of view it's still a linked table, so I suspect it does. Or you can advise the OP how to get their existing code to work and prove me wrong. Won't be the first time I've been wrong.

 

[Pat Hartman]

thanks, friends;
but this required rebuilding the db, which had been built for years.
I need a solution for the old code if possible

Some code needs to be fixed. And, this is a pretty easy fix so rather than trying to avoid actually fixing the problem, just do it. You just cannot convert an application BE from Jet to SQL Server and expect it to work smoothly when your app is using techniques that are diametrically opposed to what would be standard in SQL Server.

Really? How hard is it to change the code to run a query with an argument? I'm guessing this is a 15 minute fix.

 

[zezo2021]

friends
Can I use Ai to convert OLD DAO To ADO?
my second question Is ADO CAN WORK WITH LINKED TABLE TO AZURE SQL?
SPECIAL INDEX AND SEEK

 

[xavier.batlle]

friends
Can I use Ai to convert OLD DAO To ADO?
my second question Is ADO CAN WORK WITH LINKED TABLE TO AZURE SQL?
SPECIAL INDEX AND SEEK

No, AFAIK ADO connections to AZURE SQL servers don't implement the "seek" method. You can use "find" or "filter" to search records.

 

[Pat Hartman]

Can I use Ai to convert OLD DAO To ADO?

Unless your DAO is experiencing a problem, don't bother converting it.

If you think converting to ADO is the solution, the answer is NO IT IS NOT. This method (opening a recordset that reads an ENTIRE table and uses seek and find to locate records) is extremely inefficient with RDBMS' and that is why you need to convert it to a query. Just open a query that has criteria. It can't be much simpler than that. You are wasting time. Please try to convert the code to using a query.