?
Solved

Editing VBScript to connect to the Excel database

Posted on 2010-11-21
9
Medium Priority
?
2,294 Views
Last Modified: 2012-08-14
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

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

Open in new window

Username-passwords.xls
0
Comment
Question by:Thomas_Meyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 5

Accepted Solution

by:
Pabilio earned 1200 total points
ID: 34183094
Helloo Thomas,

To see if the error is in the conection string you can try this:

Make a dummy excel file and Get external data from an already existing Excel workbook.
Once you have some sample data in the dummy excel spreadsheet run the following code in sheet1:

Sub foo()
Range("F1").Value = Worksheets(1).QueryTables(1).Connection
End Sub

This will display at cell F1 the connection string used from excel and maybe it could help you to find the error...
You will see in Cell F1 something like:
ODBC;DRIVER=SQL Server;SERVER=SERVIDOR;UID=Administrador;APP=Microsoft Office 2003;WSID=SERVIDOR;DATABASE=CEDRO;Network=DBMSLPCN;Trusted_Connection=Yes

Compare it with your OLD connection string and maybe you'll find the difference there.

Hope it helps,
Roberto.
0
 
LVL 34

Expert Comment

by:Norie
ID: 34187701
Double check the connection string.

Also what version of Excel is installed?

For later versions you might need to change Excel 8.0.
0
 

Author Comment

by:Thomas_Meyer
ID: 34190053
To Roberto:
my old connection string for 32-bit systems look exactly as follows:

    Set adoCon = CreateObject("ADODB.Connection")
        'On the next line change the path to and name of the spreadsheet file.'
        adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\S1\logon_zaci$\skola\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
...

Unfortunately I am not an expert, but if you'll be able to help in any way, I'll try.

Thanks.


0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:Thomas_Meyer
ID: 34190101
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
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 800 total points
ID: 34191210
It works on my 64-bit machine.

In C:\Windows\SysWOW64
I have
msjet40.dll version is 4.00.9756.0
msjetoledb40.dll version is same

In C:\Windows\winsxs\x86_microsoft-windows-m..-components-jetcore_31bf3856ad364e35_6.1.7600.16385_none_046511bf090691ab
I have the same files and versions

In C:\Program Files (x86)\Common Files\microsoft shared\OFFICE12
I have ACEOLEDB.dll version 12.0.4518.1014
There are also a bunch of other ACE*.dll files.

Perhaps it's just a matter of getting the right files in the right spot.
0
 
LVL 34

Expert Comment

by:Norie
ID: 34192600
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.
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 34193678
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.
0
 

Author Comment

by:Thomas_Meyer
ID: 34255220
Hi Pabilio
I tried the code you recommended me and writes me an error (see picture)
I make a mistake?

Error.jpg
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question