Excel read to and from access database with adodb

Hey

Im trying to make an bill of materials item info. its a basic form with item#, description, client, material 1, material2, work1, work2.
I dont have a db yet for this. so i will use the form in a different sheet to enter that info into a db. the load form i would want to load information from a simple dropdown list of the item#. it doesnt need to have a search option..i just want it to work then i could add search by description. 

I made a form using excel with cells and not textboxes and such.  I have mapped each cell to another sheet called workspace and they are on 1 row . This makes is easier to load info from an Access table because i tried doing 1 cell for each Fields.Value and it was VERY slow.

I have 'sheet 1' with the form, the cells mapped to a row 3 of 'workspace sheet' and a button that loads a table row from Access db to row 3 of 'workspace sheet'.  

I think I will have to have a 'sheet3' to Edit entries. so id load on a different row in "Workspace sheet" then

I have 'sheet 2' with the form and the cells in 'workspace sheet' are mapped to the cells on the form. So when i enter info on the sheet, it mirrors it to row2 of 'workspace sheet' and it will have a button that sends row2 of 'workspace sheet' to the Access table.

Im stuck because i dont have any experience doing this. I got as far as making the load button work but barely because it puts previous entries onto 'workspace sheet' row4. **which is fine because i wont be using row4 anyway....**  
and i need a way to load a specific row from the Access table.

the code i have now that works to pull 1 line is

Worksheets("Workspace").Cells(3, 2).CopyFromRecordset rs

**this line is found in the code.txt attached. it is just the load button

---

Thank you for any and all help!!

PS
I dont want the whole code written for me because it sounds like a lot. I just need guidance on what code i need to use for what and how to use them.

AttachmentSize
code.txt640 bytes
Vishesh's picture

Read from Access

After this line

rs.Open "Select * from AIC_Items"

write, for example rs.filter ="FirstName = 'Wilma' OR FirstName = 'Betty'"

where firstname is field name .

On the same line you can create a filter to extract exactly one row.

To reset the filter back to full table rs.filter=""