How do I retreive info from access table in vb?

[Debbie]

I have tables in access. I'm writing vb code w/ an input box to allow users
to
type in what they are searching for. I need to use a loop to compare each
item in a field to see if it matches what the user typed in the inputbox.
how do I pull the information into the vb code from the access table?

thanks!

[Brian Bishop]

"Debbie" <sunshinedlp@netzero.net> wrote:
>
>I have tables in access. I'm writing vb code w/ an input box to allow users
>to
>type in what they are searching for. I need to use a loop to compare each
>item in a field to see if it matches what the user typed in the inputbox.
>how do I pull the information into the vb code from the access table?
>
>thanks!
>
Debbie are you familiar with ADO

If you are the best way to accomplish your task is to query the database
table using a parameterized query.

To use ado that best way is to set a referance in your project to the ado
type library that way you will get intellisense.

In order to set this referance select the project menu option on the menu
bar then choose references.

A dialog box will appear with a list of all of the type libraries currently
resident on you computer. Put a check next to the type library name Microsoft
ActiveX Data Objects 2.x Library. Then hit the ok button you will now have
a reference to ado in you project.

You will then need to write code to create a ado connection object and and
to query your database.

An example of this is below

Public Function GetData(ByVal v_vntParam As Variant, _
ByVal v_sField As String) As ADODB.Recordset
Dim p_sSQL As String
Dim p_sWHERE As String
Dim p_oConnection As ADODB.Connection
Dim p_rsData As ADODB.RecordSet

Set p_oConnection = New ADODB.Connection
Set p_rsData = New ADODB.Recordset

p_sSQL = "SELECT " _
& "* " _
& "FROM " _
& "your table name "

Select Case VarType(v_vntParam)
Case vbString
p_sWHERE = "WHERE " _
& v_sFieldName & " = '" & v_vntData & "'"
Case vbInteger,vbDouble,vbLong
p_sWHERE = "WHERE " _
& v_sFieldName & " = " & v_vntData
Case vbDate
p_sWHERE = "WHERE " _
& v_sFieldName & " = #" & v_vntData & "#"
Case Else
Err.Raise vbObjectError + 1001
End Select

With p_oConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.DataSource = "C:\Temp\Mydatabase.mdb" 'Database path
.Open
End With

p_rsData.Open p_sSQL,p_oConnection

Set GetData = p_rsData

End Function

This is very simplified code on how to get data from a database based on
a parameter but, without allot of error handling or security issues taken
into account but, this basic code will get you what you want.

[Brian Bishop]

"Brian Bishop" <bbishop@fnmortgage.com> wrote:
>
>"Debbie" <sunshinedlp@netzero.net> wrote:
>>
>>I have tables in access. I'm writing vb code w/ an input box to allow
users
>>to
>>type in what they are searching for. I need to use a loop to compare each
>>item in a field to see if it matches what the user typed in the inputbox.
>>how do I pull the information into the vb code from the access table?
>>
>>thanks!
>>
>Debbie are you familiar with ADO
>
>If you are the best way to accomplish your task is to query the database
>table using a parameterized query.
>
>To use ado that best way is to set a referance in your project to the ado
>type library that way you will get intellisense.
>
>In order to set this referance select the project menu option on the menu
>bar then choose references.
>
>A dialog box will appear with a list of all of the type libraries currently
>resident on you computer. Put a check next to the type library name Microsoft
>ActiveX Data Objects 2.x Library. Then hit the ok button you will now have
>a reference to ado in you project.
>
>You will then need to write code to create a ado connection object and and
>to query your database.
>
>An example of this is below
>
>Public Function GetData(ByVal v_vntParam As Variant, _
> ByVal v_sField As String) As ADODB.Recordset
>Dim p_sSQL As String
>Dim p_sWHERE As String
>Dim p_oConnection As ADODB.Connection
>Dim p_rsData As ADODB.RecordSet
>
> Set p_oConnection = New ADODB.Connection
> Set p_rsData = New ADODB.Recordset
>
> p_sSQL = "SELECT " _
> & "* " _
> & "FROM " _
> & "your table name "
>
> Select Case VarType(v_vntParam)
> Case vbString
> p_sWHERE = "WHERE " _
> & v_sFieldName & " = '" & v_vntData & "'"
> Case vbInteger,vbDouble,vbLong
> p_sWHERE = "WHERE " _
> & v_sFieldName & " = " & v_vntData
> Case vbDate
> p_sWHERE = "WHERE " _
> & v_sFieldName & " = #" & v_vntData & "#"
> Case Else
> Err.Raise vbObjectError + 1001
> End Select
>
> With p_oConnection
> .Provider = "Microsoft.Jet.OLEDB.4.0"
> .DataSource = "C:\Temp\Mydatabase.mdb" 'Database path
> .Open
> End With
>
> p_rsData.Open p_sSQL,p_oConnection
>
> Set GetData = p_rsData
>
>End Function
>
>This is very simplified code on how to get data from a database based on
>a parameter but, without allot of error handling or security issues taken
>into account but, this basic code will get you what you want.

Sorry I forgot one line of code before the statement with p_oConnection
you must add this line of code p_sSQL = p_sSQL & p_sWHERE

Sorry about that.