ODBC Connect error in Script Host Version 5.8
in [VbScript]
|
Prev: How to Use/Replace Capicom in VBS script on Windows7 x64?
Next: [Fixed Up] - Re: ASP function question.
From: TreyS on 5 Feb 2010 11:21 My Access/ODBC connection code is failing in Win7/Script Host 5.8. If it runs on V 5.6, it works. If it runs inside VbsEdit debugger, on Win7, it works. It FAILS running directly on either Win7 or Server 2008 R2 (V 5.8) I guess the basic question is what is different between V 5.6 and V 5.8? If the underlying OS/provider has changed, I think that would be the same when running either in or out of the VbsEdit debugger. I'm using the generic format: Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; (From ConnectionStrings.com) Specifically: Driver={Microsoft Access Driver (*.mdb)};Dbq=GPCData.mdb;DefaultDir=\\DC02\GPC_DB;Uid=Admin;Pwd=; Or: Driver={Microsoft Access Driver (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=; Error Message: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified There is no Data Source Name configured. ( and I don't want to create one...). I believe the Driver is specified in the connection string. I have also tested the MS Jet OLE DB 4.0 version: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=; As: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=; Error Message: ADODB.Connection: Provider cannot be found. It may not be properly installed. Office 2007, including Access, is installed on the Win7 system, so I would think any required components should be present. What am I missing?
From: Richard Mueller [MVP] on 5 Feb 2010 13:35 "TreyS" <TreyS(a)HotMail.com> wrote in message news:4663906A-9725-4CBA-AE05-C56D29C959A7(a)microsoft.com... > My Access/ODBC connection code is failing in Win7/Script Host 5.8. > If it runs on V 5.6, it works. > If it runs inside VbsEdit debugger, on Win7, it works. > > It FAILS running directly on either Win7 or Server 2008 R2 (V 5.8) > > I guess the basic question is what is different between V 5.6 and V 5.8? > If the underlying OS/provider has changed, I think that would be the same > when running either in or out of the VbsEdit debugger. > > I'm using the generic format: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; (From > ConnectionStrings.com) > Specifically: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=GPCData.mdb;DefaultDir=\\DC02\GPC_DB;Uid=Admin;Pwd=; > Or: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=; > > Error Message: Microsoft OLE DB Provider for ODBC Drivers: > [Microsoft][ODBC Driver Manager] Data source name not found and no default > driver specified > > There is no Data Source Name configured. ( and I don't want to create > one...). I believe the Driver is specified in the connection string. > > > I have also tested the MS Jet OLE DB 4.0 version: > Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User > Id=admin;Password=; > As: > Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=; > Error Message: ADODB.Connection: Provider cannot be found. It may not be > properly installed. > > Office 2007, including Access, is installed on the Win7 system, so I would > think any required components should be present. > > What am I missing? Access 2007 uses a new OLEDB driver, Microsoft.ACE.OLEDB.12.0. See this link: http://www.connectionstrings.com/access-2007 I use something similar to: strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=c:\MyFolder\MyDatabase.mdb;" _ & "Persist Security Info=False;" -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net --
From: TreyS on 9 Feb 2010 10:44 Sorry, no fix yet... Good to know about the new OLEDB driver, but that appears NOT to be the issue. Using the connection string: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;Persist Security Info=False" I get the same results/error. It DOES work from within the VbsEdit debugger ( Win7/64, Office 07, WSH 5.8 ). On that Win7/64, or Server 2008 R2 ( no Office installed, WSH 5.8) client, from command prompt, it fails with the same error mentioned earlier. There is no error when run from an XP client with Office 2007 and WSH 5.7. (Appears to have new OLEDB driver) On XP client ( Office 2003 and WSH 5.7 ) I get the "Provider cannot be found" error (Presumably because V12 driver is not available) Same system succeeds using the OLEDB V4 connection string. BTW, the database is an MDB, in Access 2000 format. It appears to be a difference between WSH 5.6/5.7 and WSH 5.8. More thoughts?... "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in message news:e00WBIppKHA.1544(a)TK2MSFTNGP06.phx.gbl... > > "TreyS" <TreyS(a)HotMail.com> wrote in message > news:4663906A-9725-4CBA-AE05-C56D29C959A7(a)microsoft.com... >> My Access/ODBC connection code is failing in Win7/Script Host 5.8. >> If it runs on V 5.6, it works. >> If it runs inside VbsEdit debugger, on Win7, it works. >> >> It FAILS running directly on either Win7 or Server 2008 R2 (V 5.8) >> >> I guess the basic question is what is different between V 5.6 and V 5.8? >> If the underlying OS/provider has changed, I think that would be the same >> when running either in or out of the VbsEdit debugger. >> >> I'm using the generic format: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; (From >> ConnectionStrings.com) >> Specifically: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=GPCData.mdb;DefaultDir=\\DC02\GPC_DB;Uid=Admin;Pwd=; >> Or: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=; >> >> Error Message: Microsoft OLE DB Provider for ODBC Drivers: >> [Microsoft][ODBC Driver Manager] Data source name not found and no >> default driver specified >> >> There is no Data Source Name configured. ( and I don't want to create >> one...). I believe the Driver is specified in the connection string. >> >> >> I have also tested the MS Jet OLE DB 4.0 version: >> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User >> Id=admin;Password=; >> As: >> Provider=Microsoft.Jet.OLEDB.4.0;Data >> Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=; >> Error Message: ADODB.Connection: Provider cannot be found. It may not be >> properly installed. >> >> Office 2007, including Access, is installed on the Win7 system, so I >> would think any required components should be present. >> >> What am I missing? > > Access 2007 uses a new OLEDB driver, Microsoft.ACE.OLEDB.12.0. See this > link: > > http://www.connectionstrings.com/access-2007 > > I use something similar to: > > strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _ > & "Data Source=c:\MyFolder\MyDatabase.mdb;" _ > & "Persist Security Info=False;" > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > >
From: TreyS on 10 Feb 2010 18:56 Interesting, but no solution. Good to know about the new, v12, OLEDB driver. However, results with the new driver are almost the same as with the v4 version. Machine A1 - Win7/64, WSH 5.8, Office 2007 Machine A2 - Same machine, running within vbsEdit debugger Machine B - Srv08R2, WSH 5.8, no Office installed Machine C - XP, WSH 5.7, Office 2003 Machine D - XP, WSH 5.7, Office 2007 The error, when it occurs, is "Provider cannot be found. It may not ..." When using the V4 OLEDB connection string, the script: Succeeds on A2, C, D Fails on A1, B When using the V12 connection string, the script: Succeeds on A2, D Fails on A1, B, C It makes sense V12 should fail on C, since it doesn't have Office 2007. It seems like there is a difference between the script host versions 5.7 and 5.8. My test script follows... ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'strConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=;" 'strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=;" strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;Persist Security Info=False" WScript.Echo "Connection string:" & _ vbCrLf & _ " " & _ strConnection Set objConnection = CreateObject("ADODB.Connection") 'Errors occur here, when they happen... objConnection.Open strConnection WScript.Echo "!!Connection succeeded!!" Set objRecordset = CreateObject("ADODB.Recordset") strQuery = "Select * From EmailConfig;" Set objRecordSet = objConnection.Execute( strQuery ) Do While NOT objRecordSet.EoF intCount = intCount + 1 objRecordSet.MoveNext Loop WScript.Echo vbCrLf & _ " Retrieved " & _ intCount & _ " records." ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in message news:e00WBIppKHA.1544(a)TK2MSFTNGP06.phx.gbl... > > "TreyS" <TreyS(a)HotMail.com> wrote in message > news:4663906A-9725-4CBA-AE05-C56D29C959A7(a)microsoft.com... >> My Access/ODBC connection code is failing in Win7/Script Host 5.8. >> If it runs on V 5.6, it works. >> If it runs inside VbsEdit debugger, on Win7, it works. >> >> It FAILS running directly on either Win7 or Server 2008 R2 (V 5.8) >> >> I guess the basic question is what is different between V 5.6 and V 5.8? >> If the underlying OS/provider has changed, I think that would be the same >> when running either in or out of the VbsEdit debugger. >> >> I'm using the generic format: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; (From >> ConnectionStrings.com) >> Specifically: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=GPCData.mdb;DefaultDir=\\DC02\GPC_DB;Uid=Admin;Pwd=; >> Or: >> Driver={Microsoft Access Driver >> (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=; >> >> Error Message: Microsoft OLE DB Provider for ODBC Drivers: >> [Microsoft][ODBC Driver Manager] Data source name not found and no >> default driver specified >> >> There is no Data Source Name configured. ( and I don't want to create >> one...). I believe the Driver is specified in the connection string. >> >> >> I have also tested the MS Jet OLE DB 4.0 version: >> Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User >> Id=admin;Password=; >> As: >> Provider=Microsoft.Jet.OLEDB.4.0;Data >> Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=; >> Error Message: ADODB.Connection: Provider cannot be found. It may not be >> properly installed. >> >> Office 2007, including Access, is installed on the Win7 system, so I >> would think any required components should be present. >> >> What am I missing? > > Access 2007 uses a new OLEDB driver, Microsoft.ACE.OLEDB.12.0. See this > link: > > http://www.connectionstrings.com/access-2007 > > I use something similar to: > > strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _ > & "Data Source=c:\MyFolder\MyDatabase.mdb;" _ > & "Persist Security Info=False;" > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > >
From: TreyS on 11 Feb 2010 08:45
Interesting, but no solution. Good to know about the new, v12, OLEDB driver. However, results with the new driver are almost the same as with the v4 version. Machine A1 - Win7/64, WSH 5.8, Office 2007 Machine A2 - Same machine, running within vbsEdit debugger Machine B - Srv08R2, WSH 5.8, no Office installed Machine C - XP, WSH 5.7, Office 2003 Machine D - XP, WSH 5.7, Office 2007 The error, when it occurs, is "Provider cannot be found. It may not ..." When using the V4 OLEDB connection string, the script: Succeeds on A2, C, D Fails on A1, B When using the V12 connection string, the script: Succeeds on A2, D Fails on A1, B, C It makes sense V12 should fail on C, since it doesn't have Office 2007. It seems like there is a difference between the script host versions 5.7 and 5.8. My test script follows... ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'strConnection = "Driver={Microsoft Access Driver (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=;" 'strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=;" strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\DC02\GPC_DB\GPCData.mdb;Persist Security Info=False" WScript.Echo "Connection string:" & _ vbCrLf & _ " " & _ strConnection Set objConnection = CreateObject("ADODB.Connection") 'Errors occur here, when they happen... objConnection.Open strConnection WScript.Echo "!!Connection succeeded!!" Set objRecordset = CreateObject("ADODB.Recordset") strQuery = "Select * From EmailConfig;" Set objRecordSet = objConnection.Execute( strQuery ) Do While NOT objRecordSet.EoF intCount = intCount + 1 objRecordSet.MoveNext Loop WScript.Echo vbCrLf & _ " Retrieved " & _ intCount & _ " records." ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' "TreyS" <TreyS(a)HotMail.com> wrote in message news:4663906A-9725-4CBA-AE05-C56D29C959A7(a)microsoft.com... > My Access/ODBC connection code is failing in Win7/Script Host 5.8. > If it runs on V 5.6, it works. > If it runs inside VbsEdit debugger, on Win7, it works. > > It FAILS running directly on either Win7 or Server 2008 R2 (V 5.8) > > I guess the basic question is what is different between V 5.6 and V 5.8? > If the underlying OS/provider has changed, I think that would be the same > when running either in or out of the VbsEdit debugger. > > I'm using the generic format: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; (From > ConnectionStrings.com) > Specifically: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=GPCData.mdb;DefaultDir=\\DC02\GPC_DB;Uid=Admin;Pwd=; > Or: > Driver={Microsoft Access Driver > (*.mdb)};Dbq=\\DC02\GPC_DB\GPCData.mdb;Uid=Admin;Pwd=; > > Error Message: Microsoft OLE DB Provider for ODBC Drivers: > [Microsoft][ODBC Driver Manager] Data source name not found and no default > driver specified > > There is no Data Source Name configured. ( and I don't want to create > one...). I believe the Driver is specified in the connection string. > > > I have also tested the MS Jet OLE DB 4.0 version: > Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User > Id=admin;Password=; > As: > Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=\\DC02\GPC_DB\GPCData.mdb;User Id=admin;Password=; > Error Message: ADODB.Connection: Provider cannot be found. It may not be > properly installed. > > Office 2007, including Access, is installed on the Win7 system, so I would > think any required components should be present. > > What am I missing? |