Code for getting Access recordset into Excel

[Matt]

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

 

[Dave Patrick]

'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]

'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

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!

 

[Dave Patrick]

You're welcome.

--

Regards,

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

 

[Mike]

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]

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








- 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??

 

[Mike]

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]

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






- Show quoted text -

thanks again for the explanation and your time!

 

[Matt]

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?