Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2019, 01:11 AM   #1
bigmac
Newly Registered User
 
Join Date: Oct 2008
Location: wales
Posts: 278
Thanks: 72
Thanked 0 Times in 0 Posts
bigmac is on a distinguished road
Question excel to table

hello all, can you help please, i have a datbase with a link to an excel sheet,(linked so any updates on the excel sheet is shown on the link in the database).

question can i make a table from this ? if so will the table auto update from the excel sheet,

the reason i want to do this is that on the excel sheet there is no primary id, so if i can put it in a table i can add one

bigmac is offline   Reply With Quote
Old 08-16-2019, 01:23 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,596
Thanks: 59
Thanked 2,435 Times in 2,335 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: excel to table

to create New Table from your Excel linked table:
Code:
currentdb.Execute "select sheetName.* into New_Table from sheetName;
when the "New_Table" is created, you can add new record to it:
Code:
currentdb.Execute "insert into New_table select sheetName.* from sheetName;"
unfortunately the New_table is not auto update, you update it manually.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 08-16-2019, 01:57 AM   #3
bigmac
Newly Registered User
 
Join Date: Oct 2008
Location: wales
Posts: 278
Thanks: 72
Thanked 0 Times in 0 Posts
bigmac is on a distinguished road
Re: excel to table

thank you arnelgp, can i get htis to auto update as well?, if so how

bigmac is offline   Reply With Quote
Old 08-16-2019, 04:28 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: excel to table

Once you import the spreadsheet, it is no longer "live". Every time you want an updated set of data, you will need to delete the rows from the table and then run the append query again.

What exactly are you trying to do? You said you wanted to add an ID, The process I described could end up assigning a different ID each time the spreadsheet is appended depending on how the Excel sheet is updated.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-16-2019, 05:54 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,093
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: excel to table

Here is your problem.

1. Linking to Excel as an external table, you can see changes to the table when they are made. I.e. manual updates to the table are visible within moments after the person updating the table exits/saves or manually saves the changes. But you cannot update the Excel worksheet as a linked table from Access because that particular kind of table is linked Read-Only.

2. You can make a copy of the linked table to a local table in your database, but that is then a point-in-time copy, a snapshot. The linkage would be updated but that table took on an independent life of its own the moment the copy was complete.

3. There is no auto-update function in Access or Excel that would allow synchronization of the two entities (worksheet vs. table). If you want to know about changes to the Excel worksheet, you must examine that sheet and compare it to the Access snapshot table to find differences. This will be a piecemeal operation rather than a wholesale operation. There will be no simple single query to do this. A sequence of queries MIGHT do this, but you might be looking at some complex VBA code to assure proper results.

4. You COULD wipe out the table and re-import the worksheet, but you lose whatever data was in the Access table beforehand. This might not be so bad depending on what you were doing, but if ANYTHING in the Access table has gained dependencies (like a records in a child table) then to wipe out the table and import a new version runs the risk of creating orphan records or of violating relational integrity on such records.

To know the best course of action here we would need to know more about the data flow of this particular piece of the business to which it relates.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-16-2019, 06:17 AM   #6
bigmac
Newly Registered User
 
Join Date: Oct 2008
Location: wales
Posts: 278
Thanks: 72
Thanked 0 Times in 0 Posts
bigmac is on a distinguished road
Question Re: excel to table

hello The_Doc_Man,
what i am trying to acheive is this, in an existing datbasethaty we are using in work, one of our team is using an excel spreadsheet, he likes the way we do searches in the database and has asked can we link his sheet to it (this i have done) but i need place a primary id to it , i thought if i make a teble out of it and then add a primary key then i can do better searches on a form with it, or dont i need this ?
bigmac is offline   Reply With Quote
Old 08-16-2019, 06:45 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,024
Thanks: 40
Thanked 3,578 Times in 3,456 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: excel to table

not essential to have a unique ID - just depends on what you want to do. Excel data is seriously denormalised so depends what you want your search to return.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-16-2019, 04:37 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: excel to table

Never under any conditions modify a set of data from two perspectives. You can't have Excel updating data in the spreadsheet and your Access app updating data that came from the imported spreadsheet. Reconciliation will be completely manual since you will have no way of knowing which set of data is the master.

If you want a uniqueID, put it in the spreadsheet and write a macro to generate the id each time a row is added. Although, we still don't think you need an ID. However, if it turns out that you want to keep extra data in the database, having a unique ID in the spreadsheet will allow you to keep an Access table that you can link to the spreadsheet via the uniqueID.

Best solution - get the data out of Excel and put it in Access. Make forms and reports and queries so that the user doesn't even know he is working with Access since he is seeing a finished product rather than tables, queries, and code which he won't understand and that is probably why he is reluctant to give up his Excel. He is afraid he won't be able to figure out how to do what he wants if he has to do it in Access.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-16-2019, 09:49 PM   #9
bigmac
Newly Registered User
 
Join Date: Oct 2008
Location: wales
Posts: 278
Thanks: 72
Thanked 0 Times in 0 Posts
bigmac is on a distinguished road
Re: excel to table

thank you all for your advice, I will try it without the unique id first
bigmac is offline   Reply With Quote
Old 08-19-2019, 03:08 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: excel to table

Putting the unique ID in the spreadsheet is good practice if you are going to keep that as your "real" database.

At some point in time, you will need to wean your Excel users off their spreadsheet and get them to do their data maintenance in a database. You can still create exports for them that they can use to slice and dice and make reports that you haven't yet implemented.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-19-2019, 06:24 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,093
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: excel to table

Concur with Pat. As usual, she's right on point.

You must eventually establish a definitive data source that is your "authority" for anything that relates to this project. Having distributed data in multiple spreadsheets is OK if and ONLY if the copies are known to NOT be definitive or authoritative.

This is an important concept in databases - authoritative sources. When you have two sources and one says X but the other says Y, you have just incurred extra expense in the ugly process of data reconciliation. Therefore, you save your company time (=money) by saving them from the need to stop what they are doing in the future so they can decide whether X or Y is correct. "An ounce of prevention is worth a pound of cure" as my old Mississippi aunt Hattie used to say.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Excel into a table, overwrite the data of the fields imported from Excel in Ta bharatmvs Tables 6 03-20-2015 03:40 AM
appending table in access from excel, excel file name change everyday with date gcgc2000 Queries 1 12-28-2012 04:21 PM
Import Excel workbook into Access table using Excel's format (formulas, values, etc) captdkl02 Modules & VBA 1 12-17-2012 09:03 AM
import multiple excel files with FILTERED Excel spreasheet into an access table WuJu Modules & VBA 5 10-31-2011 04:38 PM
Export Access Table to Excel- with the date as part of the new Excel file name WinDancer Macros 10 01-22-2008 01:10 PM




All times are GMT -8. The time now is 01:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World