Update table in access from excel

[Boss]

Hi,

I have a situation where my team (50 people) update excel sheet in their own
systems. What i wish to do is to get entire data in one master table in MS
access. I can do it daily by importing multiple excel sheets.

But i need to view the entire data at runtime. All i should do for this is
that link access table at the backend so that when the user submits one
record in his excel sheet record gets updated in the master table of Access.


I know this is possible but could not write the code find it in google.
please help me with the code, This would help me a lot in the project.

Thanks!
Boss

 

[Piet Linden]

Hi,

I have a situation where my team (50 people) update excel sheet in their own
systems. What i wish to do is to get entire data in one master table in MS
access. I can do it daily by importing multiple excel sheets.

But i need to view the entire data at runtime. All i should do for this is
that link access table at the backend so that when the user submits one
record in his excel sheet record gets updated in the master table of Access.

I know this is possible but could not write the code find it in google.
please help me with the code, This would help me a lot in the project.

Thanks!
Boss

Basically, create a link to ONE of the spreadsheets, (assuming they
all have the same structure).
If they're all in the same directory, you can use DIR inside a loop to
walk the contents of the directory, then use an append/update query to
insert/update the records.

Sure it's possible.... Open up a code window, Hit F1, and introduce
yourself to the help files. If you're expecting someone to write this
for you, send money.

 

[ryguy7272]

Have you ever seen Ken Snell’s site? KEN is pretty much the KING of
importing and exporting. This should answer most of your importing (and
exporting) questions:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This will probably do what you want:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpFolderFiles

Or this:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrsFldFiles



HTH,
Ryan---

 

[Boss]

Hi,

I am facing a new problem, With the data which is getting updated in table i
have prepared a form in ms access which give me counts of the activities.

As i save the ms access form it gives me a error: Database in state by user
"admin". Then i cannot update records using excel.

please help.
Thanks!
Boss