Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO connection Open failure
Hi all. I've run into an ADO issue, here's what's happening: I'm using an Excel file on a shared drive to automate an Access database in the background. At some point in the code I'm using ADO to export an Access dataset. The problem occurs when the code attempts to open an ADO connection. Please note that this is most likely a compatibility issue; I get no errors when I run the code from my PC. ** The error occured when another user tried to execute the code. ** Here's the VBA in question: Dim strCon As String Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon The strAppAccFullName string variable is used for the full path name to the Access database on the shared drive; its value is: "\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb" The error message I'm getting is: Run-time error '-2147220999 (800401f9): Method 'Open' of object '_Connection' failed As for the settings etc.: The Excel file is using the following references: - Visual Basic for Applications - Microsoft Excel 11.0 Object Library - OLE Automation - Microsoft Office 11.0 Object Library - Microsoft Forms 2.0 Object Library - Microsoft Access 11.0 Object Library - Microsoft DAO 3.6 Object Library - Microsoft ActiveX Data Objects 2.8 Library On my PC (where the code runs fine), I'm running Office 2003 & Windows XP. My MDAC version is 2.81. On the PC where the error occurred, the user is running Office 2002 & Windows XP. The MDAC version on that PC must be 2.8xx, because I tried to install MDAC 2.8 from Microsoft and the installation failed (I was told the component is a part of Windows). The objective is to get the code to run without errors on the other user's PC (Office 2002). Any insights / suggestions would be greatly appreciated. Many thanks, Alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO connection Open failure
Does the user account that runs your code has read & write permission to the
network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. "alexhatzisavas" wrote in message ... Hi all. I've run into an ADO issue, here's what's happening: I'm using an Excel file on a shared drive to automate an Access database in the background. At some point in the code I'm using ADO to export an Access dataset. The problem occurs when the code attempts to open an ADO connection. Please note that this is most likely a compatibility issue; I get no errors when I run the code from my PC. ** The error occured when another user tried to execute the code. ** Here's the VBA in question: Dim strCon As String Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon The strAppAccFullName string variable is used for the full path name to the Access database on the shared drive; its value is: "\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb" The error message I'm getting is: Run-time error '-2147220999 (800401f9): Method 'Open' of object '_Connection' failed As for the settings etc.: The Excel file is using the following references: - Visual Basic for Applications - Microsoft Excel 11.0 Object Library - OLE Automation - Microsoft Office 11.0 Object Library - Microsoft Forms 2.0 Object Library - Microsoft Access 11.0 Object Library - Microsoft DAO 3.6 Object Library - Microsoft ActiveX Data Objects 2.8 Library On my PC (where the code runs fine), I'm running Office 2003 & Windows XP. My MDAC version is 2.81. On the PC where the error occurred, the user is running Office 2002 & Windows XP. The MDAC version on that PC must be 2.8xx, because I tried to install MDAC 2.8 from Microsoft and the installation failed (I was told the component is a part of Windows). The objective is to get the code to run without errors on the other user's PC (Office 2002). Any insights / suggestions would be greatly appreciated. Many thanks, Alex |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO connection Open failure
"Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Thanks Norman. Yes, the user has read/write permission to the shared drive. A series of Access queries are executed without problems, and the code only crashes when it reaches the point where ADO is used. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. I'm using DAO for some Access manipulatins (e.g. browsing through the TableDefs collection). The code has been executing fine on other PCs, so I don't think the issue is related to this. Besides, what's the point of having these libraries if you can't use combinations of them at the same time? I'm leaning toward a corrupted MDAC, as per the following resource: http://www.dbforums.com/archive/index.php/t-321180.html I should also note that the user's PC has AS400 Client Access installed. Any ideas? "Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. "alexhatzisavas" wrote in message ... Hi all. I've run into an ADO issue, here's what's happening: I'm using an Excel file on a shared drive to automate an Access database in the background. At some point in the code I'm using ADO to export an Access dataset. The problem occurs when the code attempts to open an ADO connection. Please note that this is most likely a compatibility issue; I get no errors when I run the code from my PC. ** The error occured when another user tried to execute the code. ** Here's the VBA in question: Dim strCon As String Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon The strAppAccFullName string variable is used for the full path name to the Access database on the shared drive; its value is: "\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb" The error message I'm getting is: Run-time error '-2147220999 (800401f9): Method 'Open' of object '_Connection' failed As for the settings etc.: The Excel file is using the following references: - Visual Basic for Applications - Microsoft Excel 11.0 Object Library - OLE Automation - Microsoft Office 11.0 Object Library - Microsoft Forms 2.0 Object Library - Microsoft Access 11.0 Object Library - Microsoft DAO 3.6 Object Library - Microsoft ActiveX Data Objects 2.8 Library On my PC (where the code runs fine), I'm running Office 2003 & Windows XP. My MDAC version is 2.81. On the PC where the error occurred, the user is running Office 2002 & Windows XP. The MDAC version on that PC must be 2.8xx, because I tried to install MDAC 2.8 from Microsoft and the installation failed (I was told the component is a part of Windows). The objective is to get the code to run without errors on the other user's PC (Office 2002). Any insights / suggestions would be greatly appreciated. Many thanks, Alex |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO connection Open failure
Since you use DAO, have you tried to use DAO to open the database/a
RecordSet? If the code is to only work with JET database (*.mdb) and since DAO is needed for schema data manipulating, I'd also use DAO for data accessing. DAO is better than ADO when dealing with JET database. This at least eliminates a possible error source (ADO). "alexhatzisavas" wrote in message ... "Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Thanks Norman. Yes, the user has read/write permission to the shared drive. A series of Access queries are executed without problems, and the code only crashes when it reaches the point where ADO is used. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. I'm using DAO for some Access manipulatins (e.g. browsing through the TableDefs collection). The code has been executing fine on other PCs, so I don't think the issue is related to this. Besides, what's the point of having these libraries if you can't use combinations of them at the same time? I'm leaning toward a corrupted MDAC, as per the following resource: http://www.dbforums.com/archive/index.php/t-321180.html I should also note that the user's PC has AS400 Client Access installed. Any ideas? "Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. "alexhatzisavas" wrote in message ... Hi all. I've run into an ADO issue, here's what's happening: I'm using an Excel file on a shared drive to automate an Access database in the background. At some point in the code I'm using ADO to export an Access dataset. The problem occurs when the code attempts to open an ADO connection. Please note that this is most likely a compatibility issue; I get no errors when I run the code from my PC. ** The error occured when another user tried to execute the code. ** Here's the VBA in question: Dim strCon As String Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon The strAppAccFullName string variable is used for the full path name to the Access database on the shared drive; its value is: "\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb" The error message I'm getting is: Run-time error '-2147220999 (800401f9): Method 'Open' of object '_Connection' failed As for the settings etc.: The Excel file is using the following references: - Visual Basic for Applications - Microsoft Excel 11.0 Object Library - OLE Automation - Microsoft Office 11.0 Object Library - Microsoft Forms 2.0 Object Library - Microsoft Access 11.0 Object Library - Microsoft DAO 3.6 Object Library - Microsoft ActiveX Data Objects 2.8 Library On my PC (where the code runs fine), I'm running Office 2003 & Windows XP. My MDAC version is 2.81. On the PC where the error occurred, the user is running Office 2002 & Windows XP. The MDAC version on that PC must be 2.8xx, because I tried to install MDAC 2.8 from Microsoft and the installation failed (I was told the component is a part of Windows). The objective is to get the code to run without errors on the other user's PC (Office 2002). Any insights / suggestions would be greatly appreciated. Many thanks, Alex |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO connection Open failure
"Norman Yuan" wrote: Since you use DAO, have you tried to use DAO to open the database/a RecordSet? If the code is to only work with JET database (*.mdb) and since DAO is needed for schema data manipulating, I'd also use DAO for data accessing. DAO is better than ADO when dealing with JET database. This at least eliminates a possible error source (ADO). The thing is, I'm using ADO from Excel to connect to the database, retrieve a dataset and 'paste' it onto a spreadhseet using the CopyFromRecordset method. The error occurred on the other user's PC when the code tries to establish the ADO connection to the database: Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon <<<- error I wouldn't know how to do the same (i.e. 'paste' an Access dataset onto a spreadsheet) using DAO from within Access. If you think that's worth exploring and you've got some sample code for this please feel free to paste it here. Thanks again. "Norman Yuan" wrote: Since you use DAO, have you tried to use DAO to open the database/a RecordSet? If the code is to only work with JET database (*.mdb) and since DAO is needed for schema data manipulating, I'd also use DAO for data accessing. DAO is better than ADO when dealing with JET database. This at least eliminates a possible error source (ADO). "alexhatzisavas" wrote in message ... "Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Thanks Norman. Yes, the user has read/write permission to the shared drive. A series of Access queries are executed without problems, and the code only crashes when it reaches the point where ADO is used. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. I'm using DAO for some Access manipulatins (e.g. browsing through the TableDefs collection). The code has been executing fine on other PCs, so I don't think the issue is related to this. Besides, what's the point of having these libraries if you can't use combinations of them at the same time? I'm leaning toward a corrupted MDAC, as per the following resource: http://www.dbforums.com/archive/index.php/t-321180.html I should also note that the user's PC has AS400 Client Access installed. Any ideas? "Norman Yuan" wrote: Does the user account that runs your code has read & write permission to the network folder ("\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb")? To connect to Jet database, the user must have read/write permission to the folder where *.mdb is in. Also, you do not need to set reference to DAO3.6, since you are using ADO. That is, either use ADO, or use DAO, not both. "alexhatzisavas" wrote in message ... Hi all. I've run into an ADO issue, here's what's happening: I'm using an Excel file on a shared drive to automate an Access database in the background. At some point in the code I'm using ADO to export an Access dataset. The problem occurs when the code attempts to open an ADO connection. Please note that this is most likely a compatibility issue; I get no errors when I run the code from my PC. ** The error occured when another user tried to execute the code. ** Here's the VBA in question: Dim strCon As String Dim con As ADODB.Connection Dim rst As ADODB.Recordset Set con = New ADODB.Connection strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & strAppAccFullName & ";" con.Open ConnectionString:=strCon The strAppAccFullName string variable is used for the full path name to the Access database on the shared drive; its value is: "\\Seukefnpcrw001\DB_ZM33_review_v1.3.mdb" The error message I'm getting is: Run-time error '-2147220999 (800401f9): Method 'Open' of object '_Connection' failed As for the settings etc.: The Excel file is using the following references: - Visual Basic for Applications - Microsoft Excel 11.0 Object Library - OLE Automation - Microsoft Office 11.0 Object Library - Microsoft Forms 2.0 Object Library - Microsoft Access 11.0 Object Library - Microsoft DAO 3.6 Object Library - Microsoft ActiveX Data Objects 2.8 Library On my PC (where the code runs fine), I'm running Office 2003 & Windows XP. My MDAC version is 2.81. On the PC where the error occurred, the user is running Office 2002 & Windows XP. The MDAC version on that PC must be 2.8xx, because I tried to install MDAC 2.8 from Microsoft and the installation failed (I was told the component is a part of Windows). The objective is to get the code to run without errors on the other user's PC (Office 2002). Any insights / suggestions would be greatly appreciated. Many thanks, Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
failure to open up excel file | Excel Discussion (Misc queries) | |||
after a power failure I can't open the spreadsheet | Excel Discussion (Misc queries) | |||
Failure to open workbook via automation | Excel Programming | |||
Connection to MySQL causes "Catastrophic failure" | Excel Programming | |||
Workbook.Open failure | Excel Programming |