Command and pass value to Acces from Excel

[Mmattson]

I have an Access DB where I maintain the metadata for data maintained within Excel spreadsheets (I have good reasons why it stays in Excel). I would like to be able to open the Access DB and go directly to the record associated with this piece of data using a VBA input box.

For example...if I am working in Excel, I want to call a module that prompts me to enter my unique identifier for the Access record. I type it in, hit enter and Access will open and go directly to the record with this unique identifier.

Does anybody have a module already designed for this type of activity?

 

[Calvin]

probably not but you can probably solve this simply enough.

Start by adding a new table to your access database with two fields in it, one being a unique id field that you populate with a "1" and the second being the field that you insert/store the value that you aquire from you Excel input box method.
With your Excel input box method, use ado to connnect to the mdb file and insert the data that the user entered in the Input box into the new access table.
then in access have the startup form or Sub Main process read the value out of the new table and open the form with the associated record.

This is just the basic idea, you will need to be familar with ADO, Shell execute or CreateObject, understand how startup forms or Sub Main works, Dlookup, and SQL to complete this.
If you understand the basic idea of what I'm explaining, you should be ok, if not then search this forum, read your help files and then ask your question, if you get lost.

 

[adam_fleck]

Example

Here's a zip file with a spreadsheet and an Access 2000 database in it.

I've been doing the sort of thing you're talking about for a while and there seem to be two main approaches.

1) Get Excel to open Access, then open the application you want, then the form you want, then navigate to the record you want to see. It's like you were doing all this yourself, only the code does it for you.

2) Design a custom form in Excel, then connect to the Access database, get hold of the record you need, and display it on the custom Excel form.

The disadvantage of 1 is that I haven't been able to find a way of getting Excel to drop Access properly. Even when you exit from Access, Excel still keep a thread to it open - the database is still open by someone, and you have to use task manager to close it properly. This can be annoying if you need to use the database directly. Time was short, so I didn't try to solve this though maybe someone else here will be able to say how to do it.

One of the disadvantes of 2, is that if you want to edit your Access data on the Excel popup, you'll need to write routines that will post the changed record back to Access. This isn't too difficult, though, and once you get your head round arrays, you should find it fairly straight forward, if time consuming.

An advantage of approach 2 is that if you end up scaling up to SQL server there's less work to do (I hope, as I'm about to do this).

 

[Looko]

hi, Im trying to work the vba codes of Adam's but It won't work with my Dbase in Access, can some1 tells me why?

thank you

looko

 

[Looko]

I've fixed it . thanx