Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
,
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);
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.
Tamil,
You need to establish a new connection string based on new location of your .accdb file.
Thanks,
AS
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.
Try without '=' before variable like:
...DBQ=$(vFILE);.....
Tresesco,
How do you define/input vFILE variable?
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.
Could you clarify these:
?
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.
Like this???
Thanks,
AS