Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 24th, 2008, 01:41 PM
Registered User
Join Date: Sep 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel/Access interaction

I wasn't sure whether to post this under Access or Excel--hopefully the right people will find it.

I am trying to import data into Excel from an Access database. The database already contains various forms and queries, and I am not allowed to make any changes to the database.

There is one query in particular that I want to import into Excel; call it EdProfileAnchor. EdProfileAnchor looks like this:

SELECT Choose(Val(Forms!ddm.grpGeographicLevel),[district_nam],[region_nam],[Constituency],[Ward],[school_nam]) AS GroupLevel,
Choose(Val(Forms!ddm.grpGeographicLevel),gdb_school.district_num,gdb_school.region_num,gdb_school.constituency_num,gdb_school.ward_num,gdb_school.school_num) AS GroupLevelCode
FROM a_district 
    a_Ward INNER JOIN (
            (gdb_school INNER JOIN SchoolSet2 ON gdb_school.school_num = SchoolSet2.school_num) 
            INNER JOIN a_region ON gdb_school.region_num = a_region.region_num
        INNER JOIN a_Constituency ON gdb_school.constituency_num = a_Constituency.constituency_num
    ) ON a_Ward.ward_num = gdb_school.ward_num
) ON a_district.district_num = gdb_school.district_num
GROUP BY Choose(Val(Forms!ddm.grpGeographicLevel),[district_nam],[region_nam],[Constituency],[Ward],[school_nam]),
ORDER BY Choose(Val(Forms!ddm.grpGeographicLevel),gdb_school.district_num,gdb_school.region_num,gdb_school.constituency_num,gdb_school.ward_num,gdb_school.school_num);
There's some complicated nested join stuff going on here, but the key point, as far as I can tell, is that the columns GroupLevel and GroupLevelCode depend on these Choose functions. When trying to import this query into Excel, I get the message "Too few parameters. Expected 1." The parameter that it wants is, apparently, the value from Forms!ddm.grpGeographicLevel. How should I handle this? All of the information that I've found online has been about a different situation, for example, if the query had "WHERE GroupLevelCode = Val(Forms!ddm.grpGeographicLevel)" then I could pass a parameter to the query so that GroupLevelCode = 2. But in my case, I'm not trying to set the value of GroupLevelCode; I'm trying to designate which [i]column[i] (gdb_school.district_num, gdb_school.region_num, etc.) gets named "GroupLevelCode." I hope that made sense. Can anyone think of a way that this could be accomplished without making changes to the query in the database?

If there's a way to set the value of Forms!ddm.grpGeographicLevel using an Excel macro, I think that approach would help me the most. Ideally, I'd like to be able to do this without opening Access, but if it's not possible to "fake" set the value of Forms!ddm.grpGeographicLevel, I'm open to the option of setting it while Access is running.

Thank you in advance.

Similar Threads
Thread Thread Starter Forum Replies Last Post
.dll interaction with an application DavidStowell Visual Basic 2008 Essentials 2 March 3rd, 2008 04:33 PM
Converting excel data to Access using excel VBA ShaileshShinde VB Databases Basics 1 April 26th, 2006 07:57 AM
User interaction... Don Herman Access 3 January 31st, 2006 08:48 AM
Reagrding the mouse interaction in C# CsharpHelp C# 5 May 19th, 2005 03:58 PM
INTERACTION anil_soma ADO.NET 2 September 23rd, 2004 01:58 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.