Solved

Editing VBScript to connect to the Excel database

Posted on 2010-11-21
9
2,186 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 5

Accepted Solution

by:
Pabilio earned 300 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 33

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 200 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
 

Author Comment

by:Thomas_Meyer
ID: 34191523
0
 
LVL 33

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

822 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