How to Disconnect from a database via script?

[#1]

Hello,

I am using the File>Database>Query Builder method to connect to an excel file and create a table. I am able to close the table with jmp script but the connection remains active and locks up the excel file for other users. How can I disconnect via jmp script? I can manually go through File>Database>Open>Disconnect but I would like to do this automatically in the running script.

Here is the query code:

RedBinMain = New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
	),
	QueryName( "RedBinMain_beta" ),
	Select(
		Column( "RBNbr", "t1" ),
		Column( "DMR", "t1" ),
		Column( "Date_Entered", "t1" ),
		Column( "Initiator", "t1" ),
		Column( "Location", "t1" ),
		Column( "AssignedPerson", "t1" ),
		Column( "Problem", "t1" ),
		Column( "USI_PN", "t1" ),
		Column( "Customer", "t1" ),
		Column( "Disposition", "t1" ),
		Column( "LastUpdatedDate", "t1" ),
		Column( "Cause", "t1" ),
		Column( "Classification", "t1" ),
		Column( "Defect_Code", "t1" ),
		Column( "BrokenSubMenu", "t1"),
		Column( "BrokenSubMenu_1", "t1"),
		Column( "Dimensional_Code", "t1" ),
		Column( "Notes", "t1" ),
		Column( "Assignable_WorkCell", "t1" ),
		Column( "DispositionerInvestigator", "t1" ),
		Column( "ScrappedQuantity", "t1" ),
		Column( "PreEtch", "t1" ),
		Column( "PostEtch", "t1" )
	),
	From( Table( "RedBinMain_beta", Alias( "t1" ) ) )
) << Run Foreground;

I tried the below code with no luck

close database connection(RedBinMain); 

What am I missing?

Here is where the connection is shown as "connected" using the ODBC driver "Excel 12.0"

[#2 · Accepted solution]

The key is in naming the database connection separately from the query. In your example above, RedBinMain is for the query, not the connection. Example below that should make the previous sentence actually make sense:

dbc = Create Database Connection(
	"ODBC:DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
);

//your script here: RedBinMain = New SQL Query ........ << Run Foreground

Close Database Connection (dbc);

[#3]

Thank You! That makes sense and works now although I had a ODBC error at first until I removed the "ODBC:" before the DSN= statement.

I inserted "dbc" as the connection variable in the query as well in case anyone else reads this. Here is the new working code for reference.

dbc = Create Database Connection(
		"DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
	);

RedBinMain = New SQL Query(
	Version( 130 ),
	Connection(dbc),
	QueryName( "RedBinMain_beta" ),
	Select(
		Column( "RBNbr", "t1" ),
		Column( "DMR", "t1" ),
		Column( "Date_Entered", "t1" ),
		Column( "Initiator", "t1" ),
		Column( "Location", "t1" ),
		Column( "AssignedPerson", "t1" ),
		Column( "Problem", "t1" ),
		Column( "USI_PN", "t1" ),
		Column( "Customer", "t1" ),
		Column( "Disposition", "t1" ),
		Column( "LastUpdatedDate", "t1" ),
		Column( "Cause", "t1" ),
		Column( "Classification", "t1" ),
		Column( "Defect_Code", "t1" ),
		Column( "BrokenSubMenu", "t1"),
		Column( "BrokenSubMenu_1", "t1"),
		Column( "Dimensional_Code", "t1" ),
		Column( "Notes", "t1" ),
		Column( "Assignable_WorkCell", "t1" ),
		Column( "DispositionerInvestigator", "t1" ),
		Column( "ScrappedQuantity", "t1" ),
		Column( "PreEtch", "t1" ),
		Column( "PostEtch", "t1" )
	),
	From( Table( "RedBinMain_beta", Alias( "t1" ) ) )
) << Run Foreground;

Close Database Connection (dbc);