Two Problems....

[yaaara]

Hi Folks,

I'm a bit new to the forums and I am looking for some urgent help please !!! :|

I have two issues as follows:

1) I have a table in MS-Access (2003), the data in which needs to be appended by 30K records on a daily basis. The data is present in an Excel file. I am presently using ADODB coding method and looping through the records to read them and insert them in Access from Excel file. But this is taking wayyyyyyyy long time to complete. Is there any quicker option available using which I could transfer the data in Excel to the existing Access table quickly? (using coding only please as I don't wanna give the users the tables to work upon)

2) Access doesn't allow the results of the transform... pivot query to be saved into a table like it does with the SELECT... INTO statement.. However, I have a requirement where I have two separate data sources (one in tabular format and other needs to be transformed using the transform... pivot query statement. Both results need to be merged together and displayed as a single result (grouped by the common field)... Is there any method by which it could be done?

A quick resolution would be highly appreciated :)

Many Thanks well in advance....

[OBP]

yaaara, welcome to the Forum.
Well the answer to both your Questions appear to involve more queries.
The first question would involve some simple VBA to Import the Excel Worksheet in to a Temporary Table which has an Append Query to append the data to your table (and an Update one if required) and then delete the temporary table.
The second question should be possible with a Query combine the data from the Pivot query and the tabular results, providing that there is a suitable "key" to join the data.

[yaaara]

Well the answer to both your Questions appear to involve more queries.
The first question would involve some simple VBA to Import the Excel Worksheet in to a Temporary Table which has an Append Query to append the data to your table (and an Update one if required) and then delete the temporary table.
The second question should be possible with a Query combine the data from the Pivot query and the tabular results, providing that there is a suitable "key" to join the data.

Dear OBP,

Could you please guide me through citing some examples as I am not too sure how to combine the data from the Pivot query and tabular results (there is surely a suitable key available). Also, I'm not too sure as to how to import the excel worksheet into a temporary table that has an append query to append the data to my table... probably, some examples would be helpful :)

Thanks a ton !!

[OBP]

It is a bit difficult guide you through the combining Queries without an Example of your data. Can you provide a zipped database with some dummy data in it that provides your Pivot & Select Queries that you want to combine?

Here is an Excel Workbook "Import Utility" database, currently it imports the Range "a1:co500" from each Worksheet in the Workbook. It is very easy to change the Range. Note that the Import utility uses the FileUtilities VBA Module.
To create the Append is not possible for me without knowing whether your Excel worksheet has Field Names and whether or not they are the same names as the table the data will be appended to.

[yaaara]

To create the Append is not possible for me without knowing whether your Excel worksheet has Field Names and whether or not they are the same names as the table the data will be appended to.

Hi OBP,

Thanks for the prompt response. Yes, my excel file does have headers and are in exactly the same format as they are in the database, so that's no problem at all.. all what i'm looking forward to is a way to append large volumes of data in an excel file to the existing access table..

[OBP]

Did you test the Excel Import VBA that I posted?
if it worked OK then create a Select Query Based on the Temp table it created.
Then change it to an Append Query (Main Menu>Query) and select your real table to append to. Access should match up the Field names for you.