I am looking to see if some one can give me some basic code, using
ADO, that will query an Access database using a cell value as a
variable and then returning a recordset value to a cell. i.e.

cell A1= employeeNumber
using employee number, query the tblEmployee and return the Employee
name in B2

any help is appreciated. If anyone know of a good tutorial site for
doing this type of thing i would appreciate it.

thanks

'Give this a go. Don't forget to; Tools|References and check the box for
'Microsoft ActiveX Data Objects 2.x Library'

Use the highest version that will still support your clients.


Sub ReadAccess()

Const adLockReadOnly = 1
Const adOpenForwardOnly = 0

Dim strConnect As String, strSQL As String, i As Integer

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data " _
& "Source=C:\Program Files\Microsoft " _
& "Office\Office12\SAMPLES\Northwind.mdb;" _
& "Persist Security Info=False"

Sheets(1).Range("B1") = "eastc"
strSQL = "SELECT Customers.ContactName " _
& "FROM Customers " _
& "WHERE (((Customers.CustomerID)='" & Sheets(1).Range("b1") & "')); "

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

i = 1
objConnection.Open strConnect
objRecordset.Open strSQL, objConnection, _
adOpenForwardOnly, adLockReadOnly
Do While objRecordset.EOF = False
Sheets(1).Range("A" & i) = objRecordset!ContactName
i = i + 1
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Matt" wrote:
>I am looking to see if some one can give me some basic code, using
> ADO, that will query an Access database using a cell value as a
> variable and then returning a recordset value to a cell. i.e.
>
> cell A1= employeeNumber
> using employee number, query the tblEmployee and return the Employee
> name in B2
>
> any help is appreciated. If anyone know of a good tutorial site for
> doing this type of thing i would appreciate it.
>
> thanks
>

On Jun 23, 6:49 pm, "Dave Patrick" <DSPatr...@nospam.gmail.com> wrote:
> 'Give this a go. Don't forget to; Tools|References and check the box for
> 'Microsoft ActiveX Data Objects 2.x Library'
>
> Use the highest version that will still support your clients.
>
> Sub ReadAccess()
>
> Const adLockReadOnly = 1
> Const adOpenForwardOnly = 0
>
> Dim strConnect As String, strSQL As String, i As Integer
>
> strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data " _
> & "Source=C:\Program Files\Microsoft " _
> & "Office\Office12\SAMPLES\Northwind.mdb;" _
> & "Persist Security Info=False"
>
> Sheets(1).Range("B1") = "eastc"
> strSQL = "SELECT Customers.ContactName " _
> & "FROM Customers " _
> & "WHERE (((Customers.CustomerID)='" & Sheets(1).Range("b1") & "')); "
>
> Set objConnection = CreateObject("ADODB.Connection")
> Set objRecordset = CreateObject("ADODB.Recordset")
>
> i = 1
> objConnection.Open strConnect
> objRecordset.Open strSQL, objConnection, _
> adOpenForwardOnly, adLockReadOnly
> Do While objRecordset.EOF = False
> Sheets(1).Range("A" & i) = objRecordset!ContactName
> i = i + 1
> objRecordset.MoveNext
> Loop
> objRecordset.Close
> objConnection.Close
> End Sub
>
> --
>
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]http://www.microsoft.com/protect
>
>
>
> "Matt" wrote:
> >I am looking to see if some one can give me some basic code, using
> > ADO, that will query an Access database using a cell value as a
> > variable and then returning a recordset value to a cell. i.e.
>
> > cell A1= employeeNumber
> > using employee number, query the tblEmployee and return the Employee
> > name in B2
>
> > any help is appreciated. If anyone know of a good tutorial site for
> > doing this type of thing i would appreciate it.
>
> > thanks- Hide quoted text -
>
> - Show quoted text -

I will it give it a try! Thanks!

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Matt" wrote:
> I will it give it a try! Thanks!
>

Try this
Sub GetEmployeeName()

Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim employeeID As String

i = 2
'used to get employee ID Number
employeeID = Range("A1").Value

'you could also use this just remove the '
'employeeID = InputBox("Select employee number", "Get Employee Number",
"<Employee Number>")

