• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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.
  • 2
  • 2
1 Solution
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"

'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

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

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
Rodger49Author Commented:
Still can't get the program to work. when I click the command button I get the error message 3001
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Increase Security & Decrease Risk with NSPM Tools

Analyst firm, Enterprise Management Associates (EMA) reveals significant benefits to enterprises when using Network Security Policy Management (NSPM) solutions, while organizations without, experienced issues including non standard security policies and failed cloud migrations

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now