Solved

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

Posted on 2008-09-30
7
1,207 Views
Last Modified: 2012-06-27
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.
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

Open in new window

0
Comment
Question by:zephyr_hex
  • 4
  • 3
7 Comments
 
LVL 23

Expert Comment

by:irudyk
ID: 22609897
Something like the following might work:
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:")
 

Dim strSQL

strSQL = ""
 

' 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

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType1='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

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

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType2='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if (bool_found=instr(1,var_item,"RB"))=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType3='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if (bool_found=instr(1,var_item,"RWW"))=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType4='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if (bool_found=instr(1,var_item,"RW"))=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType5='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if (bool_found=instr(1,var_item,"UB"))=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType6='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else wscript.echo "Invalid part number.  Part must begin with RN, RB, RW or UB"

end if
 

if strSQL <> "" Then

	Dim cn

	Set cn = CreateObject("ADODB.Connection")

	cn.Open "DSN=UPC;"
 

	Dim rs

	Set rs = CreateObject("ADODB.recordset")

	rs.Open strSQL, cn, 3, 3
 

	if Not rs.EOF then

		rs.MoveFirst

		wscript.echo rs(0)

	end if

	rs.Close

	Set rs = Nothing

	cn.Close

	Set cn = Nothing

End If

Open in new window

0
 
LVL 23

Expert Comment

by:irudyk
ID: 22609908
Forgot to mention....change TableName to the name of the table or query from your database.
0
 
LVL 42

Author Comment

by:zephyr_hex
ID: 22610251
thanks for the quick response.  i'm about to head out for the day... i'll look at this more closely tomorrow... but at first glance, this appears to be what i'm looking for.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 42

Author Comment

by:zephyr_hex
ID: 22616935
this part of the script must not be working right:
if (bool_found=instr(1,var_item,"RN"))=1 then

because it's not correctly detecting when var_item starts with "RN" ...

what i'm trying to do here is determine whether or not var_item starts with "RN"...
0
 
LVL 42

Author Comment

by:zephyr_hex
ID: 22617040
i found the error... i should be using <>0 (instead of =1) to detect when InStr does not find "RN" in var_item

InStr returns the position of the first occurrence... and not true/false.

so, my check should be:
if (instr(1,var_item,"QM"))<>0 then
0
 
LVL 23

Accepted Solution

by:
irudyk earned 500 total points
ID: 22617079
Using instr(1,var_item,"RN"))=1 should work as well.  In fact, if you want to ensure that the text you are looking for is at the start of the field, then you want the value to be 1, not anything other than zero.  See revised code:
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:")

 

Dim strSQL

strSQL = ""

 

' the script needs to know which column to look in for the <MyCompanyName> Part Number

if 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

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType1='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if 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

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType2='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if instr(1,var_item,"RB")=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType3='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if instr(1,var_item,"RWW")=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType4='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if instr(1,var_item,"RW")=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType5='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else if instr(1,var_item,"UB")=1

         strSQL = "SELECT CompetitorPartNumber FROM TableName WHERE MyCompanyPartNumberType6='" & var_item & "' And CompetitorName=" & Chr(34) & var_competitor & Chr(34)

else wscript.echo "Invalid part number.  Part must begin with RN, RB, RW or UB"

end if

 

if strSQL <> "" Then

	Dim cn

	Set cn = CreateObject("ADODB.Connection")

	cn.Open "DSN=UPC;"

 

	Dim rs

	Set rs = CreateObject("ADODB.recordset")

	rs.Open strSQL, cn, 3, 3

 

	if Not rs.EOF then

		rs.MoveFirst

		wscript.echo rs(0)

	end if

	rs.Close

	Set rs = Nothing

	cn.Close

	Set cn = Nothing

End If

Open in new window

0
 
LVL 42

Author Closing Comment

by:zephyr_hex
ID: 31501735
thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 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

13 Experts available now in Live!

Get 1:1 Help Now