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.

 

[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.