Subscribe Bookmark RSS Feed

script for specifying DBQ

chang

Community Trekker

Joined:

Jul 11, 2012

Hi,

I am wondering how I could specify the source of the database so this, perhaps as a global statement, could be referred by each dt=. As you could see in the the following script, to get data from multiple Access tables I duplicate DBQ=.... for each dt=. The only difference is the "SELECT..." part. In SAS, I would write a macro variable (%let dir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\) to specify the location of the database. But I haven't learned how to do the similar in JMP. I have underlined the duplicated part of the first two dt=. My script is attached.

dt1=Open Database( "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

    "SELECT * FROM qryAFF14",    "qryAFF14", invisible);

dt2=open database( "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

    "SELECT * FROM qryALF0", "qryALF0", invisible);

dt3=Open Database( "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

    "SELECT treatment, bee, age, days_exp_cum, trips_exp_count FROM qryTrips_days_exp", "qryTrips_days_exp", invisible);

dt4=Open Database( "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

    "SELECT * FROM qryLeft_time_hour", "qryLeft_time_hour", invisible);

dt5=Open Database( "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

    "SELECT * FROM qryCombo_exp", "qryCombo_exp", invisible);

1 REPLY
pmroz

Super User

Joined:

Jun 23, 2011

Try this:

dbq_string = "DBQ=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis\Chang_bee_db.mdb;" ||

             "DefaultDir=E:\Now\(Project) RFID_r3_Homing_r1\Ben analysis;" ||

             "Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;" ||

             "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\chang_bee_db.dsn;" ||

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

dt1=Open Database(dbq_string,

    "SELECT * FROM qryAFF14", "qryAFF14", invisible);

dt2=open database(dbq_string,

    "SELECT * FROM qryALF0", "qryALF0", invisible);

dt3=Open Database(dbq_string,

    "SELECT treatment, bee, age, days_exp_cum, trips_exp_count FROM qryTrips_days_exp", "qryTrips_days_exp", invisible);

dt4=Open Database(dbq_string,

    "SELECT * FROM qryLeft_time_hour", "qryLeft_time_hour", invisible);

dt5=Open Database(dbq_string,

    "SELECT * FROM qryCombo_exp", "qryCombo_exp", invisible);