Solved

get an mdb table record using vbscript

Posted on 2013-12-10
11
1,056 Views
Last Modified: 2013-12-15
#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.  

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)

Open in new window


--- 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.
0
Comment
Question by:rberke
  • 6
  • 3
11 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39708600
Microsoft Jet is not installed.  You need to download and install it.  

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.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39708606
Here, this should be what you need to download & install.
http://www.microsoft.com/en-us/download/details.aspx?id=13255
0
 
LVL 5

Author Comment

by:rberke
ID: 39709280
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
0
 
LVL 5

Author Comment

by:rberke
ID: 39709442
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\wscript "C:\Users\bob.berke\Desktop\test4.vbs"

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?
0
 
LVL 5

Accepted Solution

by:
rberke earned 0 total points
ID: 39709510
Windows is a 4 letter word  !!!!

It appears that this is a common problem which can be fixed with the code at http://stackoverflow.com/questions/2806584/how-do-i-run-a-vbscript-in-32-bit-mode-on-a-64-bit-machine

I followed the instructions and it worked perfectly.  I copied the code to the clipboard,
then pasted it with no changes above my first line.

After that my vbs worked fine.  Ugly as sin, but it worked fine.  There are simpler versions of the code available elsewhere, but this is the first version I found that allows the .vbs program to be called with multiple arguments.

Ughhhhhh

So, I answered my question #1 myself.  I will still give points to anybody that tells me how to switch my code to use DAO instead of ADO.
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 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 250 total points
ID: 39709920
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39709951
DAO is extremely obsolete.
I don't think so - it is the default for A2013 and is alive and well.  Since A2007 and the advent of ACE (replacement for Jet), DAO has been under the control of the Access development team rather than the SQL Server team who kept trying to kill Jet and spreading rumors of its demise.  But instead of saying we are killing Jet, they kept telling people that they were killing Access.

It is ADO that is losing support -
http://blogs.msdn.com/b/adonet/archive/2011/09/13/microsoft-sql-server-oledb-provider-deprecation-announcement.aspx
0
 
LVL 5

Assisted Solution

by:rberke
rberke earned 0 total points
ID: 39710182
That link dd not convince me that DAO is on the rebound, but that doesn't matter.

I'll stop using DAO when it stops working and not until then.

But, I really don't have a strong preference for either, its just that I have a bunch of old code that uses DAO, and I don't have a good business reason to change it.  

But, the vbscript environment is radically different than the VBA environment, and the program is now working well with ADO, so I think this particular program will stay in ADO.

Thanks for your help.
0
 
LVL 5

Author Comment

by:rberke
ID: 39713007
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\wscript “fivelines.vbs”

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
0
 
LVL 5

Author Closing Comment

by:rberke
ID: 39719767
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

747 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

14 Experts available now in Live!

Get 1:1 Help Now