How to pull data from Access from within Excel?

[Tech.Zoan]

I have an Access database I use to create a job quotation # and assign
a project name to. I also use Excel to keep track of the hours worked
on each project.

What I'd like to do is create an employee time sheet in Excel and just
enter his daily hours along with the job quotation # and have Excel
automatically pull the job name from the Access quotation database.

Can anyone help me with a sample or a direct me to a site that will
show me how I can do this... I'm not an expert in either software but
if i see a sample, i usually can modify it for my use. Thanks folks!

 

[Guest]

Excel has a data link wizard.

Go to Data->Import External Data->New Database Query

It should walk you through the process.

 

[Kerry]

Try something along these lines:

Rem Look up a category code from a database
Dim wrkJet As Workspace
Dim dbsDatabase As DAO.Database
Dim rstRecordSet As DAO.Recordset
Dim strSQL As String

Rem Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Rem Set database to Database to get data from
Set dbsDatabase= wrkJet.OpenDatabase("C:\Database.mdb")

Rem set recordset to What data want
Let strSQL = "Select [Column] From

Where [Column]=1"
Set rstRecordSet = rstRecordSet.OpenRecordset(strSQL,
dbOpenSnapshot)
Rem Work with data
If rstRecordSet.Fields("Column") = 0 Then
Rem Do stuff
End If

Rem Close Database
Set rstRecordSet = Nothing
dbsDatabase.Close
wrkJet.Close

 

[Tech.Zoan]

Thanks Kerry, but that is a little too advance for me :-(

 

[Tech.Zoan]

I did see that Wizard and I did manage to create a link from within my
Excel page to my Access database, however, it inputed all records,
though i was able to limit it the fields i wanted... however all
records showed up in my spreadsheet... i want just the one record i
specify in a cell.

I'll try it using the Wizard... maybe it will give me more options to
select the one record and fields i want. Thanks.

 

[Guest]

Did you specify which project # to search for, or the cell containing the
project #, when you built your query or simply pull a range?

It will only return as many matches as it find, and assuming your project
numbers are unique.. that should only be 1.

 

[Tech.Zoan]

I'm not having much success with this. All I seem to be able to get
is have the whole database (though i was able to limit which fields
would show) display in my spreadsheet.

Maybe someone could walk me step by step on how to do the following
since it's doesn't appear to be a long process to begin with.

I have an Access database called Quotations.mdb which includes the
following fields... Quotation # and Project Name.

Now in Excel, I want to create an employee timesheet where I can just
enter the Quotation # in a cell and it will automatically retrieve the
Project Name from the database and input it in a specified cell.

Example of timesheet:

Employee Name:

Monday's hours: Quote
#: Project Name:
Tuesday's hours: Quote
#: Project Name:

So the employee may work at different job sites during the week. Let's
say that the Quote # will be entered into cell D5 and D6.... etc.. and
the Project Name is to be retrieved into cell F5 and F6... etc...

With that... what would be the step by step instructions... example..
Data -> Import External Data -> New Database Query -> MS Database
Access -> OK -> etc...

Thank you guys for taking the time to help me out here... very much
appreciated!

 

[Guest]

If you have your Access file open, close it. Click in the cell you want to
start the query results in, then select Data->Import External Data->New
Database Query, Select Microsoft Access Database and browse for your database.

You should then be shown the “choose columns†dialog, Chose your 2 columns
and click next. Then Select your ID column in the filter data box, select
“equals to†and then pick any value you want ( we’ll make it reference a cell
later ). Click next. Then click next again, as there is no reason to sort a
query that returns a single record.

Next select view data or edit query in Microsoft Query. A window should pop
up that looks a lot like Microsoft Access’s query builder. Look for where
the value you chose to filter by is, and replace it with [pID], or some other
name that isn’t a field name in your table ( but keep the brackets in place
). You will be prompted for a value, enter anything there.. it’s only used
to return results from the current query view.

Select File->Return Data to Microsoft Office Excel. You will then get an
Import Data form, click on the parameters button. Highlight the pID ( or
whatever you used ), click the “Get the value from the following cellâ€
option, enter or browse for your cell reference ( where you want to enter a
value to update the query ). Check the refresh automatically when cell value
changes box and click ok.. click ok one more time to finish.

 

[Tech.Zoan]

Thanks Lance... I'm almost there! lol

But I'm having difficulties in making it show the way i want it.
Right now I can enter a job # in a cell and it will return the job #
and project name in two cells side by side but it also shows the field
names above each cells.

What I want is to type in the job # in one cell and have the
corresponding project name appear in a specified cell.. not
necessarily the one beside it and i do not want the field names to
show on my spreadsheet.

Furthermore... I need to have at least 10 queries or more on the same
timesheet. Since the same employee might work at 3 different job
sites on the same day, i need to be able to go one cell below and
enter a new job # and have the project name appear below the other job
name... and repeat this process until all of his hours are entered.

The problem right now seems to be that the input cell has to be
different then where the result is displayed because it is returning
the job# as well as the project name but I don't need the job # to be
returned from the database since I already provide it myself on the
timesheet.

I know i'm a pain in the ass, Lance, but i'm almost there if you can
help me out with this last bit... thanks!