CLR - OLEDB Open() error

[stanl]

You would have thunk .NET would do well with database classes as db objects work well in COM. Seems I'm not the only one frustrated, but this WB code cannot even open a connection. I have attached the zipped access db used in the script.

Code: Winbatch

;Winbatch 2013 - CLR - OLEDB {baby steps}
;
;
;Stan Littlefield July 7, 2013
;////////////////////////////////////////////////////////////////////////////////////////////////////////
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB:';Connect Timeout=30' ;oProvider.ConnectionTimeout = 30 not needed
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
;got errors trying to set oProvider.ConnectionString so used this class
oBuilder = ObjectClrNew("System.Data.OleDb.OleDbConnectionStringBuilder")
oBuilder.ConnectionString = cConn
oConn = oBuilder.ConnectionString
message("debug",oConn)
;do far so good, but cannot open connection
;used either
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",oConn)
;or
;oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection")
;oProvider.ConnectionString = oConn
message("debug",oProvider.ConnectionString)
;invocation error here
oProvider.Open()
oTables = oProvider.GetSchema("Tables")

oTables = 0
oProvider.Dispose()
oProvider=0
Exit
 
 

[td]

I haven't taken the time to create a working version of your script but one very obvious problem is that there isn't a constructor for the OleDbConnection class that takes a OleDbConnectionStringBuilder class instance.  The OleDbConnection class has two constructors - a constructor with no parameters and one taking a string value. 

I highly recommend taking the time to review the MSDN documentation for a class before setting out to use it.  MSFT has done a good job of standardizing the documentation for FCL classes.  Class documentation is usually concise, informative and a quick read.  I only wish they had done so well with the documentation for some of their other products.

[stanl]

I highly recommend taking the time to review the MSDN documentation for a class before setting out to use it.

Well, had you really read my post in the script comments I noted that there were issues assigning the connectionstring directly to the connection class. In either case, the debug messages in the script will illustrate that the provider.connectionstring can be displayed .... just not implemented in the .open() method.
Any implication that the builder class has to be somehow magically connected to the connection class seems a big CROCK. I would think a connection string is just that and once assigned to an object should work.
My underlining concern is that while the Jet.4.0 provider is recognized by .NET - Ace isn't.
 

[td]

Well, had you really read my post in the script comments I noted that there were issues assigning the connectionstring directly to the connection class. In either case, the debug messages in the script will illustrate that the provider.connectionstring can be displayed .... just not implemented in the .open() method.

Silly me.  I happened to notice the line in passing while conducting some Sunday maintenance on the server and wildly assumed that you were correctly using your own variable naming convention.  My apologies. I should know better.

Any implication that the builder class has to be somehow magically connected to the connection class seems a big CROCK. I would think a connection string is just that and once assigned to an object should work.
My underlining concern is that while the Jet.4.0 provider is recognized by .NET - Ace isn't.

This works for me on Windows 8 with Office  2013.

Code: Winbatch

cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oBuilder = ObjectClrNew("System.Data.OleDb.OleDbConnectionStringBuilder")
oBuilder.ConnectionString = cConn
cConn2 = oBuilder.ConnectionString
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn2)
oProvider.Open()
oTables = oProvider.GetSchema("Tables")

oTables = 0
oProvider.Dispose()
oProvider=0
Exit
 

[td]

Of course, in the above example it would be simpler to pass the connection string directly to the OleDbConnection constructor and not bother with OleDbConnectionStringBuilder class.

[td]

Got the above with the 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':cACCDB:' connection string to work  on a Windows 7 machine with just the Access database engine for 2010 installed.

[stanl]

Got the above with the 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=':cACCDB:' connection string to work  on a Windows 7 machine with just the Access database engine for 2010 installed.

Yes. Your version of the script worked on my Win7 laptop with Office 2013. Funny that I was unable to set the ConnectionTimeout property. But now for the next step - display the oTables (I assume it is a DataTable).
Thanks