ADP Function

[Frank Dulk]

a help on ADP, I am using Access 2000 with SQL Server 2000.

I made the connections with bank sql, the tables, views, procedures are in
my project window to oh everything well.

It happens that when I will use a code that I use a lot in access without
sql, gives mistake.

I already read a lot of things in the it helps to respect, but unhappily I
didn't get to connect the points.

For instance:

Function Teste()
Dim Rs as Recordset
Set Rs = currentdb.OpenRecordSet("Select * From Clientes")
Rs.FindFirst "ID_Cliente = 10"
If Rs.NoMatch then
MsgBox " Cliente Not found "
Else
MsgBox " Found Customer"
endif
Set Rs = nothing
End Function
I saw in the help that more I cannot use (FindFirst nor NoMatch) oh it is
the problem, I thought about using it is clear (Select * From Clientes Where
ID_Cliente = 1), but in the one that I saw here in the help, every time that
will read the Banco SQL I will have to do a new Connection,
connertion.execute (" Select *...") and for oh... it goes...

As in Access I used the command here Currentdb in ADP doesn't have as using
something of the type, for not being long so much every time tends to do
connection again as bank that is somewhere (67.19.225.8) and to delay
everything again... making the connection... and reading the data

For the that I see here, the data are already connected in the Projeto ADP,
they are not.. would not have as using the method direct OpenRecordSet, or
similar thing...

 

[Norman Yuan]

In APD, you cannot use Application.CurrentDB(), instead, use
Application.CurrentProject.

ADP uses ADO while CurrentDB() returns a DAO.Database object. CurrentDB() is
for *.mdb, not *.adp.

 

[Alex]

Hi Frank,

As Norman has said using currentproject.connection is the way forward,
if you are using an adp project then the connection is established as
soon as the project is opened and closed when the project is closed. On
the point about FindFirst and NoMatch these are DAO technologies and it
requires a rethink about how things works.

1. DAO open the recordset then find the record, then find any others
e.g. next matching record.

2. ADO open the recordset with a select query that finds the correct
record first e.g. 'Select * from Clientes Where ClientName='" & "Frank"
& "'.

The difference between the two technologies in your example is one
opens the recordset (all records) then goes to find the matching ones
(DAO), the other opens the recordset with only the matching records
(ADO). In migrating code you will find you are opening and closing
recordsets more often but it is a small price to pay.

In your loops when you are searching for a new search string it is good
practice to test to see if the recordset is already open if so close it
and then re-open it with the new select statement.

e.g.

if adoTable.state = adstateopen then
adoTable.close
end if
adoTable.open mySelectStatement, currentproject.connection, cursortype,
locktype

Hope it helps


Regards

Alex