Avatar of Rodger49
Rodger49 asked on

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.
DB Dev Tools

Avatar of undefined
Last Comment
Rodger49

8/22/2022 - Mon
gplana

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

gplana

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

ASKER CERTIFIED SOLUTION
Rodger49

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Rodger49

Still can't get the program to work. when I click the command button I get the error message 3001
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes