System Resource Exceeded while opening ADO Connection

Question

Consider the following codings..

conn = Sys.OleObject("ADODB.Connection");

conn.ConnectionString = "DRIVER=Microsoft Excel Driver (*.xls);DBQ=filePath\ fileName.xls;ReadOnly=1;";

conn.Open();

• When trying to execute the above commands, I got the error "[Microsoft][ODBC Excel Driver]System Resource Exceeded"..
• To avoid this, I changed virtual memory of paging file size for the particular drive from 256MB to 4096MB ( which was system managed earlier)
• Then I continued executing, It worked fine*..*

But Still now, am facing the same error once for atleast 20 times if execution happens.. Means that error occuring frequency is reduced but not the error occurance..

So Please help me on this... to solve this issue..

Any answer is highly appreciatable..

Regards,

Ram Bharath

Answer 1

As far as I can remember, Jet is installed with Windows on Windows XP and later, although it may be a different story for WIndows 64-bit.

You can get the ACE download here (this is the 2010 version): http://www.microsoft.com/en-us/download/details.aspx?id=13255

You should probably have a more general look around the downloads site for ACE- and Jet- related materials, updates and so on. The English language version is at http://www.microsoft.com/en-us/download/default.aspx

Answer 2

" the Excel ODBC driver also relies on the Jet/ACE IISAM, and it is possible to install the Jet/ACE stuffwithout installing either Access or Office." - could you please let us know what stuffs do we need to install to use JET

Answer 3

I get your point, but...

1. As far as I know, the Excel ODBC driver also relies on the Jet/ACE IISAM, and it is possible to install the Jet/ACE stuff without installing either Access or Office. The OLE DB provider will also open Excel files without having to have. Either Access or Excel on the system.
2. Trying alternative approaches is a troubleshooting step that should also feed back into the decision-making process. If the Excel OLE DB provider behaves the same way as the ODBC driver, OK, that does not tell you much. But if it "just works," you have to consider how much effort to put into making the ODBC driver work, and so on.

Answer 4

Hai Peter.. Very much thanks for your reply...

I need a clarification from you..

Actually Why we are using this type of connection (DRIVER=Microsoft Excel Driver (*.xls);) is the point that we have in our project.. We have many systems operating with diff OS and we are not going to install MSOffice package in all the systems.. We are going to give the datas in an excel sheet in an system and we just copy that into other systems( where the .xls file cant be opened as it is not installed in that machine).. So we have drive our project using the datas in excel in all the machines..

So we are going for ADO connection.. And ODBC connection(without DSN)

So when we are usinf this connection, it can read the datas in an excel file even if it is not installed in a machine.. That makes the sense here.. 

And For your information, we are not going to give datas in MSAccess(if we are going to use that, then we have to install MSOffice package) and since we are not givong datas through MSAccess, we cant use Provider=Microsoft.ACE.OLEDB.12.0; 

And if we are going to use like Provider=Microsoft.Jet.OLEDB.4.0 , then we have to install MSoffice package in all systems and also ISAM i suppose..

I think that now you got some idea on what point am trying to convey..

We have to create an excel file in an system where MSOffice is installed.. Then we have to test some of the systems according to the datas specified in the excel file.. So we are going to copy the excel file in all othe systems where MSoffice package is not installed.. And now we are going to drive the testing process by reading the datas in excel file in systems where MSOffice package is not installed.. So we are going for 'DRIVER=Microsoft Excel Driver (*.xls);DBQ=filePath\ fileName.xls'

The problem we are facing is not regarding the connection.. It is connecting well and doing well.. But after sometimes . when we are opening a connection to an excel file and driving a test and closing the connection, and after sometime we will open the same/other connection to open the same/other excel file and driving a test and will close the connection.. when we are doing it again and again, at some point, it throws an error [Microsoft][ODBC Excel Driver]System Resource Exceeded"..To avoid this, I changed virtual memory of paging file size for the particular drive from 256MB to 4096MB ( which was system managed earlier).. Then I continued executing, It worked fine..

But Still now, am facing the same error once for atleast 20 times if execution happens.. Means that error occuring frequency is reduced but not the error occurance..

Answer 5

Does it make any difference if you connect using the Jet/ACE OLE DB provider instead of going through the OLE DB provider for ODBC data sources (which is what you are doing) ?

e.g. instead of **DRIVER=Microsoft Excel Driver (*.xls);**you would need

Provider=Microsoft.Jet.OLEDB.4.0;

or

Provider=Microsoft.ACE.OLEDB.12.0;

and you would also need to change other parts of your connection string (I do not have th details to hand, sorry).****