Advertisement

09.30.2008 at 02:07PM PDT, ID: 23776198
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.1

How to connect to MS Access Database and return value - vbscript

Asked by zephyr_hex in VB Script, Microsoft Access Database, Visual Basic Programming

Tags: , , ,

i need to write a script (vbscript) that accepts two inputs.  these two inputs are used to find an entry in a MS Access database, and the script returns the result.

the database has several columns that correspond to different types of parts that my company sells.  these columns map to a competitor's name and competitor's part number.  this database is usually used for finding MY COMPANY'S part number based on the competitor's name and competitor's part number (interchange).
example of column names:
CompetitorName   CompetitorPartNumber   MyCompanyPartNumberType1    MyCompanyPartNumberType2 ... (up to Type6)

so, my company has 6 parts that interchange to 1 competitor part.

however, in this script, i need to do the opposite:  the user will supply my company's part number, and the competitor's name, and the competitor's part number should be returned.


inputs:  competitor name, and my company's part number
output: competitor part number

i have the basics of the script (i think), but i need help with connecting to the MS Access database, searching for the record and returning the result.
the MS Access database has an ODBC connection set up called UPC.  there is no password on the database.

one other thing to note...
my company has 6 part types, and you can determine what type a part is by looking at the first few letters of the part name.  this is what i'm attempting to do in the last part of the script.

thanks.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:

Dim var_item, var_competitor, bool_found
 
strMenu="Select a Number:" & vbCrLf &_
"1 - Competitor1" & vbCtLf &_
"2 - Competitor2" & vbCtLf &_
"3 - Competitor3" & vbCtLf &_
"4 - Competitor4" & vbCtLf &_
"5 - Competitor5" & vbCtLf &_
"6 - Competitor6" & vbCtLf &_
"7 - Competitor7"
 
rc=InputBox(strMenu,"Menu",1)
if isNumeric(rc) then
	select case rc
	case 1
		var_competitor="Competitor1"
	case 2
		var_competitor="Competitor2"
	case 3
		var_competitor="Competitor3"
	case 4
		var_competitor="Competitor4"
	case 5
		var_competitor="Competitor5"
	case 6
		var_competitor="Competitor6"
	case 7
		var_competitor="Competitor7"
	case else
		wscript.echo "Please select a valid competitor"
	end select
else
wscript.echo "Invalid Option.  Please select a number [1-7]"
end if
 
var_item=InputBox("Enter a <MyCompanyName> Part Number:")
 
' the script needs to know which column to look in for the <MyCompanyName> Part Number
if (bool_found=instr(1,var_item,"RN"))=1 then
	'check the Access database in RNEC (MyCompanyPartNumberType1) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else if (bool_found=instr(1,var_item,"RBB"))=1 then
	'check the Access database in RNCLBB (MyCompanyPartNumberType2) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else if (bool_found=instr(1,var_item,"RB"))=1
	'check the Access database in RNCLB (MyCompanyPartNumberType3) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else if (bool_found=instr(1,var_item,"RWW"))=1
	'check the Access database in RNWW (MyCompanyPartNumberType4) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else if (bool_found=instr(1,var_item,"RW"))=1
	'check the Access database in RNW (MyCompanyPartNumberType5) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else if (bool_found=instr(1,var_item,"UB"))=1
	'check the Access database in RNUBDV (MyCompanyPartNumberType6) column for var_item and return CompetitorPartNumber for corresponding var_competitor in CompetitorName column
	'if not found, return NULL
else wscript.echo "Invalid part number.  Part must begin with RN, RB, RW or UB"
end if
[+][-]09.30.2008 at 03:06PM PDT, ID: 22609897

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09.30.2008 at 03:07PM PDT, ID: 22609908

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09.30.2008 at 04:11PM PDT, ID: 22610251

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.01.2008 at 10:42AM PDT, ID: 22616935

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.01.2008 at 10:51AM PDT, ID: 22617040

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.01.2008 at 10:55AM PDT, ID: 22617079

View this solution now by starting your 14-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: VB Script, Microsoft Access Database, Visual Basic Programming
Tags: microsoft, access, 2003, vbscript
Sign Up Now!
Solution Provided By: irudyk
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20081112-EE-VQP-43 - Hierarchy / EE_QW_2_20070628