'Change data source path to the path of you database
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\PathTO\MyDataBase\" _
& "Employee.mdb;Persist Security Info=False"

'You will need to modify this section the Select , From and Where
'you do not need to modify =" & employeeID & ")); " part of code
'if i knew the table and fieds i could help a little more

strSQL1 = "SELECT Employee.NAME, tblEmployee .EMPLOYEE_ID " _
& "FROM tblEmployee " _
& "WHERE (((tblEmployee .EMPLOYEE_ID)=" & employeeID & ")); "


Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Range("B" & i) = rs1!Name

i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

"Matt" wrote:

> I am looking to see if some one can give me some basic code, using
> ADO, that will query an Access database using a cell value as a
> variable and then returning a recordset value to a cell. i.e.
>
> cell A1= employeeNumber
> using employee number, query the tblEmployee and return the Employee
> name in B2
>
> any help is appreciated. If anyone know of a good tutorial site for
> doing this type of thing i would appreciate it.
>
> thanks
>
>

On Jun 23, 7:35 pm, Mike <M...@discussions.microsoft.com> wrote:
> Try this
> Sub GetEmployeeName()
>
> Dim cnn As ADODB.Connection
> Dim rs1 As ADODB.Recordset
> Dim strSQL1 As String, strConn
> Dim i As Integer
> Dim employeeID As String
>
> i = 2
> 'used to get employee ID Number
> employeeID = Range("A1").Value
>
> 'you could also use this just remove the '
> 'employeeID = InputBox("Select employee number", "Get Employee Number",
> "<Employee Number>")
>
> 'Change data source path to the path of you database
> strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> & "Data Source=C:\PathTO\MyDataBase\" _
> & "Employee.mdb;Persist Security Info=False"
>
> 'You will need to modify this section the Select , From and Where
> 'you do not need to modify =" & employeeID & ")); " part of code
> 'if i knew the table and fieds i could help a little more
>
> strSQL1 = "SELECT Employee.NAME, tblEmployee .EMPLOYEE_ID " _
> & "FROM tblEmployee " _
> & "WHERE (((tblEmployee .EMPLOYEE_ID)=" & employeeID & ")); "
>
> Set cnn = New ADODB.Connection
> Set rs1 = New ADODB.Recordset
> cnn.Open strConn
> rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
> Do While rs1.EOF = False
> Range("B" & i) = rs1!Name
>
> i = i + 1
> rs1.MoveNext
> Loop
> rs1.Close
> cnn.Close
> End Sub
>
>
>
> "Matt" wrote:
> > I am looking to see if some one can give me some basic code, using
> > ADO, that will query an Access database using a cell value as a
> > variable and then returning a recordset value to a cell. i.e.
>
> > cell A1= employeeNumber
> > using employee number, query the tblEmployee and return the Employee
> > name in B2
>
> > any help is appreciated. If anyone know of a good tutorial site for
> > doing this type of thing i would appreciate it.
>
> > thanks- Hide quoted text -
>
> - Show quoted text -

Thanks! I am just trying to understand what i=i+1 is about. Is that
there for the loop in case I am trying to pull more than the Name?
ie, if I wanted ss# then it would be placed in b3...etc??

the i = i +1 is a loop to get next row in case you were pulling more then
one employee at a time
If you look at the top of the code you will see i = 2 meaning row 2
If your wanting to add something else you would use
Range("C" & i) = rs1!SocialNumber
Range("D" & i) = rs1!DateOfHire
and so on
"Matt" wrote:

