Select Excel File and upload into a table.

[Waddy]

Hello the dream team

I have a database that has over 600k records and will not append but I can export the data into a spreadsheet put in new records from a report that is ran into this spreadsheet and upload it back into the table for analysis using the wizard.

I have got the code to export the file but now I need to re-upload it, I know there's a wizard and it works but I want to sort it in a form.

Is there some code to open up the upload wizard function or can someone point me to a thread. I'm guessing this is VBA only.

 

[isladogs]

Can you explain all of that again please.
What does 'will not append' mean? And why is that the case?
What exactly are you struggling with - importing new data from Excel?
Not sure why you are exporting just so you can update then reimport

 

[Waddy]

Due to the amount of data when I use the append query to update the table it says the database is over 2GBs and not enough memory.
I wish to setup a form were you select the an excel spreadsheet and it uploads into an existing table.

 

[isladogs]

How big is the db if you compact it? How big is the append data?
Have you considered splitting the db and/or using a separate 'temp' database just for this purpose?

 

[Waddy]

I have compacted the database but still gives me the same response I have also done a front and back end approach again no look. The database when compacted comes to 134MBs.

I have no experience of creating a temp table?

Thank you for your time isladogs.

 

[isladogs]

Sorry but I'm still no wiser. I really can't get a handle on what you are doing.

134MB gives plenty of room for you to append new records unless you are adding hundreds of millions of records at once...which I doubt.
If that was the case, then you should upsize your BE tables to SQL Server.

Where is the data stored that you need for these new records?
Are you also overwriting existing records?

 

[GinaWhipp]

Hmm, perhaps it's a deceptive message. Do the Headers in the Excel spreadsheet use any wildcard characters or use Reserved Words?

 

[Frothingslosh]

And while you're at it, how about showing us the append query itself?

There's something seriously wonky if it's creating that much extra data - it's quite possible you have a bad join that's causing massive duplication of records somehow.

 

[Waddy]

Just an update, I managed to get the append query to work in the end

Another one solved!

 

[isladogs]

For the benefit of those involved in this thread and those who read it in the future, please explain what the solution was.