1. Create the simple MS SQL database with the 2 simple table and data listed at the bottom of these instructions.
2. Open the [login to view URL] file attached.
3. Write a VBA module that, when run, connects to the SQL database, loops through the first 100 rows of the worksheet, and for each row that has a value in the Age column:
a. gets the Age, Status, and Color values from the row in the worksheet
b. selects 'price' value from the SQL "Dogs" table WHERE the age, status, and color values match the .xlsx row
c. If 1 row is returned, inserts the SQL [login to view URL] value into the 'Allowed' column in that row in the worksheet
d. if >1 row is returned, inserts the string "multiple" in the 'Allowed' column for that row in the worksheet
e. If 0 rows are returned, then does the same Select operation from the SQL "Cats" table and inserts the value in the allowed column as described above.
f. If 0 rows are returned from the "dogs" and 0 from the "cats" table, then inserts nothing into the allowed column.
4. If this is even possible:
a. Write a second VBA module (or other VB application), that when an age, status, and color is entered in real-time in a row, the VBA module automatically and asynchronously performs the actions above for that row.
b. If this is possible or not, advise me in your bid
Database:
Create a throwaway MS SQL server database called 'automation'
Create a throwaway table called 'Dogs' with columns
'age' integer,
'status' varchar(20),
'color' varchar(20),
'price' numneric(8,2)
Insert arbitrary data:
22,'fat','blue',102.23
26,'fat','black',234.13
26,'','black',1234.13
10,'','green',203.2
12,'fat','red',304.12
4,'dead','red',1209.13
1,'','red',2406.8
1,'fat','red',1400.12
1,'dead','red',1.23
10,'','blue',890.3
10,'fat','blue',103.23
10,'dead','blue',102.12
89,'','black',8.03
6,'','black',70.34
Create a throwaway table called 'Cats' with columns
'age' integer,
'status' varchar(20),
'color' varchar(20),
'price' numneric(8,2)
Insert arbitrary data:
55,'fat','blue',22.23
55,'fat','black',24.13
56,'','black',34.13
50,'','green',23.2
52,'fat','red',34.12