I have a Task which id like you to ponder and see if any of you know how to
accomplish it.

As we know whilst in Excel we can lookup information from another sheet or
Wookbook by using the Vlookup or Hlookup functions.

The task i have been given is to get data from an access database into a
excel document in the kinda way we do with Vlookup etc.

For Example, We input a Contract Number into a Cell and then the rest of the
information regarding that contract is automatically bought from an access
database to the excel document.

Someone else may have an easier way, but if I were doing it, I'd use VBA Code
and set up a Tools | References reference to the Access object library, then
in the code I'd create an instance of Access and set it to the database file,
create a recordset object to receive the results of an SQL statement that
would be built in the VBA code and executed against the database using the
Contract Number that had been entered, then simply transfer the data
retrieved into the Excel worksheet.

You can 'cheat' a little and use Access itself to create a query that would
retrieve the data you need based on a specific contract number and examine
the query in SQL View. That gives you a 'template' for the SQL string to be
built in your Excel VBA code. Lets say your SQL view of the query in Access
shows this:
SELECT tblContracts.* FROM tblContracts WHERE
(((tblContracts.ContractNumber) = 'ABC 24165'));

Then you could build a string in VB code as
strSQL = "SELECT tblContracts.* FROM tblContracts WHERE
(((tblContracts.ContractNumber) = '" & strContractNumber & "'));"

where strContractNumber contains the contract number from your Excel sheet.

Is that enough of a start, or do you need more detail?

SELECT tbl_Addresses.*
FROM tbl_Addresses
WHERE (((tbl_Addresses.Address_ID)=124));

"Alistaire Green" wrote:

I have a Task which id like you to ponder and see if any of you know how to
accomplish it.

As we know whilst in Excel we can lookup information from another sheet or
Wookbook by using the Vlookup or Hlookup functions.

The task i have been given is to get data from an access database into a
excel document in the kinda way we do with Vlookup etc.

For Example, We input a Contract Number into a Cell and then the rest of the
information regarding that contract is automatically bought from an access
database to the excel document.

hi
microsoft query might be your best bet.
on the tool bar....
dataimport external datanew database queryfollow the wizard.
select ms assess db click ok. navagate to your access db.
select your table then the fields you want
on the last screen of the query wizard(the one with the finish button)
select the view data or edit query option then click finish
when the query form comes up, on the tool bar select criteriaadd criteria.
for value enter [Enter a contract number] or what ever you want the prompt
to be.
to refresh the query, add the exteral data menu and click the refresh icon.
looks like an exclamation point.
each time you refresh, MSQ will prompt you for a contrat number then slap
you data for that number on the sheet.

Regards
FSt1

"Alistaire Green" wrote:

I have a Task which id like you to ponder and see if any of you know how to
accomplish it.

As we know whilst in Excel we can lookup information from another sheet or
Wookbook by using the Vlookup or Hlookup functions.

The task i have been given is to get data from an access database into a
excel document in the kinda way we do with Vlookup etc.

For Example, We input a Contract Number into a Cell and then the rest of the
information regarding that contract is automatically bought from an access
database to the excel document.

Thanks Jlathem,

I uderstand the code you have typed, but im a little confused how we can get
other cells in a excel sheet to populate accourding to the Contract Number is
in the Cell.

Eg

Cell A1 is where you type the contract Number.

Then from putting the Contract Number in cell A1 all the other cells which
automatically populate.

Not know if im onto a loose end but if there is further help you can give fr
me to get my head wrong this i would appreciate it :)

Take a look at the bottom of the page at Nick Hodge's site. He shows how to
do pretty much exactly waht you want, using parameterized queries

http://www.nickhodge.co.uk/gui/datam...taexamples.htm


"Alistaire Green" wrote:

I have a Task which id like you to ponder and see if any of you know how to
accomplish it.

As we know whilst in Excel we can lookup information from another sheet or
Wookbook by using the Vlookup or Hlookup functions.

The task i have been given is to get data from an access database into a
excel document in the kinda way we do with Vlookup etc.

For Example, We input a Contract Number into a Cell and then the rest of the
information regarding that contract is automatically bought from an access
database to the excel document.

See also this page
http://www.rondebruin.nl/accessexcel.htm

And this one
http://www.erlandsendata.no/english/...php?t=envbadac


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Alistaire Green" wrote in message
...
I have a Task which id like you to ponder and see if any of you know how to
accomplish it.

As we know whilst in Excel we can lookup information from another sheet or
Wookbook by using the Vlookup or Hlookup functions.

The task i have been given is to get data from an access database into a
excel document in the kinda way we do with Vlookup etc.

For Example, We input a Contract Number into a Cell and then the rest of the
information regarding that contract is automatically bought from an access
database to the excel document.