> On Jun 23, 7:35 pm, Mike <M...@discussions.microsoft.com> wrote:
> > Try this
> > Sub GetEmployeeName()
> >
> > Dim cnn As ADODB.Connection
> > Dim rs1 As ADODB.Recordset
> > Dim strSQL1 As String, strConn
> > Dim i As Integer
> > Dim employeeID As String
> >
> > i = 2
> > 'used to get employee ID Number
> > employeeID = Range("A1").Value
> >
> > 'you could also use this just remove the '
> > 'employeeID = InputBox("Select employee number", "Get Employee Number",
> > "<Employee Number>")
> >
> > 'Change data source path to the path of you database
> > strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> > & "Data Source=C:\PathTO\MyDataBase\" _
> > & "Employee.mdb;Persist Security Info=False"
> >
> > 'You will need to modify this section the Select , From and Where
> > 'you do not need to modify =" & employeeID & ")); " part of code
> > 'if i knew the table and fieds i could help a little more
> >
> > strSQL1 = "SELECT Employee.NAME, tblEmployee .EMPLOYEE_ID " _
> > & "FROM tblEmployee " _
> > & "WHERE (((tblEmployee .EMPLOYEE_ID)=" & employeeID & ")); "
> >
> > Set cnn = New ADODB.Connection
> > Set rs1 = New ADODB.Recordset
> > cnn.Open strConn
> > rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
> > Do While rs1.EOF = False
> > Range("B" & i) = rs1!Name
> >
> > i = i + 1
> > rs1.MoveNext
> > Loop
> > rs1.Close
> > cnn.Close
> > End Sub
> >
> >
> >
> > "Matt" wrote:
> > > I am looking to see if some one can give me some basic code, using
> > > ADO, that will query an Access database using a cell value as a
> > > variable and then returning a recordset value to a cell. i.e.
> >
> > > cell A1= employeeNumber
> > > using employee number, query the tblEmployee and return the Employee
> > > name in B2
> >
> > > any help is appreciated. If anyone know of a good tutorial site for
> > > doing this type of thing i would appreciate it.
> >
> > > thanks- Hide quoted text -
> >
> > - Show quoted text -
>
> Thanks! I am just trying to understand what i=i+1 is about. Is that
> there for the loop in case I am trying to pull more than the Name?
> ie, if I wanted ss# then it would be placed in b3...etc??
>
>

On Jun 23, 8:25 pm, Mike <M...@discussions.microsoft.com> wrote:
> the i = i +1 is a loop to get next row in case you were pulling more then
> one employee at a time
> If you look at the top of the code you will see i = 2 meaning row 2
> If your wanting to add something else you would use
> Range("C" & i) = rs1!SocialNumber
> Range("D" & i) = rs1!DateOfHire
> and so on
>
>
>
> "Matt" wrote:
> > On Jun 23, 7:35 pm, Mike <M...@discussions.microsoft.com> wrote:
> > > Try this
> > > Sub GetEmployeeName()
>
> > > Dim cnn As ADODB.Connection
> > > Dim rs1 As ADODB.Recordset
> > > Dim strSQL1 As String, strConn
> > > Dim i As Integer
> > > Dim employeeID As String
>
> > > i = 2
> > > 'used to get employee ID Number
> > > employeeID = Range("A1").Value
>
> > > 'you could also use this just remove the '
> > > 'employeeID = InputBox("Select employee number", "Get Employee Number",
> > > "<Employee Number>")
>
> > > 'Change data source path to the path of you database
> > > strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> > > & "Data Source=C:\PathTO\MyDataBase\" _
> > > & "Employee.mdb;Persist Security Info=False"
>
> > > 'You will need to modify this section the Select , From and Where
> > > 'you do not need to modify =" & employeeID & ")); " part of code
> > > 'if i knew the table and fieds i could help a little more
>
> > > strSQL1 = "SELECT Employee.NAME, tblEmployee .EMPLOYEE_ID " _
> > > & "FROM tblEmployee " _
> > > & "WHERE (((tblEmployee .EMPLOYEE_ID)=" & employeeID & ")); "
>
> > > Set cnn = New ADODB.Connection
> > > Set rs1 = New ADODB.Recordset
> > > cnn.Open strConn
> > > rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
> > > Do While rs1.EOF = False
> > > Range("B" & i) = rs1!Name
>
> > > i = i + 1
> > > rs1.MoveNext
> > > Loop
> > > rs1.Close
> > > cnn.Close
> > > End Sub
>
> > > "Matt" wrote:
> > > > I am looking to see if some one can give me some basic code, using
> > > > ADO, that will query an Access database using a cell value as a
> > > > variable and then returning a recordset value to a cell. i.e.
>
> > > > cell A1= employeeNumber
> > > > using employee number, query the tblEmployee and return the Employee
> > > > name in B2
>
> > > > any help is appreciated. If anyone know of a good tutorial site for
> > > > doing this type of thing i would appreciate it.
>
> > > > thanks- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks! I am just trying to understand what i=i+1 is about. Is that
> > there for the loop in case I am trying to pull more than the Name?
> > ie, if I wanted ss# then it would be placed in b3...etc??- Hide quoted text -
>
> - Show quoted text -

