[AccessD] Storing variable names in a table

Hale, Jim Jim.Hale at FleetPride.com
Fri Jul 1 11:13:54 CDT 2005


Sad,
Thank you for the reply. I'm experimenting with your suggestion. I'll let
you know how it goes.
Jim Hale

-----Original Message-----
From: Sad Der [mailto:accessd666 at yahoo.com]
Sent: Thursday, June 30, 2005 12:47 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Storing variable names in a table


Jim,

I'm probably missing the big picture here but i'll
give it a try.
Suppose you have a table with the following fields:
Query (Text)
Parameter (Text)
ParameterType (Text)

It has the values:
Query Parameter                   ParameterType
A     strareacode                 String
A     intCo                       Integer
A     "AreaActSummaryACT"         String
A     "dept1"                     String
A     .Worksheets(y).Range("a11") Range

You could add all parameters and there types in an 
array
Something like this:
intCntr = Select count(*) from ParameterTable where
Query = "A"
dim avarParameter() as variant
redim avarparameter(1,intCntr)

So now you now how to convert the parameter types.

for lngArrayCntr = 0 to UBOUND(avarparameter)
 ConvertDataType(avarparameter(1,lngArrayCntr)
next lngArrayCntr

Did I mis it completely? :-)

HTH

SD
--- "Hale, Jim" <Jim.Hale at fleetpride.com> wrote:

> In a class I have created a method that reads query
> names from a table and
> opens the appropriate record sets. There may be any
> number of queries that
> are run by a single method call. For parameter
> queries life is more
> complicated. I created a method which loads the
> variables into a ParmArray
> and then sets the query parameters to the correct
> variable. The problem with
> this is I can only run one parameter query at a time
> from my method. a
> typical call looks like:
> 
> 'set parameter values
>  Call Xcel.QryParmValues(strareacode, intCo,
> "AreaActSummaryACT", "dept1",
> .Worksheets(y).Range("a11"))
> ' read query names from table and open the
> recorsets.
>     Xcel.MultiplePasteExcel (119)
> 
> 119 is the case number so the method knows which the
> query names to pull
> from a table. Ideally I would like to store all the
> parameters for all the
> queries in a table and extract them as needed. 
> "AreaActSummaryACT", and
> "dept1" are no problem since they are strings. What
> I don't know how to do
> is store variable names such as strareacode, intCo
> as strings in a table and
> then assign them the correct value from the
> procedure all this is running
> in. Worksheets(y).Range("a11")) will also be a
> challenge to store in a table
> (it picks up the parameter value from an Excel
> worksheet) but I may be able
> use eval() to create the correct value. If I can
> solve these problems I will
> be able to populate Excel workbooks with one method
> call since all the info
> needed to create recordsets with data to paste into
> worksheets will reside
> in a table. If I can't store the variable names in a
> table I quess I could
> expand the QryParmValues method to handle a multi
> dimensional array  but I
> am not at all sure how to do this either. Any help
> will be appreciated.
> 
> Jim Hale
> 
> 
> 
>
***********************************************************************
> The information transmitted is intended solely for
> the individual or
> entity to which it is addressed and may contain
> confidential and/or
> privileged material. Any review, retransmission,
> dissemination or
> other use of or taking action in reliance upon this
> information by
> persons or entities other than the intended
> recipient is prohibited.
> If you have received this email in error please
> contact the sender and
> delete the material from any computer. As a
> recipient of this email,
> you are responsible for screening its contents and
> the contents of any
> attachments for the presence of viruses. No
> liability is accepted for
> any damages caused by any virus transmitted by this
email.> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.


More information about the AccessD mailing list