Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tamilarasu
Champion
Champion

Set database path in a variable.?

hello-smiley.gif,

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);

Capture.PNG

1 Solution

Accepted Solutions
simospa
Partner - Specialist
Partner - Specialist

Hi,

I tried to write the connectionString on the same row, and it works

Set vConn = 'Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Extended Properties="DBQ=$(vPath);DefaultDir=C:\Users\Tamilarasu.Nagaraj\Desktop\QV\;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;FILEDSN=$(vPath).dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;";Initial Catalog=$(vPath)]';

You wrote the vConn in many rows, it seems it is considered as different statements...

Helps me use TRACE statement 🙂

Let us know.

S.

View solution in original post

16 Replies
amit_saini
Master III
Master III

Tamil,

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

Thanks,

AS

tamilarasu
Champion
Champion
Author

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.question.gif



tresesco
MVP
MVP

Try without '=' before variable like:

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

tamilarasu
Champion
Champion
Author

Tresesco,

Capture.PNG

tresesco
MVP
MVP

How do you define/input vFILE variable?

tamilarasu
Champion
Champion
Author

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
MVP
MVP

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
amit_saini
Master III
Master III

Like this???

Thanks,
AS