Transferspreadsheet

[batwings]

Hi there

I have managed to import a spreadsheet from Excel to Access using a command button on a form and adding the code below to the onclick event. And it works well but:-

The spreadsheet table1.xls I use is updated frequently! how do I go about dropping the data in the Access table "Test" but keeping the structure of the table and then re-import new data from spreadsheet table1.xls

Or Append then update although with 24,000 rows and 11 columns it might be quite slow tp try this.

Keeping in mind that in the "Test" table there is an ID field which is the PK so I would probably need to be able to shift the columns along 1 to the right to prevent the new spreadsheet data trying to populate the ID field with the wrong data.

Also there is one new column last on the right that I added to the test "Table" for comments It would be nice to keep but not neccessary.

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "Test", "C:\Test\table1.xls", True, " "
End Sub




Regards

Batwings

 

[DCrake]

Simple Software Solutions

Why don't you simply link the spreadsheet to the database using the get external data option form the file menu and specifying .xls type data.

CodeMaster:http://www.icraftlimited.co.uk

 

[batwings]

TransferSpreadsheet

DCrake

Sounds like a great idea, only one question though, is it possible to add other columns to the linked table from inside Access? I have 2 additional columns that are not on the input spreadsheet.

I was really looking for a VBA way to import/update/append now that I have the 1st set of data imported using the Import External Wizard.

I am not sure how to import 10 columns from Excel into my exisiting Table that has 11 Columns 10 + the ID PK. Everytime I try to import using the Transferspreadsheet() I get errors.

I guess it is the column mismatch and the fact that Access is trying to import my Column A into the ID PK column and I am not sure how to shift them over by one. I do not want to have to add an extra column 1st to the import spreadsheet before every import/update...

thanks
Batwings

 

[Moniker]

Don't import directly into the table with the PK on it. Setup a temporary table with the ten fields you want to input, do a Transferspreadsheet from Excel into your temporary table, and then do a separate append query from the temporary table to the permanent table with the PK.

 

[qdogfball]

Another point..

why do you bother with Excel updating and importing into Access?

Why not just use Access in the first place?

Have your users use Access to input information and then away you go with whatever you want to do with the data!

 

[batwings]

Moniker

It seems to be the best idea,I will go with that i'm just not sure how to purge the main table of all records before the transfer from the temp table to the 10+PK Table.

To answer qdogfball, the reason I do not use Access for the source data is that the spreadsheet is generated from another database that users update to, I am just interested in the output from one particular report out of that database that I manipulate thru access and produce my own custom report for our dept.

Thanks I think you have got me on the right track

Batwings

 

[Moniker]

To delete the records out of any table, it's this in VBA:

CurrentDb.Execute "DELETE * FROM YourTableNameHere"

In the QBE, just choose new Query, add the table whose content you want to delete, select all the fields (drag the asterisk into the grid), change it to a Delete query, and execute it.

 

[batwings]

Moniker

As I'm new to VBA and feeling my way here, how exactly can I use your suggestion?

Create the temp table, then import the spreadsheet data to temp table from the spreadsheet and then append the data to Test table..

What i mean is I am not sure how to use the

CurrentDb.Execute "DELETE * FROM Temp"
DoCmd.TransferSpreadsheet acImport, 8, "Temp", "C:\Test\table1.xls", True, " "
CurrentDb.Execute "DELETE * FROM Test"
Then append to Test from Temp

All from the click of a button?

It is the syntax and if I can use a combination of commands and functions together I am not sure of..

Private Sub Command0_Click()
Do this blah blah,
Do that blah blah
Do the next thing
And so on
End Sub


I must buy a book and will as soon as I get home..

Thanks again

Batwings

along with

 

[qdogfball]

Moniker

To answer qdogfball, the reason I do not use Access for the source data is that the spreadsheet is generated from another database that users update to, I am just interested in the output from one particular report out of that database that I manipulate thru access and produce my own custom report for our dept.

Batwings

Now I am REALLY confounded! Why would you have data come out of a database and back into one?

Seems like it would be better to link the data in the two databases and work from there.

 

[batwings]

qdogfball

The reporting from the other database does not give me what I need.
I have read only access to it but can export all the Fields I need in an Excel spreadsheet but still have to manipulate and filter the data to produce the report I require. All I needed was some help in how to Transfer the spreadsheet update append etc.. then it would be easy.

Just export the spreadsheet to the same folder everyday/week/month whatever and hit a button in Access to have the report I have already configured receive the data!

Thanks for your help!

Batwings

 

[Moniker]

If you're appending to table Test and Test already has data in it, you don't want to get rid of your already imported data (most likely). So, the order is:

1) Delete data in Temp as shown
2) Import into Temp
3) Append to Test

And yes, all under one button.

If you do want to clear the contents of Test everytime, then you were on track.