Passing object properties to variables

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

One way you could accomplish this is by using a Script task.

You can access the properties of all the connections in your Connection Manager through the Dts ScriptObjectModel .

For example, the following code grabs the ConnectionString of my "currentFolder" connection object, and stores it in the "myConnectionString" variable.

PublicSub Main()

Dim value AsString

value = Dts.Connections("currentFolder").ConnectionString

Dts.Variables("myConnectionString").Value = value

'MsgBox(Dts.Variables("myConnectionString").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

Be sure to include your variable (ie. User::myConnectionString) in the ReadWriteVariables property of your script task.

Is this what you meant?

|||Yes, this is exactly what I meant. Thanks!

Passing dates to ServerFilter from Access

Hello.
I have a solution written in Access. In order to filter a
report a date is passed the the serverfilter property.
e.g. MyFilterDate='01/28/2004'
This works fine on one machine but on another machine I
get an overflow error due to the fact that date is passed
in American Format.
Both machines appear configured with the same regional
settings, the VBA references are the same and anything I
have thought of have been the same.
They are both connected to the same SQL server.
Can anyone help?
If you pass in the date as 'yyyymmdd' it will never get confused.
Andrew J. Kelly SQL MVP
"Scamps" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
> Hello.
> I have a solution written in Access. In order to filter a
> report a date is passed the the serverfilter property.
> e.g. MyFilterDate='01/28/2004'
> This works fine on one machine but on another machine I
> get an overflow error due to the fact that date is passed
> in American Format.
> Both machines appear configured with the same regional
> settings, the VBA references are the same and anything I
> have thought of have been the same.
> They are both connected to the same SQL server.
> Can anyone help?
|||Thank you very much.
Problem appears solved immediately.
>--Original Message--
>If you pass in the date as 'yyyymmdd' it will never get
confused.
>--
>Andrew J. Kelly SQL MVP
>
>"Scamps" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2dc5801c46a8a$2c5537f0$a501280a@.phx.gbl...
filter a[vbcol=seagreen]
passed[vbcol=seagreen]
I
>
>.
>

Pass Through query with property ReturnsRecords set to FALSE

Whenever I try to make a pass through query in Access 2002 for Oracle
sql server I get this fault
"The microsof Jet engine cannot find table or query" and then mentions
the connection string as table or query name.
The query is not supposed to return records, because it is DROP TABLE
<table_name>
When I run the syntax in Oracle SQL plus it works fine.
If I set the ReturnRecords property to TRUE it correctly says that the
query doesn't return records, but the query is correctly executed.
Here the problem is, it stops the Macro I use to run a whole serie of
pass throughs.
Anyone got a solution? I have the latest jet engine service pack
installed but no improvement.
DiPass-through queries pass SQL statements directly to the data source
-- they are not parsed by Access, Jet or ODBC. I'm not sure why you're
getting the Jet engine error message, or where the problem lies. You
might want to consider kicking off your pass-through queries from VBA
instead of from macros. That way you can get more information through
structured error handling, which macros lack. You can create or modfiy
pass-through queries by coding to the DAO.QueryDef object.
--Mary
On 24 Aug 2006 01:34:40 -0700, "Di" <dianne.timmerman@.gmail.com>
wrote:

>Whenever I try to make a pass through query in Access 2002 for Oracle
>sql server I get this fault
>"The microsof Jet engine cannot find table or query" and then mentions
>the connection string as table or query name.
>The query is not supposed to return records, because it is DROP TABLE
><table_name>
>When I run the syntax in Oracle SQL plus it works fine.
>If I set the ReturnRecords property to TRUE it correctly says that the
>query doesn't return records, but the query is correctly executed.
>Here the problem is, it stops the Macro I use to run a whole serie of
>pass throughs.
>Anyone got a solution? I have the latest jet engine service pack
>installed but no improvement.
>Di