Look at the links that Duke Carey and Ron de Bruin have provided - they
should help some. In the end, you're going to return a recordset that
contains the information you want. I presume it will be a 1-record-deep
recordset pertaining to the specific contract number you provided. That
recordset is going to be just like the table it came from: have fields of the
same name. You then take each field and put it into a cell. Assuming that
you've got the cell with the contract ID in it selected in a worksheet and
you want to put the fields of data in the same row, you could use something
like:

With rstReturnedRecord
ActiveCell.Offset(0, 1) = !ContractManager
ActiveCell.Offset(0, 2) = !ContractValue
ActiveCell.Offset(0, 3) = !StaffSize
ActiveCell.Offset(0, 4) = !LastInvoice
End With
to move the individual elements from the returned recordset
(rstReturnedRecord) onto the sheet.

"Alistaire Green" wrote:

Thanks Jlathem,

I uderstand the code you have typed, but im a little confused how we can get
other cells in a excel sheet to populate accourding to the Contract Number is
in the Cell.

Eg

Cell A1 is where you type the contract Number.

Then from putting the Contract Number in cell A1 all the other cells which
automatically populate.

Not know if im onto a loose end but if there is further help you can give fr
me to get my head wrong this i would appreciate it :)

Allistaire,
I hate leaving people hanging - but I still recommend reading the
information provided by others in other posts - much to learn.

In the meantime, here's a fully functioning Excel macro that I built as an
example for you. Hopefully you can follow along and see what's needed and
how things would be done. Obviously the path to the .mdb file, the table
name and the fields of the table will different, but the process and logic is
sound.

Sub ConnectToDatabase()
'uses DAO datasets vs ADO
'needs Tools | References to:
' MS Office xx Object Library
' Microsoft DAO 3.6 Object Library (or 3.51)
'
'to set up the references, open the VB Editor [Alt]+[F11] and
'choose Tools then References from the VB Editor's menu.
'
'change these constants as required
'full, well-formed path to the database, can use network type path
Const PathToDatabase = "G:\Contracts\Contracts.mdb"
Const tableName = "tblContracts"

Dim wkSpace As DAO.Workspace
Dim dbObject As DAO.Database
Dim anyTable As DAO.Recordset
Dim sqlStatement As String

Dim anyRange As Range
Dim contractID As String
Dim LC As Long ' loop counter

'presume Contract ID to get info about is in on Sheet1, cell A1 and
'we want to put returned data in columns B, C, D, etc
Set anyRange = Worksheets("Sheet1").Range("A1")
If IsEmpty(anyRange) Then
MsgBox "Please Provide a Contract ID", vbOKOnly, "No Contract ID"
anyRange.Select
Set anyRange = Nothing 'release resources
Exit Sub
End If
contractID = anyRange.Value

'seem ready to go, give it a try
Set wkSpace = DAO.CreateWorkspace("myWorkspace", "admin", "")
'if the database requires userID and password to open, you'll
'need to provide those as additional parameters in next command.
Set dbObject = wkSpace.OpenDatabase(PathToDatabase)

'build an SQL statement to include the contractID for retrieval
sqlStatement = "SELECT tblContracts.* FROM tblContracts WHERE " & _
"(((tblContracts.[Contract ID])='" & contractID & "'));"

Set anyTable = dbObject.OpenRecordset(sqlStatement)
'the returned records, if any, are now in anyTable
On Error GoTo ErrorDuringRetrieval
anyTable.MoveLast
anyTable.MoveFirst ' gives accurate recordcount
On Error GoTo 0 ' clear error trapping
'move our individual recordset fields into row below the Contract ID entry
at A1
For LC = 1 To anyTable.RecordCount
'we could do this with 2 loops, but we'll keep it straight-forward
With anyTable
anyRange.Offset(LC, 0) = ![Contract ID] ' field name
anyRange.Offset(LC, 1) = ![ContractName] ' field name
anyRange.Offset(LC, 2) = ![Contract Manager] ' field name
anyRange.Offset(LC, 3) = ![ContractValue] ' field name
anyRange.Offset(LC, 4) = ![StaffSize] ' field name
End With
Next
ExitConnToDB:
On Error Resume Next ' just grease on through
anyTable.Close
Set anyTable = Nothing
dbObject.Close
Set dbObject = Nothing
wkSpace.Close
Set wkSpace = Nothing
Set anyRange = Nothing
On Error GoTo 0 ' clear any error trapping still in effect
Exit Sub ' stay out of error handling territory

ErrorDuringRetrieval:
If Err.Number = 3021 Then
'simply no records returned
MsgBox "No records matching the entered Contract ID found.", _
vbOKOnly, "No Match"
Else
'something probably unexpected
MsgBox "Error: " & Err.Number & vbCrLf & Err.desctiption, _
vbOKOnly, "Error Encountered"
End If
Resume ExitConnToDB

End Sub


"Alistaire Green" wrote:

Thanks Jlathem,

