Manipulate Access table or query from excel using excel vba

[Flexcel22]

Hello guys & gals, genius MVPs and respected members,

I would like to manipulate data on Access table or query.

In my excel I have a userform a textboxes in which a user must type in value or text. After inserting a value or text, the user must then check 4 checkboxes depending of whichever check or uncheck then press the update button.

When the button clicked, excel opens Access table or query, Filtering values in a specific column that contains textbox value from my excel userform then there are four columns offset to that specific column that must correspond to those checkboxes in my userform. If unchecked or checked in excel userform then in Access those colmns must be unchecked or check as well.

Hope I have explained my point but I could elaborate if I happen to miss something. Thank you in advance and looking forward for considerate answers to my post.

Thank you

 

[Micron]

When the button clicked, excel opens Access table or query

The way you've written your post makes it seem that you already have some parts of this completed so my answer may not apply. Also, table and query columns are not called columns - they are fields, so I might not correctly interpret if your references are Excel or Access based.

If your userform controls are bound, then that data is in a sheet. In that case consider automation but pull it (import) from your sheet using Access. It would be easier to run queries or open forms or reports and apply filters from the Access side and use that sheet data to do the filtering.

EXCEL Import

then in Access those colmns must be unchecked or check as well.

Not clear if that means those 4 fields are part of the filtering or if you want to update them. Updating would require an update query but same thing - easier to get from your sheet data than the other way around.

If your userform controls are not bound then I think that will be considerably more difficult. IMO this is not a quick and easy thing to write and get working properly but if you have the time to research and learn it should be doable.

 

[Flexcel22]

Hi Micron,

Thank for your time and patience in coping to answer my post.

I have read the part of your answer from the link that you provided, and the latter seems to suits my needs which is to update fields in an access by opening a worksheet and transfer data respectively to a recordset . However I would like if the code is run in excel rather than in Access since I'm working in excel or updating a recordset from Excel . If the code is Access based ( run from access) then would it be tweaked to be excel base ( run from excel ) ...?

Thank you again and looking forward to hearing from your end

 

[Flexcel22]

Hi Micron,

Thank for your time and patience in coping to answer my post.

I have read the part of your answer from the link that you provided, and the latter seems to suits my needs which is to update fields in an access by opening a worksheet and transfer data respectively to a recordset . However I would like if the code is run in excel rather than in Access since I'm working in excel or updating a recordset from Excel . If the code is Access based ( run from access) then would it be tweaked to be excel base ( run from excel ) ...?

Thank you again and looking forward to hearing from your end

 

[Flexcel22]

Hi Micron,

I tried to upload a snapshot of the code in link u shared but couldn't upload maybe image to large but I referring to the sub heading below

"Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)"

I would like to run in excel vba. Can I copy and paste the code in a commandbutton in my excel userform? Or would it be tweaked to suits my needs?

 

[Micron]

Either of the 2 approaches on this page are written for Access to get data from Excel via automation. So no, not much of that coded would help you to do it the other way around. For that I would consider TransferSpreadsheet function (I believe it exists for Excel code as well). If that won't work for you then as mentioned, it would be more difficult to do what you want from Excel code. The difference being that all Access related objects would have to be created in Excel, similar to the Excel objects created in that Access code.

Perhaps if you research terms related to what you want to do you will get search engine results you can use. I've never done it from Excel.

 

[Flexcel22]

Thanks Micron, I will look around if there could be any answers. Appreciate your time.

 

[Micron]

Good luck!