Access query using Excel cells

[bmoremu]

This is probably easier than I'm making it out to be but I'm having trouble doing this: A user enters an account(s) on a spreadsheet, when they submit the file with the account(s), I want the macro to query an Access database to make sure that account exists, if not, I'll have an error message pop up. Can someone fill me in on how to do this in VBA/DAO?? Sorry, I'm pretty experienced in Excel VBA, but still learning the link from Excel --> Access, or DAO. Thanks in advance!

 

[PHV]

Feel free to do a keyword search in this forum for DAO. You'll get many interestings threads covering your issue.

Hope This Help
PH.

 

[SkipVought]

Hi,

Another technique to use is on the Workbook Open event, for instance, refresh an MS Query on a worksheet that queries your Access table.

Then reference that List to validate the user response.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bmoremu]

Thanks, but I don't think I can load the list into Excel because it's a list with 20,000+ accounts in it and up to 50 users could be accessing the database at a time.

 

[SkipVought]

Excel has a maximum of 65,536 rows.

The strategy is that ONE query to retrieve 20,000 rows will be more efficient than 1000s of queries, each retrieving one row.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bmoremu]

Ahh, I see what you mean. Right now the account validation list in on a remote server with a slo

http://wwwwww

connection so retrieving 20,000 rows would take forever, not to mention if multiple users were doing it. I think we'll implement the database on the LAN, atleast for starters, and then, yes, your suggestion would make sense. Thanks!