Solved

Edit VB script that connects to the database document excel

Posted on 2010-11-16
25
801 Views
Last Modified: 2013-11-25
Hi Experts,

I have a great request! We have upgraded our organization on a 64-bit operating systems,
but we experienced a serious problem in our script, which are under 32 bit operating systems
work (When you run the script error now appears: 'Microsoft.Jet.OLEDB.4.0' provider is not registered on local machine.).
This is a VB logon script, the script used my Excel database, where two columns
"Username" and "Passwords" in the script and they verified the accuracy of data entered via
the VB InputBox.
Here I read the information that "Microsoft.Jet.OLEDB" can use our systems, there are some
other (newer) version of this script connection to Excel DB You can use ODBC? If so, how?
Thank you all for your help!!!

The appendix includes web links where I read about not supporting "Microsoft.Jet.OLEDB"
in 64-bit systems, a simple example excel database sample code in our login script.
For example, questions like I will send the login script.

Thomas M.
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.Jet.OLEDB.4.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
  • 13
  • 12
25 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148365
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148374
In the overview it states that it works to integrate Access and Excel data to SQL Server on 64 bit systems, so use this instead of Jet. It seems this will be replacing Jet in the future when we all end up on 64 bit systems.
0
 

Author Comment

by:Thomas_Meyer
ID: 34153176
Thanks, Microsoft Access Database Engine Redistributable 2010 I have installed, but my script does not work (says the same error). You may need to adjust the conection string, you do not know how to please?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34157160
Haven't used it myself, but I'd guess you need
Microsoft.ACE.OLEDB.12.0
in place of
Microsoft.Jet.OLEDB.4.0
in the connection string
0
 

Author Comment

by:Thomas_Meyer
ID: 34158485
OK, I'll try, you need something please adjust the line to create connections to ADODB?
I think it could be a problem.
Thanks a lot.
Set adoCon = CreateObject ("ADODB.Connection")

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34158579
No. It still uses ADODB.Connection.
The DR=Yes;IMEX=1 stuff is still valid too.
0
 

Author Comment

by:Thomas_Meyer
ID: 34161985
I changed the conection string to Microsoft.ACE.OLEDB.12.0, but when running the script runs just an error in the line conection string: ADODB.Connection error '800a0e7a 'Provider can not be found. It May Not Be Properly Installed.
I found this article, but I do not know whether I can help:

http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-80040200/80040514/800a0e7a-errors.html

Pretty please advice.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34168653
You did download AccessDatabaseEngine_X64.exe not AccessDatabaseEngine.exe, right?

http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
0
 

Author Comment

by:Thomas_Meyer
ID: 34175525
AccessDatabaseEngine_X64.exe i can not install because the 64-bit operating system we have installed 32-bit MS Office 2010 (as recommended by Microsoft). Do you think it would be a problem?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34175558
I would think it should match the Office version.
0
 

Author Comment

by:Thomas_Meyer
ID: 34178901
Thus it may be the problem? Operating systems have 64bit and 32bit MS Office, this problem can be solved somehow (?) As easy by another user database instead of Excel? Do you know of any method?
Poslech
Fonetický prepis
Slovník - Zobrazit podrobný slovníkpríslovce0.so0.as0.that0.then0.thereby0.like that0.accordinglyspojka0.in that mannerzájmeno0.thus0.that way
0
 

Author Comment

by:Thomas_Meyer
ID: 34178913
Thus it may be the problem? Operating systems have 64bit and 32bit MS Office, this problem can be solved somehow (?) As easy by another user database instead of Excel? Do you know of any method?

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34181436
I'm not sure. Could you post the code you are using?
Alternatively you could accept my answer to your first question about replacing Jet and repost this in the Excel and SQL zones. I haven't done much 64 bit integration and more people would see it that way.
However, I will look at your code and test it out on my 32 bit system to see if it works there if you would like. Maybe we can narrow it down some.
0
 

Author Comment

by:Thomas_Meyer
ID: 34182688
All code is completely up in my post. The 32-bit operating system, I personally have a 32 bit system and the script run on it without problem, but in our organization for 64-bit systems, the script shows the error.
Try to do what is in your power, in any case, you add points.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34190927
Okay, I found a machine that runs 64-bit Windows Server and has 32-bit Office that I can test on.

Strange. Your code works just fine with both Jet and ADE.
Let me see if I can find out exactly what is installed on this computer.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34191162
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: 34191270
These files have its 32-bit system as well, not in 64-bit ...
I found this: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7f43a748-18a0-4201-a23e-671dc58e20ef
0
 

Author Comment

by:Thomas_Meyer
ID: 34191511
0
 

Author Comment

by:Thomas_Meyer
ID: 34194390
All folders and files have *. dll in the same place as yours, the error is when opening ADODB.Connection, not ADODB.Connection replace them with something else?
0
 

Author Comment

by:Thomas_Meyer
ID: 34255197
Hello,
he would not need to replace the Excel document as a text document and make it a simple user database?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34258660
Using a database (like Access) instead of Excel is usually a good idea. If he's willing to do that, then go with it.
0
 

Author Comment

by:Thomas_Meyer
ID: 34264804
With Acces database I have never worked, I do not know how to build simple, can you recommend any procedures? It will also certainly need to change the connection string?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34267438
The problem is going to be that Access will use the same provider. I find it strange that yours doesn't work since mine does. The only thing I could suggest for that would be to reinstall Office and hope for the best.
0
 

Author Comment

by:Thomas_Meyer
ID: 34268571
And would it not be a remake of a database in Excel as a text document? And then try a different connection string? Reinstalling Office does not help.
The problem is only in the ADODB connection.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34268855
I've never used a connection string to connect to a text file. Not sure how to approach that one.

Like I mentioned above, your code works just fine as is on my 64 bit system with 32 bit Office with both JET and ACE so I'm not sure why yours isn't working.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 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