Solved

Editing VBScript to connect to the Excel database

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 41
How to use odbc in vb to connect to ms sql 14 40
Split a string in excel 9 36
Merging-Splitting-Multiple-Rows 33 46
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

20 Experts available now in Live!

Get 1:1 Help Now