• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1255
  • Last Modified:

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

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
zephyr_hex (Megan)
Asked:
zephyr_hex (Megan)
  • 4
  • 3
1 Solution
 
irudykCommented:
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
 
irudykCommented:
Forgot to mention....change TableName to the name of the table or query from your database.
0
 
zephyr_hex (Megan)DeveloperAuthor Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
zephyr_hex (Megan)DeveloperAuthor Commented:
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
 
zephyr_hex (Megan)DeveloperAuthor Commented:
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
 
irudykCommented:
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
 
zephyr_hex (Megan)DeveloperAuthor Commented:
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now