Transfer Data From Form in MS Access to Excel sheet?

101 100+

Hi
I am totaly new to MS Access.
I have one worksheet in excel in which I have data of company's inventory.
I want to know can I transfer data from a form made in MS Access to the Excel worksheet?
Can I use access form to fetch or update data in excel worksheet?
Can I use MS Access as frontend Or I have to use another frontend like VB?

Nov 28 '07 #1

Follow Post Reply

2904

Jim Doherty

897

Expert 512MB

Hi
I am totaly new to MS Access.
I have one worksheet in excel in which I have data of company's inventory.
I want to know can I transfer data from a form made in MS Access to the Excel worksheet?
Can I use access form to fetch or update data in excel worksheet?
Can I use MS Access as frontend Or I have to use another frontend like VB?

Have a look at how you LINK tables to an Access database.

(File...Get External Data....Link Tables...in the files of type dropdown select Microsoft Excel)

You will be prompted for the location of the excel file and which sheet you wish to attach as a table.

This may or may not do as you require overall it depends how you have your data organised in the spreadsheet. Remember we are talking tables here... many people treat excel as though it were a word processor with some layouts I've seen.

Another method is via 'Automation' but that involves using VBA code which I won't go into if you are totally new. It may be that simple attaching might suffice for you

Regards

Jim :)

Nov 28 '07 #2

billa856

101

100+

Thank You for ur help

I try that so it creat a table in access.So now i have to use this table right?
but if i want to connect it like, use VB as frontend and excel as backend.So can we do like that with Access as frontend and excel sheet as backend?

Nov 28 '07 #3

Jim Doherty

897

Expert 512MB

Thank You for ur help

I try that so it creat a table in access.So now i have to use this table right?
but if i want to connect it like, use VB as frontend and excel as backend.So can we do like that with Access as frontend and excel sheet as backend?

Well if you are a VB programmer all you are doing there is using Access as an itermediary which depending on your situation is probably not necessary I don't know!! you could interface directly with excels object model in VB and not use Access at all its a matter for you.

But if you are working IN Access ie: using its interface forms,reports and so on then you can create an access form based on an attached table and edit the values directly in the attached table

Regards

Jim :)

Nov 28 '07 #4