Open an Password Protected Access-2000 Database (OLE DB)

Hi,
I have created reports for a password protected database using Crystal Reports-8 thru OLE DB Connection. Whenever calling the report from a VB Form(thru Crystal Report Active-x Control), it reports "Unable to logon to SQL Server".

But if I try same thru ODBC Connection thru a DSN, it works perfect.

Since, there is a need to change the name of the source database (during runtime, the report has to access data from different databases), I require the same thru an OLE DB Connection. OR there is any way to change the database name in an odbc dsn by modifying connection parameters?

I remain.

Thanks.If u r tring to open Access database which has password. to change the database at runtime u can use the following code in vb.My report using Direct Database file connection not oledb or others.

Report.Database.Tables.Item(1).Location = App.Path & "\" & dbName
Report.Database.Tables(1).SetSessionInfo "", Chr(10) & txtPass

if u have more then on table u can use loop.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=myDSNn

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.

open an ODBC connection to a DB and must use a connection that looks like this: "DSN=my

In my ssis package,

I have a DSN connection like this: "DSN=myDSNname". Which decide from i have to pull the data.

By using OLE DB Source Editor, I want to assign that ODBC Connection to it.

By data source Reader i can achive this but where i have to pass the hard-code SQL Query that i don't want.

i'm using the variable for dynamic SQL command.

Thanks.

Manoj

Manoj;

You can use an expression to build dinamically the SQL statement at run time. The explanation on this trhead is for an OLE DB Command transform; but the procedure is the same for a data reader:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005847&SiteID=1

I hope thsi helps

|||

This is not working in my case.

Lets assume i have a DSN defined in my system.

Now i want to to import export the data.

I have to use the DSN for connecting the Source server and i'm passing my TSQL in the variable.

and for destination i can use OLE-DB or else what ever be fastest way.

my problem is that how can i achive such functionality,

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

Thanks

Manoj

|||

Manoj Verma wrote:

I have to use ODBC for connecting the source server and passingt he variable for executing the TSQL statement.

So, what you need is to 'pass' a query to a Data reader component right?

may be I was not clear on my previous post. If you are using a ODBC source; yes you have to use Data Reader as your source component. But Data reader does not allow you to provide the SQL statement via variable; instead you can use an expression to build the query dinamically and overwrite the SQLCommand property of the data reader comoponent. The link I provided in my previous post explains the steps for that. The expression you need to build will have the same logic of the variable you currently have for the SQL query.

|||

thanks

your reply helps us alot.