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

 

[Jedi420]

no, you can do anything with a linked table that you can do with an embedded one. I've always known that but my problem was with the importing wizard. Theres a bug in it (the link is above). Other than that, just think of linked tables as embedded tables. They just load a little slower.

-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[judgehopkins]

Thanks, jedi240

"If the people don't want to come out to the park, nobody's gonna stop them." - Yogi Berra

 

[JaneB19]

Hi, I've just been directed to this thread, I had started one very similar on Monday in a different forum!

This is really just to Jedi420, but if anybody else has the answer feel free to comment. I'm sure you will if the replys to this thread are anything to go by!

How did you get on with the TransferSpreadsheet function? I had found this solution when hunting through the help file, but I've had a problem with it's range argument when using the function in a macro.

My spreadsheet is set out in a form formate, and therefore the ranges of data are spread around the spreadsheet. Apart for creating a macro for each range of data i.e. 1 macro for data from B8:B14 and another macro for G10:G16 then 1 final macro which runs all the macros at the one time. That worked fine apart from all the data was imported into the one column in the table unless the range was like C6:E10 where the values from the C column in the spreadsheet were imported into the 1st column of the table, the D column was imported into the 2nd column, and the E column into the 3rd column of the table.

Basically, does anybody know how to solve this problem with the TransferSpreadsheet function?

Thanks
Jane

P.S. I've put my thread in here as it's related to Jedi420's problem? A sort of continuation

Jedi420 - Hope you don't mind!

[PC2]

 

[Jedi420]

Hi JaneB19,


Early on in playing with the TransferSpreadsheet function, I was trying to see what I could and could not do witht the range argument. I wanted to be able to use the wildcard (*) character in my ranges which would give me some flexibility. However, in all the documentation that I've looked at and what not, it seems that you can only give it a straight forward range, i.e. C1:E10.

If your ranges are all over the place, then it may be best to just run multiple TransferSpreadsheet's. I have a question though ... Say you had to run 3 TransferSpreadsheet's, and you wanted all that data to go to the same table, wouldnt each succesive call of TransferSpreadsheet just overwrite all the data that the previous calls put there? If so, then you may want to think about for each separate range of data you have, import it into a different table. Then when you imported all the stuff you need, run a INSERT INTO query to append add all your records to the table they are supposed to be in. You can just delete all the tables that help the import data once everything is finished. Perhaps I understood you wrong, but maybe this'll help (^_^).


-Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[JaneB19]

Hi Jedi420

What happens at the moment it that, I think, because all the macros are started by the one macro, it just goes through each and inserts the data into the table. They do not over-write each other, but all put their data into the 1 column of the table unless the range is like B8:E10 when it puts each column in the spreadsheet into a seperate field in the table.

I think I'll try your idea of inputing the ranges into seperate tables and then merging them!

I'll let you know how I get on.

Thanks
Jane

[PC2]

 

[ad2]

I don't know if this will help but I've used an Update query to do something similar. I have an Access linked db to our production Sybase db

I made sure my Excel spreadsheet had the same field names for my columns as my Access table. Imported the excel file as a new import table.

Original table: Company
Import table: Company_imp

Then the update query would be:
Field: Org_Name
Table: Company
Update To: [Company_imp]![Org_Name]

etc for each field.

Wouldn't an append query work the same?