Push data from EXCEL into ACCESS form

[girlscout]

I am starting to learn how to connect between EXCEL and ACCESS using macros. And I've already figured out how to get my information from my EXCEL spreadsheet right into an ACCESS table. This works very nicely. What I'd like to learn how to do is take my information from EXCEL and put it into an ACCESS form that is already set up. Some one has suggested that I put the data into an ACCESS table and pull it into the form from there, but I am not sure how to do this. I am relatively familiar with EXCEL but only a little knowledge about ACCESS. I am still getting my head around the SQL languange and everything used to connect between the two programs. Even if someone had a suggestion about a site that explained how to connect between the two programs would be helpful.

 

[Norie]

Why you would want to 'put' data from Excel into a form in Access?

I don't actually quite understand what you mean by that.

Forms are usually used to display data from tables/queries and allow the user to add/edit/delete records.

 

[swinglow]

if you use the Form Wizard you can specifiy which Table you want the form to be bound to

 

[xenou]

I am starting to learn how to connect between EXCEL and ACCESS using macros. And I've already figured out how to get my information from my EXCEL spreadsheet right into an ACCESS table. This works very nicely. What I'd like to learn how to do is take my information from EXCEL and put it into an ACCESS form that is already set up. Some one has suggested that I put the data into an ACCESS table and pull it into the form from there, but I am not sure how to do this. I am relatively familiar with EXCEL but only a little knowledge about ACCESS. I am still getting my head around the SQL languange and everything used to connect between the two programs. Even if someone had a suggestion about a site that explained how to connect between the two programs would be helpful.

There are many ways to do this and I'm not at all sure which is best! However, one key factor is whether the user will be updating the data and sending information back to Excel during the session (i.e., updates, additions, deletes?). Also, is the data changing constantly or pretty much stable - that is, can you pretty much set up the push from Excel to Access and then never worry about it after that, or will the data in Excel be a "moving target"? Anyway, add whatever details you can about the purpose of the form and how its related to this Excel file - we'll probably have as many suggestions as there are posts in this thread

 

[girlscout]

The data is "stable" as you would call it. The Access database is already set up by a 3rd party and the user only has access to the form to insert data, not to the table it is bound to. I want to take the data from my Excel sheet and just put it directly into the form since I can't get to the Access table.

 

[xenou]

I like using ADO with Excel/Access. You can read a bit on this here:
http://www.erlandsendata.no/english/index.php?t=envbadac

Also useful is w3schools when it comes to more info on ADO:
http://www.w3schools.com/ado/default.asp

ADO requires a little bit of work up front (learning the basics), but once you get used to its basic objects (connection, recordset, command) you can do a lot with them - they are simple but powerful. For instance, you can query Excel, grab the bit of data you need, and drop it into your form (it works the other way too - but with Excel you must always beware that users can do strange things with a spreadsheet, unlike Access tables where there is stricter control over what can or can't be in a table).

Alternatives:
--You can write an Excel macro to get the info - anything you can do in Excel you can do with Excel "from Access".
--You can pull the data from Excel all at once and drop it into a local temporary Access table (so you only go to Excel once, then deal with the data "locally").
--Under some circumstances, you can add an Excel spreadsheet as a linked table in Access.

Please keep posting your questions as this is all very general. We may need to get down to brass tacks when it comes to your actual project - more exact details may help with more specific suggestions.

 

[girlscout]

Thank you very much for the suggestions. I will check out these resources and play around a bit more.