DAO > ADO

[kovan]

anyone could help me out with the following...
this is from another thread i posted,
here is DAO code
if anyone could take few min and give me the ADO version
i would greatly appreciated..
so i can do neccessary changes to my project
please and thank you

i think i will convert
after realizing my coding sucks

i decided to change my coding if it wont take more than a week of time

here are few things i like to know
(question mark(?) representing what woudl ADO version would be..


dao" dim db as database"
ado "?"

dao "dim rs as recordset"
ado "?"

dao "set db = OpenDatabase("myBadDatabase.MDB")"
ado "?"

dao "sql="SELECT * FROM MyTable""
"set rs = db.opendatase(sql)"
ado "?"

HERE IS A EXAMPLE FROM MY PROJECT (DAO)
if anyone would be kind enough to rewrite that with ADO
i would greatly appreciated, since it will help me do my conversion



[CODE]
Public Sub LoadManufacturer(cboBox As Object, Optional idx As Integer = -1)

SQL = "SELECT * FROM manufacturer"
Set ManufRecord = MeterInfo.OpenRecordset(SQL)

With ManufRecord
If .EOF Then
MsgBox "ERROR: Manufacturer table found to be empty"
Else
If idx = -1 Then
Do Until .EOF
cboBox.AddItem !Manu
.MoveNext
Loop
'cboBox.ListIndex = 0
Else
Do Until .EOF
cboBox(idx).AddItem !Manu
.MoveNext
Loop
'cboBox(idx).ListIndex = 0
End If
End If

End With

End Sub
[CODE/]

[Ianpbaker]

The conversion goes like this

Public Sub LoadManufacturer(cboBox As Object, Optional idx As Integer = -1)

    Dim MeterInfo as ADODB.Connection
    Dim ManufRecord as ADODB.Recordset

    Set MeterInfo = New ADODB.Connection
    Set ManufRecord = New ADODB.Recordset

    MeterInfo.Open "Driver={Microsoft Access Driver(*.mdb)};" & _ 
                   "Dbq=\somepath\mydb.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"
  
    SQL = "SELECT * FROM manufacturer"
 
    ManufRecord.Open SQL,MeterInfo
        
    With ManufRecord
        If .EOF Then
            MsgBox "ERROR: Manufacturer table found to be empty"
        Else
            If idx = -1 Then
                Do Until .EOF
                    cboBox.AddItem .fields("Manu").value
                    .MoveNext
                Loop
                'cboBox.ListIndex = 0
            Else
                Do Until .EOF
                    cboBox(idx).AddItem .fields("Manu").value
                    .MoveNext
                Loop
                'cboBox(idx).ListIndex = 0
            End If
        End If
        
    End With
    
End Sub

if you need any help feel free to reply back

Ian

[kovan]

thank you for the reply

Set MeterInfo = New ADODB.Connection
Set ManufRecord = New ADODB.Recordset

is that required? (i am assuming it is because creation of new objects)

i just wanna make sure..

    MeterInfo.Open "Driver={Microsoft Access Driver *.mdb)};" & _
                   "Dbq=c:\meter\meter.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

i get a error on that..
that is the correct path to my db..

thanks

[Ianpbaker]

Hi Kovan

The first part is required to create the new object.

As for your second problem I think it is becuause of the username part

try replacing it with

"Uid=;" & _

Should work now

Hope it does

Ian

[kovan]

i did get rid of that admin
here is the new one

    MeterInfo.Open "Driver={Microsoft Access Driver(*.mdb)};" & _
                   "Dbq=\c:\meter\meter.mdb;" & _
                   "Uid=;" & _
                   "Pwd=;"

the error is

Run-time error '-2147467259(800004005)':

[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified

in my reference.. i have Microsoft ActiveX Data Obect 2.5 Library

selected..

thanks

[kovan]

its

    MeterInfo.Open "Driver={Microsoft Access Driver(*.mdb)};" & _
                   "Dbq=c:\meter\meter.mdb;" & _
                   "Uid=;" & _
                   "Pwd=;"

last one was
[CODE
MeterInfo.Open "Driver={Microsoft Access Driver(*.mdb)};" & _
"Dbq=\c:\meter\meter.mdb;" & _
"Uid=;" & _
"Pwd=;"
[/CODE]


neither work

[Ianpbaker]

Try this one instead

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\somepath\mydb.mdb;" & _
"User Id=;" & _
"Password=;"


Ian

[kovan]

it seems to work thank you

cbobox.additem .fields("Manu").value

can the above be written as cbobox.additem !Manu

?

[Ianpbaker]

If you really want to then yes but it's old school programming. The easiest way to reference a field is recordset("Myfield")

Ian

[kovan]

sorry about that.. i should have tested it before asking that... i did test it and it seem to work..

is there a reason other than "old school"
why someone shoudn't use !Manu?

dont want to be a pain in the ass
but its important for me to know since i just started to do ADO..
one reason why i used !
is its shorter to type, and programmerz are lazy
hehe
but if you can give me a reason.. i will type that the .field..

thanks again
most kind

[Ianpbaker]

Basically it was used for VBA in access and then portted accross over to DAO, then ADO, but microsoft was ment to be fazing it out. Apart from that, there isn't really any other reason why not to use it. if your not using the with statement the one I used above
recordset("Myfield")
is just as quick

Ian