Solved

Editing VBScript to connect to the Excel database

Posted on 2010-11-21
9
2,218 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 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