Thomas_Meyer
asked on
Editing VBScript to connect to the Excel database
Hello Experts,
Please I need help with editing the VB script that connects users to the Excel database. With the transition to 64-bit operating system script are inoperable. The 64-bit systems working connection string: Microsoft.Jet.OLEDB.4.0, so we changed it to Microsoft.ACE.OLEDB.12.0, skrypt still shows the error: ADODB.Connection error '800a0e7a 'provider CAN NOT Be found! It May Not Be Properly Installed.
Thank you in advance for your help
Please I need help with editing the VB script that connects users to the Excel database. With the transition to 64-bit operating system script are inoperable. The 64-bit systems working connection string: Microsoft.Jet.OLEDB.4.0, so we changed it to Microsoft.ACE.OLEDB.12.0, skrypt still shows the error: ADODB.Connection error '800a0e7a 'provider CAN NOT Be found! It May Not Be Properly Installed.
Thank you in advance for your help
Const ForAppending = 8
Dim strUsername, strPassword, strComputer, adoCon, adoRec, objShell, objFSO, objFile
Set objShell = CreateObject("WScript.Shell")
strComputer = objShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
Set objFSO = CreateObject("Scripting.FileSystemobject")
strUsername = InputBox("Enter your username.")
If strUsername <> "" Then
strPassword = InputBox("Enter your password.")
If strPassword <> "" Then
Set adoCon = CreateObject("ADODB.Connection")
'On the next line change the path to and name of the spreadsheet file.'
adoCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\eeTesting\username-passwords.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set adoRec = adoCon.Execute("SELECT Password FROM [Username_passwords$] WHERE Username = '" & strUsername & "'")
If adoRec.BOF Or adoRec.EOF Then
msgbox "The username was not found.", vbCritical + vbOKOnly, "Access Denied"
Set objFile = objFSO.OpenTextFile("\\192.168.1.10\error_login.log", ForAppending, True)
objFile.WriteLine "Bad Username," & strComputer & "," & strUsername & "," & Now
objFile.Close
Else
If adoRec.Fields("Password").Value <> strPassword Then
msgbox "The password you entered does not match the username.", vbCritical + vbOKOnly, "Access Denied"
Set objFile = objFSO.OpenTextFile("\\192.168.1.10\error_login.log", ForAppending, True)
objFile.WriteLine "Bad Password," & strComputer & "," & strUsername & "," & Now
objFile.Close
Else
Set objFile = objFSO.OpenTextFile("\\192.168.1.10\login.log", ForAppending, True)
objFile.WriteLine "Logon Successful," & strComputer & "," & strUsername & "," & Now
objFile.Close
msgbox "The system is logged: " & strUsername & " " & strPassword, vbInformation + vbOKOnly
objShell.Exec "cmd /c \\192.168.1.7\mybat.bat"
End If
End If
Else
msgbox "You must enter a password.", vbCritical + vbOKOnly, "Operation Cancelled"
Set objFile = objFSO.OpenTextFile("\\192.168.1.10\error_login.log", ForAppending, True)
objFile.WriteLine "No Password," & strComputer & "," & strUsername & "," & Now
objFile.Close
End If
Else
msgbox "You must enter a username.", vbCritical + vbOKOnly, "Operation Cancelled"
Set objFile = objFSO.OpenTextFile("\\192.168.1.10\error_login.log", ForAppending, True)
objFile.WriteLine "No Username," & strComputer & ",," & Now
objFile.Close
End If
Set objShell = Nothing
Set adoRec = Nothing
Set adoCon = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Username-passwords.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To Roberto:
my old connection string for 32-bit systems look exactly as follows:
Set adoCon = CreateObject("ADODB.Connec tion")
'On the next line change the path to and name of the spreadsheet file.'
adoCon.Open "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=\\S1\logon_zaci$\sk ola\userna me-passwor ds.xls;Ext ended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set adoRec = adoCon.Execute("SELECT Password FROM [Username_passwords$] WHERE Username = '" & strUsername & "'")
If adoRec.BOF Or adoRec.EOF Then
...
Unfortunately I am not an expert, but if you'll be able to help in any way, I'll try.
Thanks.
my old connection string for 32-bit systems look exactly as follows:
Set adoCon = CreateObject("ADODB.Connec
'On the next line change the path to and name of the spreadsheet file.'
adoCon.Open "Provider=Microsoft.Jet.OL
Set adoRec = adoCon.Execute("SELECT Password FROM [Username_passwords$] WHERE Username = '" & strUsername & "'")
If adoRec.BOF Or adoRec.EOF Then
...
Unfortunately I am not an expert, but if you'll be able to help in any way, I'll try.
Thanks.
ASKER
To Imnorie:
Using MS Office 2010 32-bit. On 32-bit OS, connection string ("Microsoft.Jet.OLEDB.4.0" and "ADODB.Connection") functioned without any problems, errors occurred in up to 64 residential operating systems.
Thanks
Using MS Office 2010 32-bit. On 32-bit OS, connection string ("Microsoft.Jet.OLEDB.4.0"
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found this:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7f43a748-18a0-4201-a23e-671dc58e20ef
http://thedailyreviewer.com/office/view/replacement-for-the-ado-jet-oledb-provider-102259933
http://efreedom.com/Question/1-2075054/Upload-Excel-File-Classic-ASP-Windows-2003-X64-Using-Office-2010-Drivers
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7f43a748-18a0-4201-a23e-671dc58e20ef
http://thedailyreviewer.com/office/view/replacement-for-the-ado-jet-oledb-provider-102259933
http://efreedom.com/Question/1-2075054/Upload-Excel-File-Classic-ASP-Windows-2003-X64-Using-Office-2010-Drivers
I'm using 64 bit Windows and I've never had problems apart from some version issues that were easily fixed.
I've also got a similar set of dlls as Tommy.
I also installed the download on this pageMicrosoft Access Database Engine 2010.
Since I'm only using 32-bit versions of Excel and SQL server I installed the 32- bit version, it wouldn't let me actually install the 64-bit for that reason actually.
I've also got a similar set of dlls as Tommy.
I also installed the download on this pageMicrosoft Access Database Engine 2010.
Since I'm only using 32-bit versions of Excel and SQL server I installed the 32- bit version, it wouldn't let me actually install the 64-bit for that reason actually.
Hi Thomas...
Using a new excel workbook (No the one that already has the code in it) can you reach the data base from and get data from there ?...
If so, then in this new workbook try the code I posted before and you'll see the actual connection string and compare it with the one you have in the code posted here.
Good luck,
Roberto.
Using a new excel workbook (No the one that already has the code in it) can you reach the data base from and get data from there ?...
If so, then in this new workbook try the code I posted before and you'll see the actual connection string and compare it with the one you have in the code posted here.
Good luck,
Roberto.
ASKER
Hi Pabilio
I tried the code you recommended me and writes me an error (see picture)
I make a mistake?
Error.jpg
I tried the code you recommended me and writes me an error (see picture)
I make a mistake?
Error.jpg
Also what version of Excel is installed?
For later versions you might need to change Excel 8.0.