Solved Is a table needed question

[donkey9972]

I just have two questions I was curious about. Can you have all your data linked to an excel spreadsheet, stored there and eliminate the need for a table?

If this is possible how can I do this?

 

[theDBguy]

Ordinarily, a linked Excel sheet is read only. So, if you don't need to update the data from within Access, then a linked table is enough.

 

[donkey9972]

So when you link the table, is the data still stored on the table at all?

 

[theDBguy]

So when you link the table, is the data still stored on the table at all?

When you create a linked table, the data is stored in the data source. So, if you create a linked table from an Excel sheet, the data stays in Excel.

 

[donkey9972]

Well, I have tried to link the data to an excel spreadsheet and I accomplished that. What I am running into problems with is the data can't be updated through access and only updated in excel. Did I do something wrong?

 

[GaP42]

Well, I have tried to link the data to an excel spreadsheet and I accomplished that. What I am running into problems with is the data can't be updated through access and only updated in excel. Did I do something wrong?

Read post 2. The lack of data constraints in Excel make it a poor choice generally for maintaining the quality of data when used /referenced by a database app particularly if the Excel file is not well managed.
A thread with references/links to what you are proposing to do I think

Updating Excel spreadsheet from within the Access

I have VBA code to create an Access database from three Excel spreadsheets. This has worked successfully for a good while. One of the Spreadsheets, obtained from an external source, contains a header row above the column headings and two comments rows at the end. The user sometimes forgets to...

www.access-programmers.co.uk

 

[donkey9972]

Okay, thank you all for the clarification. I was just curious.

 

[theDBguy]

Well, I have tried to link the data to an excel spreadsheet and I accomplished that. What I am running into problems with is the data can't be updated through access and only updated in excel. Did I do something wrong?

No you didn't do anything wrong. That's exactly what I said was going to happen if you proceeded with that idea. Since you didn't specify your true intentions, I hesitated to provide any suggestions.

If you tell us what you're trying to achieve, someone here will be able to tell you how to do it.

 

[The_Doc_Man]

Part of the problem with using Excel in that way is that the cells of an Excel spreadsheet are independent of each other. If there IS a relationship it is expressed as a formula within the cell that is related to something else. Access tends to be more stringent. If you are careful with that spreadsheet you CAN make it work (see post by GaP42). However, Excel's security paradigm is different than Access's paradigm and thus you run into issues including being write-locked.

If you ARE sharing this DB with ANY OTHER PERSON, even if you get past the "can it be updated" problem, you will find that only one person at a time can have it open for update. That is because their security involves locking the WHOLE SPREADSHEET when anyone opens it for write. All other users will be READ-ONLY. It is generally not suggested to use Excel in this way if you are doing ANYTHING AT ALL that involves sharing. You didn't say you were doing that, but if the question comes up, you heard the answer here.

 

[CJ_London]

the data can't be updated through access and only updated in excel.

Depends what this actually means. You can use sql to connect to an excel worksheet and edit existing data, but you can't add or delete rows.

 

[Pat Hartman]

In general, this is a really bad idea. If you get over the update and sharing hurdles, it is still clunky and requires code to manage.

As the others have suggested, we can't provide viable solutions without knowing your problem.

 

[isladogs]

Depends what this actually means. You can use sql to connect to an excel worksheet and edit existing data, but you can't add or delete rows.

Not quite correct.
With the correct settings, you can add or edit rows in a linked Excel file but you cannot delete rows.
For details on how to do this, see

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

isladogs.co.uk

 

[Isaac]

I think CJ is right, and it's a matter of semantics. You can edit and add data, but not technically rows.
When you say "add rows" isladogs you mean add data, really - not insert an actual Row.

 

[isladogs]

Not sure it’s worth quibbling about that particular bit of semantics.

The point is that not only can you edit existing rows of data, you can also add data to new rows.
You can also delete all the data from an entire row but attempting to use delete record to delete a row isn't allowed.

 

[KitaYama]

And above all, you can have a linked table to an Excel file, and a form to add/delete/edit data to the file using VBA. (Excel Automation)
Just like when you have an unbound form. The only difference is instead of writing data to Access table, you add/Edit data in an Excel sheet.
You don't even need to refresh the linked table to see the changes. It's instantaneous.

 

[June7]

Another consideration is that workbooks with lots of rows get very sluggish.

 

[Pat Hartman]

AND two users can't be sharing the spreadsheet while Access "owns" it.