How to create a read/write connection to Access from Excel

[prgmrgirl]

Hey guys,

I've been trying to figure out how to do the above with as little coding as possible. I thought the data connection using the Date->Import External Data was a two-way connection but its not. Basically my users want to use Excel as the front end to enter data into an Access database.

Anyone know a quick way (or even a not-so-quick way) to do this?

Thanks a bunch!
prgmrgirl

 

[xlhelp]

Can you not just have Excel as a table in Access? Am I missing something with your question?

Member- AAAA Association Against Acronym Abusers

 

[prgmrgirl]

Hi there, thanks for your super-fast response! Wow!

The only problem with that approach is that the data in the Excel spreadsheet is completely un-normalized and we want to be able to normalize the data so that we can easily query it, etc. We wanted to separate the presentation of the data from the actual data itself. Not sure how much change the users can tolerate to the way the spreadsheet looks.

Does that make sense?

 

[fneily]

Probably get more responses in the Access forums.
As you know that Access tables are normalized, an enjoyable surprise to me, why don't you just do an updatable query which would bring together the needed tables and create an Access entry form off of that? You can make it look nice and pretty for your users. It would be easy to convince your users that the data entry part would be easier in an Access form than an Excel spreadsheet. From your post, it seems the Excel part is unnecessary. Your form can even be in "datasheet" view which looks like an excel spreadsheet.
If not, then get ready to code.

 

[prgmrgirl]

LOL Thanks for your response, fneily!

I wish it was as simple as data entry. The spreadhseet is huge and allows the users to see various stages of an ongoing project (I know, I know, but no one listens to me about what tools they should use). Unfortunately this spreadsheet gives them a level of functionality that would be hard to duplicate in Access. Ideally, we'd like to just use Access as a simple data store.

Is it possible to create linked tables off of non-contiguous data (i.e. columns B, F, G would be one table, etc)?

Thanks!
prgmrgirl

 

[Welshbird]

You may be able to do that by naming ranges in the Excel sheet and linking the named ranges within Excel, but that does leave the data in Excel rather than in Access. The data import would give you the data in Access, but then not access to it from Excel.... and I'm not even making sense to myself right now.... so I'll stop.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[prgmrgirl]

ROFLMAO!!

Yeah, at this stage of the game, I'm not too picky about a solution. I'm going to try to link an Excel range made up of non-contiguous data and see if that works. I'll let you guys know.

Thanks for the help guys. Much appreciated!

prgmrgirl