I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query and it seems to me that Excel is far easier to create graphs than Access.
When I use the Get External Data feature of Excel and the dialog box with the tables and queries appears, the query I need is not listed. Is this because the query has calculated fields or some other reason? I can find the underlying table but then the data is not filtered and of course, there are no calculated fields.
I believe connections can be made using vba but I have no idea of how to do this.
This is my sql for the query if that helps. - SELECT tblStaffPerformanceStats.SRID, tblStaffPerformanceStats.RYear, tblStaffPerformanceStats.StaffCode, tblStaffPerformanceStats.PercentageRate, tblStaffPerformanceStats.ChargeRate, tblStaffPerformanceStats.WkDays, tblStaffPerformanceStats.MaxBillableHrs, tblStaffPerformanceStats.ActualBillableRevenue, tblStaffPerformanceStats.ActualBillableHrs, tblStaffPerformanceStats.HrsWrittenOff, tblStaffPerformanceStats.HrsWrittenBack, tblStaffPerformanceStats.HrsNonAttendance, tblStaffPerformanceStats.HrsOfficeAdmin, tblStaffPerformanceStats.HrsProfessionalDev, tblStaffPerformanceStats.HrsFirmDev, [WkDays]*DLookUp("[DailyAttendHrs] ","[tblStaff]","[SCode] = '" & [Forms]![afmStaffReviewCentre]![cboLastname] & "'") AS MaxAvailableHrs, ([WkDays]*DLookUp("[DailyAttendHrs] ","[tblStaff]","[SCode] = '" & [Forms]![afmStaffReviewCentre]![cboLastname] & "'"))*[ChargeRate] AS [Max$], [MaxBillableHrs]*[PercentageRate] AS TargetedHrs, Round(([MaxBillableHrs]*[PercentageRate])*[ChargeRate],0) AS TargetedRevenue, [ActualBillableHrs]-[HrsWrittenOff]+[HrsWrittenBack] AS ActualRecoverableHrs, [HrsNonAttendance]*[ChargeRate] AS [Non-Attendance$], [HrsOfficeAdmin]*[ChargeRate] AS [OfficeAdmin$], [HrsProfessionalDev]*[ChargeRate] AS [ProfDev$], [HrsFirmDev]*[ChargeRate] AS [FirmDev$], [ActualBillableRevenue]+([HrsOfficeAdmin]*[ChargeRate])+([HrsFirmDev]*[ChargeRate])+([HrsProfessionalDev]*[ChargeRate]) AS Total, Format(DSum("[ActualBillableHrs]-[HrsWrittenOff]+[HrsWrittenBack]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS ActualHrsCumulative, Format(DSum("[TargetedHrs]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS TargetedlHrsCumulative, Format(DSum(" [ActualBillableRevenue]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS ActualFeesCumulative, Format(DSum(" [TargetedRevenue]","qryStaffPerformanceCalculator","[SRID]<=" & [SRID] & ""),"000.0") AS TargetedlFeesCumulative, [ActualBillableRevenue]+([ChargeRate]*[HrsNonAttendance])+([HrsOfficeAdmin]*[ChargeRate])+([ChargeRate]*[HrsProfessionalDev])+([HrsFirmDev]*[ChargeRate]) AS TotalAcual
-
FROM tblStaffPerformanceStats
-
WHERE (((tblStaffPerformanceStats.RYear) Between #7/1/2008# And #6/30/2009#) AND ((tblStaffPerformanceStats.StaffCode)=[Forms]![afmStaffReviewCentre]![cboLastname]))
-
ORDER BY tblStaffPerformanceStats.SRID;
NB: The date range is currently hard coded but eventually will be selectable when everything is working how I want it.
I would appreciate suggestions on how to set up a connection between excel and my access query and if vba is the way to go, some sample code I could adapt.
I should also mention that although I am using Access 2007 and Excel 2007, the end user will be using Access 2007 runtime and probably Excel 2003.
Thanks
15 5255
OK
I have found some code that may do the job but I am getting a Runtime Error 3061 Too few parameters Expected 1 message.
This is the code: - Private Sub Command3_Click()
-
'This code was originally written by Dev Ashish
-
'It is not to be altered or distributed,
-
'except as part of an application.
-
'You are free to use it in any application,
-
'provided the copyright notice is left unchanged.
-
'
-
'Code Courtesy of
-
'Dev Ashish
-
-
'Copy records to first 20000 rows
-
'in an existing Excel Workbook and worksheet
-
'
-
Dim objXL As Excel.Application
-
Dim objWkb As Excel.Workbook
-
Dim objSht As Excel.Worksheet
-
Dim db As Database
-
Dim rs As Recordset
-
Dim intLastCol As Integer
-
Const conMAX_ROWS = 20000
-
Const conSHT_NAME = "Sheet1"
-
Const conWKB_NAME = "C:\ExcelTest\book1.xlsx"
-
Set db = CurrentDb
-
Set objXL = New Excel.Application
-
Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)
-
With objXL
-
.Visible = True
-
Set objWkb = .Workbooks.Open(conWKB_NAME)
-
On Error Resume Next
-
Set objSht = objWkb.Worksheets(conSHT_NAME)
-
If Not Err.Number = 0 Then
-
Set objSht = objWkb.Worksheets.Add
-
objSht.Name = conSHT_NAME
-
End If
-
Err.Clear
-
On Error GoTo 0
-
intLastCol = objSht.UsedRange.Columns.Count
-
With objSht
-
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
-
intLastCol)).ClearContents
-
.Range(.Cells(1, 1), _
-
.Cells(1, rs.Fields.Count)).Font.Bold = True
-
.Range("A2").CopyFromRecordset rs
-
End With
-
End With
-
Set objSht = Nothing
-
Set objWkb = Nothing
-
Set objXL = Nothing
-
Set rs = Nothing
-
Set db = Nothing
-
End Sub
Edit: thought I should add the error is showing on line 25.
Hi. You will need to specify which kind of recordset is involved - DAO or ADO. Either type will work with the Excel CopyFromRecords et method, but the parameter error arises when Access itself becomes confused over which you are using.
Change the DIM for variable RS to
When you change this line check by compiling the code whether or not the DAO object library is already referenced. If on compilation it generates an error you will need to add the reference by selecting tools, references from the VB editor, scrolling to the Microsoft object list and ticking Microsoft DAO 3.6 (or later).
-Stewart
Thanks Stewart
I tried that but still get the error. I also tried to tick the Microsoft DAO 3.6 Object Library reference as you suggested but I get a message saying it conflicts with existing Module, project or object Library.
Under references I have the following ticked:
Visual Basic for applications
Microsoft Acces 12 Object Library
OLE automation
Microsoft Office 12 Access database engine object library
Microsoft Forms 2 Object library
-
# Set db = CurrentDb
-
# Set objXL = New Excel.Application
-
# Set rs = db.OpenRecordset("qryStaffPerformanceCalculator", dbOpenSnapshot)
-
-
Most likely it is the code above line 25.
You might try: Set db = CurrentDb()
Also you could try Set objXL = Excel.Applicati on
Hi OzNet. Dev Ashish developed many excellent routines which worked perfectly when they were published. I see no error in the code before or after the line quoted.
If you do not have a reference to an earlier DAO object library set - and you do not appear to have one set from what you tell us - it is difficult to see where the current conflict arises. DAO (Data Access Objects) is what Access itself uses internally, but in Access 2003 the DAO object library was not referenced as the default for VBA. This results in conflicts that did not arise in versions prior to A2003 when VBA code refers to recordset objects which exist in more than one library. The OpenRecordset line quoted should work fine as long as the recordset name shown is correct. In my experience the parameter error you are encountering arises solely from a reference conflict which should be resolvable.
If, by the way, you do already have a reference to an earlier DAO object library set you should untick this and tick the latest one in the list.
@AXESMI59 - CurrentDB is an internal object variable referring to the current database object - it is not a function, so referring to it as CurrentDB() would be in error.
-Stewart
Thanks Stewart
I am using Access 2007 if that make a difference to issues you mention above.
The references listed above are ticked plus I realise now I also have the Microsoft Excel 12 Object library ticked as the code above apparently needs this to work. (I have 6 references ticked).
I assume the list is the same regardless of which sub/module etc I have open for editing.
In other subs I have lines such as: - Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Set db = CurrentDb()
and - Set rst = db.OpenRecordset(strSQL)
These work fine.
I am not sure what else to check
Hi OzNet. I don't use A2007 myself, but VBA code is I understand backwardly compatible in terms of use of DAO objects. I'm sorry that I cannot be of more assistance - I will ask colleagues who may have more experience of A2007 if they can help.
-Stewart
mshmyob 904
Recognized Expert Contributor
Have you tried just using the transferspreads heet method (the tablename parameter can be replaced with a query). It would just require a single line of code.
cheers,
Mshmyob's suggestion is definitely worth trying.
On the original question my colleague PuppydogBuddy also suggests changing the line preceding the DIM of RS to qualify the database type as DAO -
Dim db As DAO.Database
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mark T. |
last post by:
Hi,
I've got a simple DB (Office Win XP Professional).
Basically, it's like a check book register - deposit, deduction and running
sum.
I'd like to somehow link it to an Excel spreadsheet that will do the
calculations and then keep the data updated in the form
and report in the DB. Apparently this isn't done in Access because
the "cell" where you enter the data is actually a field. I have a tabular
form that looks just like a...
|
by: AOstarello |
last post by:
Hi all,
I'm trying to link to some OLAP cubes that were created using the MS
SQL Server Analysis Tools. The cubes reside on an Analysis Server.
I'm trying to find a simple way for users to query the cubes without a
lot of programming overhead.
I can easily get the results I want by using MS Excel 2003. Through
the menus, I choose: Data > Import External Data > New Database Query.
Then I can select the OLAP Cubes tab, create a data...
|
by: chemdawg |
last post by:
Need help in linking access query to excel. The link I have now does
not updated my main table or reports. What am i missing here?
|
by: oyk |
last post by:
I hope someone could help me on this. I have linked an excel worksheet to
access but the some data shown in the access table has the #Num errors.
Presumably,
this must be because the data in the particular column in excel are of mixed
data-type. For example, there are numbers as well as texts like "abcd" etc.
in the same columns
I tried changing the cell format in excel to text but to no avail as the
problem still exist.
Really need...
|
by: jalmar |
last post by:
Hello again:
My second question is:
I am linking an excel spreadsheet into Access, I have it linked and at first had problems getting the information imported into Access-it wasn't importing-I had a msg in the populated fields which read #NUM- I was told to use quotations before and after the numbers in Excel and it would import correctly, which did happen but when I run my query off that linked table, it creates far too many...
| |
by: smaczylo |
last post by:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm having I'd be
most appreciative. The database is already constructed, I'm just
wanting to export the data to an excel file.
In short, I'm hoping to export two Tables (or queries...not sure which
to use - they both seem to have the same data) in...
|
by: Andrew W |
last post by:
Hi All,
I'm trying to link to data on a website with Access. When I try File/
Get External Data/ link tables/ HTML/, I get the following error
message:
Database or object is read-only.
I find this odd because I can get this data thru Excel's web query
function.
|
by: karthik28 |
last post by:
I tried linking an excel spreadsheet to an access database. It shows me a window with the data but the header row in missing. However, when I open the excel file, save it without making any changes and try linking it again, it works fine.
Please suggest a solution so that I dont have keep saving the excel file before linking it.
Thanks
KS
|
by: agarwasa2008 |
last post by:
Hi,
I have a perfect file called "Products.xls" that I link to my MS
Access 2003 database and everytthing looks good in that file.
When I view the same file after linking in my database the data values
of records in "tblProducts" are incorrect. For example the tblProducts
has a field called "PackageSize" as listed below. THe problem is that
it displays the text correctly but if the field has numbers only it
messes it up. I have set...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |