Updating access database from excel

01 May

Hi, I have an Excel spreadsheet which extracts and formats info from an Access DB for reporting purposes etc., it refreshes on open and is available to all users.However, when the excel file is open the Access DB opens read-only and the 'Disable Shift&Click' Function fails to work.Excel makes it easy to store data, perform numerical calculations, format cells, and adjust layouts to generate the output and reports to share with others.Advanced features such as subtotals, power pivot tables and pivot charts, analysis toolkit, and many templates make it easy to accomplish a wide range of tasks.This database started out as a simple list in Excel, but now we might want to add more complex information, such as who uses the equipment or its maintenance history.

updating access database from excel-9updating access database from excel-7

Has anyone else seen this problem and is there a way around it?

There are some key differences between the two we will discuss later in the article.

As linked tables go, Share Point Lists are definitely a horse of different color. If you cared to peek inside the SQL Server database that Share Point uses to store Lists, you would realize that all list items are stored in one giant sized table with columns names int1, int2, int3 and so forth.

Microsoft Excel is great at a lot of tasks, but it has limits as a database manager. If this were a perfect world, I'd do everything in Excel: budget, write, balance my checkbook, answer my email.

If your Excel data worksheets are beginning to get out of hand, it may be time to call in a little help from Access. Unfortunately, as nearly perfect as Excel is, it isn't the best tool for every task.