Open an Acees Form from Excel

  • Thread starter Thread starter javydreamercsw
  • Start date Start date
J

javydreamercsw

Here's the situation:
I have an Excel Workbook related with an Access Database. I want to d
the following. Instructors will enter their ID in one field od th
workbook, push a button that will activate a SELECT * Tablename WHER
ID= 'the one entered'. I can manage all that but here's where I nee
the help. I need to open a form, in Access if posible, That will as
for the info of the new professor. How that can be done
 
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
When using Excel as a front end, think of MS Access as a boring old
Jet DBMS i.e. it provides data and nothing else. Forms and reports are
front end tasks, so if you aren't using MS Access as the UI you can
forget they even exist. Automating MS Access to show a form isn't
appropriate.

In broad terms, here's what you need to do:

• Get the user input within Excel e.g. via a cell/textbox on a
worksheet, the VBA InputBox function, a textbox on a userform, etc.
How? Google it.

• Create a 'stored procedure' in you database that will accept the
user input as a parameter and return the required rows based on your
SELECT query. How? Look up CREATE PROCEDURE in Jet/MS Access help.
Here's an example which takes two dates as parameters:

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

• Use a data access technology to run your stored procedure: MS Query
and ADO are the popular choices for Excel. How? Again, google it. As a
hint for MS Query, here's what should appear in the SQL window to run
the above procedure with parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}
 
Back
Top