initialization of database connection in VB

[astraltango]

How do I initialize a connection to my Access Database in VB?

eg, how would I initialize cnMyDB to use in:
cnMyDB.Execute("Select Value from Table where key=2&quot

Cheers

 

[nicsin]

There are a number of ways for doing it as have been discussed in many TT threads. My favourite is using ADO objects. In order to use them you will have to include a reference (Project -> References) to Microsoft ActiveX Data Objects (I suggest to use ADO 2.5). Then use this code:

Dim con as ADODB.Connection
Dim rs as ADODB.Recordset

set con = New ADODB.Connection
set rs = New ADODB.Recordset

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb"

with rs
    .Open "select value from table1", con

    'rs holds all the records of table1 now

    if not (.bof and .eof)
        .moveFirst
        Do until .eof
            msgBox rs(0)
            .moveNext
        Loop
    else
       msgBox "The recordset is empty"
    end if
    .close
end with

set rs = Nothing
set con = Nothing