Excel Button > Access Form > Goto Record

hi

Ive been asked if it is possible to create a button in excel that when clicked, takes data from another field (containing a job number), opens up Access, goes to a form and jumps to a record with the job number in question.

There are 2 problems with this that need to be considered:-

-The database requires a username and password.
-The Access version is absurdly ancient! (Version2).

Im assuming ill have to create a macro rather than a VB Command button in excel but as of yet i have no idea what to do with this. The Excel version is 2000.

Thanks

Ross
KinsyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
You can run VBA code from an Excel command button.

You can write VBA to open an Access DB (password etc. included) and go to a record.

Firstly you will need to add the Access 2.0 object library in the VBA ebvironment. Then you will be able to access methods and properties of the database.

Having said that, I've never done VBA for a version of Access that old, so you will just have to try it and see.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KinsyAuthor Commented:
This is the thing; i dont think Access V2 even uses VB!

Whether or not VBE can control Access i dont know!
0
nmcdermaidCommented:
Just try going into your VBA editor in Excel (ALT-F11), press Tools then References then see if you can find Access 2.0 in the list

If you can then you may be able to use VBA to run Access 2.0 programattically.

If you can't then your other options are:

1. See if Access 2.0 will take command line options, if so you may be able to tell it to do what you want... however thats a long shot.

2. You may be able to linke to the tables in Access from your Excel, then jump to the data in Excel rather than Access. You may have your reasons for not doing this though.

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Arthur_WoodCommented:
what you will be doing, however, is NOT ruinning the Access form from within Excel.  Rather, you will be using EXCEL VBA to execute e query against the DATA in the Access Table, and then after you have retrieved the data from Access, displaying that data in the Excel spreadsheet.

Why are you still using Access 2.0?  That should be upgraded to a more recent version of Access as soon as possible.

AW
0
KinsyAuthor Commented:
Its not up to me to make the decision to update Access V2. Its still in use because a vital business db was built in it and has been in use for many years.

The app i have been building is in Access 2000 however and a future project may be to update the db from v2 to 2000.
0
stevbeCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: nmcdermaid {http:#9738509} & Arthur_Wood {http:#9739100}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.