Retreiving data using an SQL string

[AccessDevJunior]

hya,
i am new to vb6 my experience lies with access.
i have 5 lines of code that retreives data from a table within an accessdatabase. i want to use the same principle to retreive the data but rather than retreive one field from one table i would like to use SQL to get exactly what i want:

Private Sub cmdRun_Click()

Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("F:\BCT\BCT_be.mdb")
Set rs = db.OpenRecordset("tblEmployeeInfo")
ListCompany.AddItem rs.Fields("Employee Name")

End Sub

If anyone could help me with this it would be a great help?

 

[TCARPENTER]

Try something like this:

Dim db As Database
Dim rs As Recordset
Dim sSQL as string

sSQL = "SELECT * FROM tblEmployeeInfo"

Set db = OpenDatabase("F:\BCT\BCT_be.mdb")
Set rs = db.OpenRecordset(sSQL)
ListCompany.AddItem rs.Fields("Employee Name")

HTH
Todd

 

[lykeat]

An alternative to retrieve the data using SQL. Hope this help.

Dim Con as ADODB.Connection 
Dim RS as ADODB.Recordset 
Dim SQL as string

ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\BCT\BCT_be.mdb; Persist Security Info=False"

SQL = "SELECT * FROM tblEmployeeInfo"

Set Con = new ADODB.Connection
Con.Open Con
    
Set RS = new ADODB.Recordset
RS.Open SQL, Con
    
Do until RS.EOF
    ListCompany.AddItem RS("Employee Name")
    RS.MoveNext
Loop
RS.Close
Con.Close  
Set RS = Nothing
Set Con = nothing

 

[AccessDevJunior]

thanx
okay ive done this, but it is only listing 1 recording (there should be 2):???

Dim db As Database
Dim rs As Recordset
Dim sSQL As String

sSQL = "SELECT tblEmployeeInfo.[Employee Company] FROM tblEmployeeInfo;"
Set db = OpenDatabase("F:\BCT\BCT_be.mdb")
Set rs = db.OpenRecordset(sSQL)
ListCompany.AddItem rs.Fields("Employee Company")

 

[AccessDevJunior]

hya, lykeat
i am getting an error with "Constring =" ???

 

[HarleyQuinn]

Hi,

Have you declared Constring as a string?

Cheers

Harleyquinn

 

[jajinder]

ConString should be Con.ConnectionString

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[AccessDevJunior]

yes thanx harleyquinn ive just done that, now im getting a msg box "data source not found, use the ODBC driver manager"
i wanted to stay away from using the driver manager.
once ive completed my application i will be required to install it over 400 users computers and i dont want to have to set up a data source everytime, also as i am new to vb6 i have no knowledge of setting such data sources.
what is your opinion? do you think i will need to set up an ODBC data source?