Set database path in a variable.?

[tamilarasu]

,

I have a connection string to fetch data from access database. Now, I want to allow the user to change the path in front end, so i have created a variable (vFile) and stored the path into it. But it showing error whenever i try to reload. Can anyone point me where I am wrong.?

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;

Extended Properties="DBQ=$(=vFILE);DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV;

Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=$(=vFILE).dsn;

MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";

Initial Catalog=$(=vFILE)](XPassword is CDfSPWBOUTcCWRNNFTceJ);

[amit_saini]

Tamil,

You need to establish a new connection string based on new location of your .accdb file.

Thanks,

AS

[tamilarasu]

Hello Amit,

I just want to replace the path with a variable, so that the user can change it in front end. Below connection string is working perfectly, but I have hard code the path in the string.

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;

Extended Properties="DBQ=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb;DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV;

Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb.dsn;

MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";

Initial Catalog=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\PTV2.accdb] (XPassword is CDfSPWBOUTcCWRNNFTceJ);

I have replaced the above path by using variable. But I got error message at the time of reload. Can you help me to correct the syntax please.

[tresesco]

Try without '=' before variable like:

...DBQ=$(vFILE);.....

[tamilarasu]

Tresesco,

[tresesco]

How do you define/input vFILE variable?

[tamilarasu]

I have included a text box in the dashboard where the user can paste the path in that box. I am just assigning a variable to store it.

[tresesco]

Could you clarify these:

• How exactly you are placing the variable in the script
• What exactly you are pasting in the variable
• And then, what excatly the error message you get

?

[jonathandienst]

Doing a variable expansion inside the connection string appears not to work with or without the = sign (at least as of 11.20 SR9).

But you can replace the entire connection string like this:

Set vPath = 'ZZZZZZ\YYYYYYY';

Set vConn = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXXXXX;Data Source=$(vPath)';

OLEDB CONNECT TO [$(vConn)];

Adapt this logic to your situation.

[amit_saini]

Like this???

Thanks,
AS