Public ADO recordsets/Connections

[gazza11]

Until now I have been using DAO to manipulate data in an Access 2000 database with VB6. Had a few problems with Windows2000 installation so I decided I should move on to ADO.
My DAO base program used a Module (.BAS) to publically open the database connection and declare recordsets that were common in different forms that I used.
I am attempting to modify my existing program so tried to do a similar thing with ADO ie open connection in module and declare public recordsets. It appears that although I can declare the recordsets they are not publically available.

Have I missed something - Can someone please tell me how I publically declare connections and recordsets for ADO.

 

[bridamol]

You have to define the objects as public.

Then properly access it. i.e.
<ModuleName>.<pulbic object/Variable>

Remeber that before accessing Connection or Recordset objects you must first inititate it and then open the connectionand recordset.

 

[gazza11]

Alas I was too hasty. I have been busy trying to find out why it will not work and have found that I can add a record.
ie
Dim CON as adodb.Connection
Dim rstSave as new adodb.recordset
Dim EmpSQL as string

If (iCurrentState = ADDING) then
rstsave.addnew
else
EmpSQL = &quot;SELECT Employee.Name, Employee.EmpID FROM Employee WHERE (Employee.EmpID)= lCurrentEmpID
Set rstsave = CON.Execute(EmpSQL)
if rstsave.recordcount > 0 then
rstsave.update
else
msgbox &quot;Oops&quot;
end if
end if

The add works fine - but when I try to update I get the error &quot;Runtime error 3251 - Object or Provider is not capable of performing requested operation&quot;

Surely I can update a record???

 

[CCLINT]

This is probably because the cursor type of the recordset returned by the connection object is forward only.

Replace this:

Set rstsave = CON.Execute(EmpSQL)

with:

With rstsave
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = EmpSQL
.ActiveConnection = CON
.Open Options:=adCmdText
End With

 

[gazza11]

Many thanks for the help. Will test this out and let you know how I go. Regards