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

[tamilarasu]

• How exactly you are placing the variable in the script.

    Added a new variable in input box property like below.

• What exactly you are pasting in the variable.

As I said, the variable taking the input from the input box (Path).

• And then, what excatly the error message you get.

I got error message like "Could not find file. 'C:\Users\Tamilarasu.Nagaraj\Desktop\QV\$(vFile).mdb'

[tamilarasu]

Amit: Yes

[jonathandienst]

Still getting the same error message.

It worked for me in a small test app. I suggest you post the code that you used to assign the connection string and the value of any variables that you tested.

[tamilarasu]

Could you see check the below files (Just a sample).

Thanks a lot.

[simospa · Accepted solution]

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.

[tamilarasu]

Well spotted. By the way I don't know how to check and find the mistake by using trace statement. Could you guide me.

Thanks for all whoever replied here. Have a nice day.

[simospa]

Good 🙂

If you use, at the end of your instruction, TRACE $(vPath) writes a content to log file.

I notice that more rows were written with timestamp on left, but I expected only 1.

Have a nice day.

S.