Import excel to append to a linked table 3

[Jedi420]

I've currently been assigned with the task of maiking our DB be able to export a table to a spreadsheet, whereupon people would then be able to update records at home. They would ideally then return to work, and be able to import the spreadsheet to be appended to the table that it originated from. The thing is, it doesnt seem that you can import a spreadsheet to be appended to a linked table. I havent looked into the programatic solutions as Im just trying to get the understanding of how one would go about this manually.
If I go to Get External Date -> Import... and follow the wizard and try to append the spreadsheet to an existing table, there are none available in the dropdown box. Does this mean that to append an imported file to a linked table that I would actually have to import into the file that holds the 'real' tables or is it possible that I'm just overlooking some mundane detail (I always do that). Any suggestions or advice anyone would have would be greatly appreciated.

-Jedi420

 

[BillPower]

Hi Jedi420,

I have a utility that does this at the click of a button, at:

http://www.wpower.fsbusiness.co.uk/

The file to look at is Import Multiple Excel Worksheets into Access.

Read the attached Help File to see how it works. I think it covers almost every type of Excel Import.

Good Luck

Bill

 

[Jedi420]

Hey, thanks ... I'll check it out.

-Jedi420

 

[Jedi420]

Billpower,
In your help file, it explicitly says

"If the table that would be replaced/deleted is a linked table, the import procedure  will not delete/replace the table. A message box will inform the user of this and the import will terminate.

The table that I wish to append to is a linked table, so I'm thinking that your utility will not work for my specific scenario.


To everyone,
Please do not consider this thread closed ... I am taking billpower's solution in the strongest consideration but still wish to know how this can be done, if possible, programatically. Thnx.

-Jedi420

 

[ShelDoyal]

Just a though...have you tried importing the main spreadsheet into Access instead of linking to it. Then link to the spreadsheets containing the new information. Then use the append query to append the new data. You could then export the updated file to Excel. Not the quickest method, but I usually do it this way. The instructions are in the help files for Access. Hope this helps.

 

[ShelDoyal]

Just a thought...have you tried importing the main spreadsheet into Access instead of linking to it. Then link to the spreadsheets containing the new information. Then use the append query to append the new data. You could then export the updated file to Excel. Not the quickest method, but I usually do it this way. The instructions are in the help files for Access. Hope this helps.

 

[BillPower]

From the Switchboard select: Import Worksheets using Sheet Names.

Make a Selection from the Import Type

From Imported Table Name select Existing Table.

Check or UnCheck Has Field Names.

Uncheck Replace Existing Table.

Select an Existing Table Name.

As long as the columns in your Worksheet(s) are the same as the Linked Table, this should work 100%

Bill

 

[Clandon]

You cannot append, edit or update any linked table in Access. You will need to import the table first and then append or run an update query to the table. I have run into the problem too many times to count. A linked file is just not an updateable table. Hope this helps.

 

[BillPower]

Hi Clandon,

Sorry, you are wrong. So wrong.

Bill

 

[Jedi420]

No, I think he is right in a certain sense. There is no way to import an excel spreadsheet to immediately be appended to an existing linked table. You can, as suggested, import the spreadsheet as its own table, then run an append query to join the two. If you know I am wrong, please explain how, without using switchboards, I can programatically import an excel spreadsheet and append it to an existing linked table, please let me know.

I do not use or know how to use switchboards so I'm not sure as to whether Billpower's solution will work or not. Maybe someone who reads will find it useful.

-Jedi420

 

[BillPower]

I haven't a clue how to prove this but as far as Access is concerned a Linked table is a table. The linked table will be treated exactly the same as if it is an "embedded" table.

Will somebody else please back me up on this, unfortuneately I'm not the best at explaining.

Bill

 

[Jedi420]

OK,

Here's how I know that something is up. My whole front end is just linked tables (of course), but to test this theory out I create two tables, named table1 and table2, so that they are directly embedded in the front end. Now, I got to Get External Date -> Import... and I follow the wizard until it prompts me for where I would like to store the spreadsheet. I choose 'In an existing table' and when I click the drop down box, ONLY the two embedded tables show and NONE of the linked ones. I mean, unless I am doing something terribly wrong, I think that is significant evidence to show that if you do want to import a file and append it to a linked table, you must go about it another way. Unfortunately, I know no other ways.

I truly appreciate everyones participation in this thread and perhaps in the end, we will all come to some agreeable conclusion. If you have anything to add ... POST IT! Until then, I'm going to begin implementing the only solution that I have available to me ... to import the spreadsheet as its own table and then just append that table to the end of the table in which I want append to. Then I'll just delete the table that was made from the spreadsheet. I look forward to anyone's thoughts on the subject. (^_^)

-Jedi420

 

[BillPower]

Hi Jedi420,

You're obviously not someone willing to experiment a little bit. Why don't you link a table or two to my demo DB. This will show you that appending linked tables is possible/normal.

Experiment, the demo shows all the code you should ever need to import workbooks.

Signing off now on this one.

Good Luck

Bill

 

[Jedi420]

Appreciate all the help, billpowers. I just really dont have time to experiment when I cannot justify it. If I cant do it manually, I dont believe that you can do it programmatically. But thank you very much for trying to help ... I really do appreciate it. Cya around (^_^)

THIS TRHEAD IS STILL OPEN.

-Jedi420

 

[BillPower]

Hi Jedi420

There's a bug in the Import Wizard. Didn't want to say it because now I'm open to all sorts of criticism.

You can do this programmatically.

Hopefully, when you have as much time as it's taken me to type this note, you can come back and say I was right.

Bill

 

[Jedi420]

Oh ... a bug ... well that sort of changes things ... and you knew this the whole time? For shame for not sharing your knowledge, billpowers :-D Can you please refer me to the source where you found this out from? Perhaps the words, "you were right", in the proper context, shall appear in my next post.

-Jedi420

 

[BillPower]

Hi Jedi420,

I can't, as far as I know the bug is undocumented.

But believe me, if you follow my instructions above, using the demo you will definitely be able to import a workbook to an existing linked table.

What more can I say. Stay at work an extra few minutes tonight to try out the Demo.

Bill

 

[Jedi420]

I found documentation for the bug ...

http://support.microsoft.com/defaul...port/kb/articles/Q258/4/29.ASP&NoWebContent=1

... as far as I can tell, it seems to be just a bug with the wizard, so perhaps it is possible to do this progamatically and Billpowers is indeed correct, as he so believes. I'll post again with my findings.

-Jedi420

 

[Jedi420]

For anyone who runs into the problem presented in this thread, you can do this programmatically using the command:

DoCmd.TransferSpreadsheet

Seems like an awful lot of talking for just this simple function, eh? However, this will not work for me since I have to export/import data that is made from a query consisting of two tables. So, I still believe that I will have to import the spreadsheet as its own table and open its recordset and add each field of the new table to the record in the destination table where it needs to go to (all through VB, of course).

Thanks for all the help and participation everyone, especially Billpowers. I'll give you a star for your efforts. Hope this thread clears things up for people who'll run into this problem in the future. Cya! (^_^)

-Jedi420

 

[judgehopkins]

The problems set out in this thread have been documented elsewhere.

Recordset not updatable after minor changes:
thread700-604183

mentioned here:

http://www.dczaland.com/appinatt/?act=ST&f=67&t=623&

slow using linked tables to SQL server:
thread181-600485

I am a newbie, so forgive me if I ask if I cannot append, edit, or update a linked table in Access, then what is the purpose of a database?

What if I wanted to give all my employees a raise?

Thanks for listening!

"The nice thing about being a celebrity is that when you bore people, they think it's their fault." - Henry Kissinger