updating complete table with excel file

[bg18461]

I am trying to update a complete table with an excel file. Each month the table data gets updated and I do not want to append any data. I simply want to replace everything in the table with certain columns from the excel file. I do not use any keys as they each have different account numbers.

I could either import certain columns from the raw excel file, or I can use a macro I created in excel to delete the unecessary columns and import the whole spreadsheet into the table. There are 160 columns i need to import from the excel file.

The access table is called Customer_Table. The excel file is called CustInfo(MonthYear) of import.


Any help? I am still pretty new to access.

 

[namliam]

Use a delete query to delete your data from the table, then use an Import to load the information from the (unaltered) spreadsheet.

 

[bg18461]

I am not sure how to write the delete and load queries, and how do i import only the required fields from the unaltered excel spreadsheet.

 

[namliam]

Well you can make a normal query then in the menu find Query > Delete to make a delete query.
Find Query > Append to make a load/append query.

The 'problem' is that you must always load an entire sheet, however you dont have to load it to your target table. A commonly used way of importing this kind of thing is:
1) Import into a 'staging' table, the full spreadsheet
2) DELETE the content of your target table
3) APPEND, from the staging table to the Target table that information that you do want.
4) DELETE the content of your staging table

Add
0) DELETE the content of your staging table
To make sure you start with an empty staging table...

I hope you understand where to go, what to do. If not post back.

 

[bg18461]

Well, i am trying to this programatically (vba) so i am using this in my vba to delete the content of my staging table

DoCmd.RunSQL ("DELETE * FROM CUSTOMER_TABLE")

I am not sure how to then create this staging table and append via vba. Also if i import the raw spreadsheet there are like 286 columns so i normally get the error that it has too many columns of data. That is why i had to run a macro i created in excel to remove the extra columns first. That is also why i only wanted to import certain columns and not the whole spreadsheet as i did not need all the columns. This happens on a monthly basis and will be done by someone who knows even less then i do about access so i want to make the process pretty easy (via the push of a button.)

 

[namliam]

Both excel columns AND access tables can only hold 255 in number, not 286... That is unless things changed in newer versions beyond 2002 one that I am using.

Plus 286 columns, wow!

 

[Rick Stanich]

Excel 2000, 2003 has a limit of 65536 rows by 256 columns.
Excel 2007 over 1 million rows by 16384 columns

 

[gemma-the-husky]

you don't even need to write SQL

the easiest way to do these things is to write design queries.

just design a delete query, to clear your table

then just

docmd.openquery "mydeletequery"
currentdb.execute "mydeletequery"

-------
sql insert or update queries are very tricky to write, but designing one is trivial