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.
Attachment | Size |
---|---|
code.txt | 640 bytes |
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=""