VB 6 and Access

I have a program in vb 6 program that is connected to access 2007. In the database I have over 3000  entry's. I want to search for a name or account number to bring up all the information on that person. How do I do it. I tried search and find but no help.
Rodger49Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
You should use a recordset. Here is an example:

Dim conn As New ADODB.Connection 'la conexion a la base de datos Access
Dim rs As ADODB.Recordset 'iterador que contiene los datos de una consulta
Dim path As String 'archivo de la base de datos Access

path = "c:\datos.mdb"

'Realizamos la conexión
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False"
conn.Open

'Realizamos la consulta
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM corredores", conexion, adOpenKeyset, adLockOptimistic

'Modificamos los controles del formulario
Set DataGrid1.DataSource = rs
Text1.Text = rs.RecordCount

Open in new window

0
gplanaCommented:
On line 13 there is the SELECT statement that will be run by access. If you want to filter by name use something like:

rs.Open "SELECT * FROM you_table WHERE name='" . the_name . "'", conn, adOpenKeyset, adLockOptimistic

Open in new window


If you want to search by account number:

rs.Open "SELECT * FROM you_table WHERE account_number=" . the_account_number, conn, adOpenKeyset, adLockOptimistic

Open in new window

0
Rodger49Author Commented:
In my database the cols are named: MemberNo,Name,Address1,Address2,City.............
When I try to run the program; I get the error message 3001. when I debug the yellow marker is on the Set DB = DBEngine.....line. Help
Dim DB As Database
    Dim RS As Recordset
    Dim SQL As String
    Set DB = DBEngine.Workspaces(0).OpenDatabase("C:\Eagles\FOEAerie3814.mdb", False)
    SQL = "Select* FROM Member WHERE Name = " & strMemberNo
    Set RS = DB.OpenRecordset(SQL, dbOpenFowardOnly, dbReadOnly)
    If Not RS.EOF Then
        txtMemberNo.Text = RS![Text1]
        txtName.Text = RS![Text2]
     End If
     RS.Close
     DB.Close
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rodger49Author Commented:
Still can't get the program to work. when I click the command button I get the error message 3001
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Dev Tools

From novice to tech pro — start learning today.