Excel-Access link

[farhaaad]

Hi everybody,

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.

I thank you people in advance!

[storrboy]

You should just choose which prgram to use. Access will allow you to
do all that within itself and with an easier time accomplishing it. I
don't think using Excel as a front-end is all that efficient.

[Larry Linson]

<fa******@gmail.comwrote

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.

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

[farhaaad]

On Mar 25, 11:45 pm, "Larry Linson" <[e-mail]:

<[e-mail]

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.

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

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.

farhaad

[The Frog]

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

[Keith Wilby]

<fa******@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

>
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.

Access is a database building tool, not an "application" in the same way as
Excel and Word are. If your users prefer Excel and Excel does the job then
let them use it. If, as I suspect, you have a multi-user requirement then
you could use Access with a form in datasheet mode and make it look just
like Excel. Result - everyone's happy.

HTH - Keith.
www.keithwilby.com

[farhaaad]

On Mar 26, 12:06 pm, "The Frog" <[e-mail]:

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

Thank you very much for you kind information, i m working on it and i
m looking for a positive result,

[The Frog]

No worries :-)

If you get stuck, I will help where I can.

Cheers

The Frog