Solved

Editing VBScript to connect to the Excel database

Posted on 2010-11-21
9
2,131 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Thomas_Meyer
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 200 total points
Comment Utility
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
Comment Utility
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now