Excel-Access link

Originally Posted by

I just wanted to know if i can make a form in excel (the same as
access forms), so when i enter data in excel form it goes to a table
in access ? I mean when i enter a value in a cell linked to access
table, it goes to table and when i enter another value in the same
cell the value goes to the next record.

Originally Posted by

<farha...@gmail.comwrote
>
>
In fact, Excel uses MS Forms, which are quite different from Access Forms,
and they are not interchangeable. MS Forms can be used, with a little
hoop-jumping, from Access but Access Forms work better (no surprise) in
Access.
>
Larry Linson
Microsoft Access MVP

Originally Posted by

>
Thanks alot, but the proplem with me is that I have data officers who
don't know access at all so i want them to enter data via Excel.
>

Originally Posted by

Hi Farha,
>
This is something that I have had to play with as well over the last
short while. The only way that I can tell you that works is to create
your forms with the necessary controls in Excel, and handle your
database connections through ADO.
>
At the moment I am creating a "macro" for excel that has a backend
based in an MDB. For any read operations I use disconnected
recordsets, and only create a write capable connection for the brief
moments when I need to commit data to the database. I also use
transactions for all data updates.
>
You will have to think carefully about your forms and their design. As
others here have quite rightly posted they are different to Access
forms and are not interchangeable. To help a little in handling data I
generally use some declared (global) variables for anything that is
related from form to form or sub to sub. I also make sure to include
as much error handling code in this as possible because I have found
that Excel does not always play nicely when working with ADO.
Especially make sure to clean up / dispose of your variables with
connection objects and recordsets. I have had afew instances where
Excel simply wont let the user exit, and upon checking the code I find
I have missed cleaning up a variable - I fix it and the problem goes
away.
>
It can be done, but be warned that it is a slow and painful method to
achieve a goal. If you want to cheat a little you could design you
application in MS Access and simply have an instance of Access created
inside Excel to run what you have made. The user will really only see
the forms that you have created, and as long as you give the user a
clear method to exit the Access application they will return cleanly
to excel. You might want to give the following code a try, it could
get you started...
>
Private Sub GetAccess()
Dim MyAccess as object
Set MyAccess = CreateObject("Access.Application")
MyAccess.OpenCurrentDatabase "C:\.......enter the path to the db
here"
MyAccess.DoCmd.RunMacro "Name of Macro"
MyAccess.Visible = True
MyAccess.DoCmd.RunCommand 10 'acCmdAppMaximize
MyAccess.DoCmd.RunCommand 11 'acCmdAppMinimize
MyAccess.Quit
Set MyAccess = Nothing
End Sub
>
I hope this helps :-)
>
Cheers
>
The Frog