Can you convert this code

Can someone help me convert this code from ADO to DAO. I'm getting nowhere. It takes and reads the tbl_Catagories which has two field (containing 48 records - CheckboxID and Description) and populates the Caption of a checkbox array. It works in ADO but the project I need to use it in is DAO based.

Private Sub LoadCheckBoxDescriptions()

Dim oCnn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRs As ADODB.Recordset

Set oCnn = New ADODB.Connection
Set oCmd = New ADODB.Command

With oCnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\db1.mdb"
.Open
End With

With oCmd
.ActiveConnection = oCnn
.CommandType = adCmdTable
.CommandText = "tbl_Categories"
Set oRs = .Execute
End With

Do While Not oRs.EOF
chkOption(oRs!Checkboxid).Caption = oRs!Description
oRs.MoveNext
Loop

Set oRs = Nothing
Set oCmd = Nothing
Set oCnn = Nothing

End Sub

Any help would be appraciated:

Thanks,
Rev. Michael L. Burns

i can ... well I think anyway :)

VB Code:

Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(App.Path & "\db1.mdb")
Set rs = db.OpenRecordset("tbl_Categories")
 
While Not rs.EOF
    chkOption(rs("Checkboxid")).Caption = rs("Description")
    rs.MoveNext
Wend
 
db.Close
rs.Close
 
Set db = Nothing
Set rs = Nothing

should do it :)

Peet,

Thnaks for the quick reply. Your post was essentially what I had come up with but I still can get it to work.

I placed Debug.Print "Got This Far!" right after the Set db and before Set rs and it worked that far but moving the Dedug to follow the Set rs does nothing. Appears like it never gets beyond this point.

Pastor Mike

I think the syntax is ok,
that means we have to consentrate on the database....

Is this table enormous?
Have u tried repair and compact on the db?

The table was just created this morning and only has 48 records, 1 for each chkbox caption.

have u tried opening any other tables in the db?

see if u can get beond the set rs=db.openrecordset("anotherTable")

trying to narrow it down :)

Peet,

All the tables seem to open ok when using a data control if that means anything but replacing the tbl_Catagories name in your example code for any table never gets beyond the Set rs

try query the table...

VB Code:

Set rs = db.OpenRecordset("SELECT * FROM tbl_Catergories")

if u can, please feel free to upload a sample db, and I'll be glad to look at it...see if I get into same problem..

Peet,

I got it working by changing the two Dim statements to read as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Now works like a champ.

Removed the DAO above and it broke again. Put it back and it worked. Any idea why? At any rate, thanks for your help.

Rev. Michael L. Burns

Glad u got it working Michael :)

Seems that ur app had problem distinguish between ADO and DAO. Did u have referense to both DAO and ADO in u'r project?

Peet,

That was it. Way back when I first started this project I was planning to do it in ADO but because of a set of controls that I needed to use (Sheridan Data Widgets 2.0 DataGrid and DBCombo) which don't work with ADO (Version 3.0 does but can't afford to upgrade) I was forced to alter my plans and just plain forgot to remove the Reference to Microsoft ActiveX 2.5 Object Library. I learn something new everyday.

Not sure if there would ever be a need to use both ADO and DAO in the same project but I guest it wouldn't be bad practice to use the appropriate prefix as I did lastnight, either DAO.rs or ADO.rs to avoid problems in the future.

Mike