I uderstand the code you have typed, but im a little confused how we can get
other cells in a excel sheet to populate accourding to the Contract Number is
in the Cell.

Eg

Cell A1 is where you type the contract Number.

Then from putting the Contract Number in cell A1 all the other cells which
automatically populate.

Not know if im onto a loose end but if there is further help you can give fr
me to get my head wrong this i would appreciate it :)

Alistaire (see if I can't spell it properly this time, sorry...)
Watch for extra line wraps in the code above creating errors in the code. I
see one at a comment that starts with 'move our individual... and the editor
has wrapped two words onto the next line - will create an error if you
copy|paste the code.

Also, early on I mentioned putting data on same row in adjacent cells, but
actual code, with correct comment, places retrieved values starting in A2.

"JLatham" wrote:

Allistaire,
I hate leaving people hanging - but I still recommend reading the
information provided by others in other posts - much to learn.

In the meantime, here's a fully functioning Excel macro that I built as an
example for you. Hopefully you can follow along and see what's needed and
how things would be done. Obviously the path to the .mdb file, the table
name and the fields of the table will different, but the process and logic is
sound.

Sub ConnectToDatabase()
'uses DAO datasets vs ADO
'needs Tools | References to:
' MS Office xx Object Library
' Microsoft DAO 3.6 Object Library (or 3.51)
'
'to set up the references, open the VB Editor [Alt]+[F11] and
'choose Tools then References from the VB Editor's menu.
'
'change these constants as required
'full, well-formed path to the database, can use network type path
Const PathToDatabase = "G:\Contracts\Contracts.mdb"
Const tableName = "tblContracts"

Dim wkSpace As DAO.Workspace
Dim dbObject As DAO.Database
Dim anyTable As DAO.Recordset
Dim sqlStatement As String

Dim anyRange As Range
Dim contractID As String
Dim LC As Long ' loop counter

'presume Contract ID to get info about is in on Sheet1, cell A1 and
'we want to put returned data in columns B, C, D, etc
Set anyRange = Worksheets("Sheet1").Range("A1")
If IsEmpty(anyRange) Then
MsgBox "Please Provide a Contract ID", vbOKOnly, "No Contract ID"
anyRange.Select
Set anyRange = Nothing 'release resources
Exit Sub
End If
contractID = anyRange.Value

'seem ready to go, give it a try
Set wkSpace = DAO.CreateWorkspace("myWorkspace", "admin", "")
'if the database requires userID and password to open, you'll
'need to provide those as additional parameters in next command.
Set dbObject = wkSpace.OpenDatabase(PathToDatabase)

'build an SQL statement to include the contractID for retrieval
sqlStatement = "SELECT tblContracts.* FROM tblContracts WHERE " & _
"(((tblContracts.[Contract ID])='" & contractID & "'));"

Set anyTable = dbObject.OpenRecordset(sqlStatement)
'the returned records, if any, are now in anyTable
On Error GoTo ErrorDuringRetrieval
anyTable.MoveLast
anyTable.MoveFirst ' gives accurate recordcount
On Error GoTo 0 ' clear error trapping
'move our individual recordset fields into row below the Contract ID entry
at A1
For LC = 1 To anyTable.RecordCount
'we could do this with 2 loops, but we'll keep it straight-forward
With anyTable
anyRange.Offset(LC, 0) = ![Contract ID] ' field name
anyRange.Offset(LC, 1) = ![ContractName] ' field name
anyRange.Offset(LC, 2) = ![Contract Manager] ' field name
anyRange.Offset(LC, 3) = ![ContractValue] ' field name
anyRange.Offset(LC, 4) = ![StaffSize] ' field name
End With
Next
ExitConnToDB:
On Error Resume Next ' just grease on through
anyTable.Close
Set anyTable = Nothing
dbObject.Close
Set dbObject = Nothing
wkSpace.Close
Set wkSpace = Nothing
Set anyRange = Nothing
On Error GoTo 0 ' clear any error trapping still in effect
Exit Sub ' stay out of error handling territory

ErrorDuringRetrieval:
If Err.Number = 3021 Then
'simply no records returned
MsgBox "No records matching the entered Contract ID found.", _
vbOKOnly, "No Match"
Else
'something probably unexpected
MsgBox "Error: " & Err.Number & vbCrLf & Err.desctiption, _
vbOKOnly, "Error Encountered"
End If
Resume ExitConnToDB

End Sub


"Alistaire Green" wrote:

Thanks Jlathem,

I uderstand the code you have typed, but im a little confused how we can get
other cells in a excel sheet to populate accourding to the Contract Number is
in the Cell.

Eg

Cell A1 is where you type the contract Number.

Then from putting the Contract Number in cell A1 all the other cells which
automatically populate.

Not know if im onto a loose end but if there is further help you can give fr
me to get my head wrong this i would appreciate it :)