Solved

Excel Button > Access Form > Goto Record

Posted on 2003-11-13
7
316 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:Kinsy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 63 total points
ID: 9738422
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
 

Author Comment

by:Kinsy
ID: 9738465
This is the thing; i dont think Access V2 even uses VB!

Whether or not VBE can control Access i dont know!
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9738509
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 62 total points
ID: 9739100
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
 

Author Comment

by:Kinsy
ID: 9740414
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
 
LVL 39

Expert Comment

by:stevbe
ID: 10025538
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question