thanks again for the explanation and your time!

On Jun 23, 9:19 pm, Matt <mwagn...@gmail.com> wrote:
> On Jun 23, 8:25 pm, Mike <M...@discussions.microsoft.com> wrote:
>
>
>
>
>
> > the i = i +1 is a loop to get next row in case you were pulling more then
> > one employee at a time
> > If you look at the top of the code you will see i = 2 meaning row 2
> > If your wanting to add something else you would use
> > Range("C" & i) = rs1!SocialNumber
> > Range("D" & i) = rs1!DateOfHire
> > and so on
>
> > "Matt" wrote:
> > > On Jun 23, 7:35 pm, Mike <M...@discussions.microsoft.com> wrote:
> > > > Try this
> > > > Sub GetEmployeeName()
>
> > > > Dim cnn As ADODB.Connection
> > > > Dim rs1 As ADODB.Recordset
> > > > Dim strSQL1 As String, strConn
> > > > Dim i As Integer
> > > > Dim employeeID As String
>
> > > > i = 2
> > > > 'used to get employee ID Number
> > > > employeeID = Range("A1").Value
>
> > > > 'you could also use this just remove the '
> > > > 'employeeID = InputBox("Select employee number", "Get Employee Number",
> > > > "<Employee Number>")
>
> > > > 'Change data source path to the path of you database
> > > > strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
> > > > & "Data Source=C:\PathTO\MyDataBase\" _
> > > > & "Employee.mdb;Persist Security Info=False"
>
> > > > 'You will need to modify this section the Select , From and Where
> > > > 'you do not need to modify =" & employeeID & ")); " part of code
> > > > 'if i knew the table and fieds i could help a little more
>
> > > > strSQL1 = "SELECT Employee.NAME, tblEmployee .EMPLOYEE_ID " _
> > > > & "FROM tblEmployee " _
> > > > & "WHERE (((tblEmployee .EMPLOYEE_ID)=" & employeeID & ")); "
>
> > > > Set cnn = New ADODB.Connection
> > > > Set rs1 = New ADODB.Recordset
> > > > cnn.Open strConn
> > > > rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
> > > > Do While rs1.EOF = False
> > > > Range("B" & i) = rs1!Name
>
> > > > i = i + 1
> > > > rs1.MoveNext
> > > > Loop
> > > > rs1.Close
> > > > cnn.Close
> > > > End Sub
>
> > > > "Matt" wrote:
> > > > > I am looking to see if some one can give me some basic code, using
> > > > > ADO, that will query an Access database using a cell value as a
> > > > > variable and then returning a recordset value to a cell. i.e.
>
> > > > > cell A1= employeeNumber
> > > > > using employee number, query the tblEmployee and return the Employee
> > > > > name in B2
>
> > > > > any help is appreciated. If anyone know of a good tutorial site for
> > > > > doing this type of thing i would appreciate it.
>
> > > > > thanks- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Thanks! I am just trying to understand what i=i+1 is about. Is that
> > > there for the loop in case I am trying to pull more than the Name?
> > > ie, if I wanted ss# then it would be placed in b3...etc??- Hide quoted text -
>
> > - Show quoted text -
>
> thanks again for the explanation and your time!- Hide quoted text -
>
> - Show quoted text -

Everything worked great! Now I have kinda of taken this up a notch
into a differenent scenario. I want to get values from an Access
table into a list box or combo box. I have a jobNumber that a user
would input and that would need to return all of the possible JobAreas
it has into a Combo Box on my worksheet. Ideas?