get an mdb table record using vbscript
#1) How can I get the following code to run under vbs? It already works under vba, but under vbscript I get the error message
provider cannot be found. It may not be properly installed
#2) How can I get it to use DAO instead of ADO? (this is not too important, but I would like to know for future projects.)
The code must run on Windows 7 computers with Office 2010 home and business, They do NOT have MS Access installed.
--- some background details, but they are not very important --------
I have modified the windows explorer context menu for our small business client computers.
When users right click on a file named "Client #1234 has not received their order" the users see the normal windows commands like "open", "delete" and one additional command :
"GOTO client's master folder"
When the user chooses that GOTO option, the vbs program grabs the filename, scans for the client number, then opens the directory associated with the order.
Right now, the client numbers are hard coded in the vbs, but that is a temporary workaround until I figure out how to get the data from the database.
provider cannot be found. It may not be properly installed
#2) How can I get it to use DAO instead of ADO? (this is not too important, but I would like to know for future projects.)
The code must run on Windows 7 computers with Office 2010 home and business, They do NOT have MS Access installed.
Const path = "C:\Users\bob.berke\Documents\db1.mdb"
const custno = 1234567
sql = "select folder from table2 where cust = " & custno
Set objconnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objconnection.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & path
objRecordSet.Open sql, objconnection
MsgBox objRecordSet(0)--- some background details, but they are not very important --------
I have modified the windows explorer context menu for our small business client computers.
When users right click on a file named "Client #1234 has not received their order" the users see the normal windows commands like "open", "delete" and one additional command :
"GOTO client's master folder"
When the user chooses that GOTO option, the vbs program grabs the filename, scans for the client number, then opens the directory associated with the order.
Right now, the client numbers are hard coded in the vbs, but that is a temporary workaround until I figure out how to get the data from the database.
Here, this should be what you need to download & install.
http://www.microsoft.com/en-us/download/details.aspx?id=13255
http://www.microsoft.com/en-us/download/details.aspx?id=13255
ASKER
I have 10 computers that will need it, and I am a little lazy about installing software on all 10 computers.
All 10 of those programs already have Vba applications that merrily read mdb tables with DAO under Excel, Word, and Outlook.
Isn't there some way to do the same thing with vbscript without installing new software?
For instance, perhaps vba is using a different provider? If so, I could browse the object using excel's IDE and find the right name to use?
Also, that code specifying Microsoft.Jet.OLEDB.4.0 works under vba. Doesn't that sort of imply that the jet engine is already installed?
rberke
All 10 of those programs already have Vba applications that merrily read mdb tables with DAO under Excel, Word, and Outlook.
Isn't there some way to do the same thing with vbscript without installing new software?
For instance, perhaps vba is using a different provider? If so, I could browse the object using excel's IDE and find the right name to use?
Also, that code specifying Microsoft.Jet.OLEDB.4.0 works under vba. Doesn't that sort of imply that the jet engine is already installed?
rberke
ASKER
I forgot to mention all the Windows 7 machines are using 64 bit Windows 7.
I found this reference which is interesting http://stackoverflow.com/questions/15069440/accessing-mdb-through-vbs
When I invoke the program using the windows 32 bit compatibility code, things work fine !!!
c:\windows\syswow64\wscrip
So, as a "solution" I can make my right click context menu program call a .bat file that calls the vbs program. Bu that is really ugly.
Can anybody suggest a cleaner way? Maybe vbscript has a compiler directive that tells it to run in 64bit mode?
I found this reference which is interesting http://stackoverflow.com/questions/15069440/accessing-mdb-through-vbs
When I invoke the program using the windows 32 bit compatibility code, things work fine !!!
c:\windows\syswow64\wscrip
So, as a "solution" I can make my right click context menu program call a .bat file that calls the vbs program. Bu that is really ugly.
Can anybody suggest a cleaner way? Maybe vbscript has a compiler directive that tells it to run in 64bit mode?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In response to the administrative comment about the link policy, I have redrafted the "correct answer" as follows.
I have a 5 line vbscript program that fails.
When a user double clicks on FiveLines.vbs, Windows 64bit launches a 64bit command processor. Unfortuantely, that processor is not able to handle ADO and similar database functions so it displays an error message.
Luckily, Windows 64bit also ships with a 32bit command processor that CAN handle those functions.
It can be executed from a batch file as follows
c:\windows\syswow64\wscrip
But, since my program must be run from a windows explorer context menu, that might not work.
Instead, I added a chunk of extra code above the original 5 lines. The added chunk does the following
1) If the 32bit command processor is running, transfer to the 5 lines of code
2) if the 64bit command processor is running, create a new command line string, and shell to it using the syswow64 version of wscript.
The bad news is that this takes about 60 lines of obscure coding. The good news is I found the exact code needed here. I just copied the code to the clipboard and pasted it in front of my code. Problem solved
I have a 5 line vbscript program that fails.
When a user double clicks on FiveLines.vbs, Windows 64bit launches a 64bit command processor. Unfortuantely, that processor is not able to handle ADO and similar database functions so it displays an error message.
Luckily, Windows 64bit also ships with a 32bit command processor that CAN handle those functions.
It can be executed from a batch file as follows
c:\windows\syswow64\wscrip
But, since my program must be run from a windows explorer context menu, that might not work.
Instead, I added a chunk of extra code above the original 5 lines. The added chunk does the following
1) If the 32bit command processor is running, transfer to the 5 lines of code
2) if the 64bit command processor is running, create a new command line string, and shell to it using the syswow64 version of wscript.
The bad news is that this takes about 60 lines of obscure coding. The good news is I found the exact code needed here. I just copied the code to the clipboard and pasted it in front of my code. Problem solved
ASKER
The experts comments are interesting so they both get points, but I found the code that made things work on my own with no help for either expert.
I don't think you will need to install the MS Access Runtime. It is free with Visual Studio Professional ... maybe other packages as well ... but for merely accessing the DB via Jet it should not be needed.