Excel import using VBA

[catguy]

I have a dilema.....


I have "inherited" a large database that requires ALOT of manual importing/copying/appending.... you get the idea. I would like to automate the daily updating using vba, but am fairly new to vba.
The current process.... Save data as an excel file (i.e. 1A1temp.xls, 1A2temp.xls, etc.) which are set up as linked tables from a specific folder. Run a query to format (because the system the data is coming from was reconfigured mid-year, so a main data type changed from 9 digits to 12 digits).
Select any new records from the format (based on date) and copy them, then paste append them into the existing table, which most of the queries are based off of.
and finally.... run an update query to update any existing records that may have changed since the last time this was done.
Confused yet?
Basically, the main tables (1A1tbl, 1A2tbl) are existing tables that data is added to each morning. This data can, and often does, change throughout the year, so to be accurate, any existing data must be updated each day.
There are no primary keys on any of the tables. Each "xxxtemp.xls" file has the same layout and fields as its corresponding table.
The data that is saved in excel is only from the previous day, month or 3 month period.
Being new to vba, is this something vba would be good for, and if so, how?
Any and all help; would be greatly appreciated..... Thanks

[maximus]

on what basis do u run the updates?

Well this is what I understand from you post. data is entered in excel sheet. That has to be imported into an access database in tables.

Now here are some assumptions:
1) Data is added in excel sheet everyday data is also modified when required.
2) The new data is appended in the tables and the modifications made to existing data is Updated.

My question is well I can be entirely wrong here but will the work not be more simple to just clear of the entire table and append all data into the tables a fresh periodically.

More over u mention that there are no primary keys I am curious as to how do u actually update the records without a unique identifier of records.

well regarding the entire task of importing data into the tables that is very much possible with access and very easily.

if u will clarify my queries I will be able to help u out with a solution.

[catguy]

Thank you, Maximus, for the quick reply.
Let me try to be more specific. We are exporting data from SAP to an excel spreadsheet. Unfortunately, our security protocols are such that we cannot do much data analysis within SAP, which is frustrating, so we need to import to access. Here are the instructions I was given: 1) Once spreadsheet is created, save as "1A1temp.xls" and click yes for overwrite... 2) Open access db and run query "1A1Format".... 3) Scroll to bottom and highlight records for previous day and copy... 4) Open table "1A1tbl", choose Edit / Paste Append, save and close table and query... 5) Run query "1A1Update".
Now, I haven't spent much time working with this db, so I do not fully understand why it was constructed the way it is.
The "1A1Format" query does one thing only, it adds zeros to any 9 digit number in a field named "Lot", so that all records in that field are 12 digits.
The records in the "Lot" field are used as the unique identifier, but I'm assuming because there can be duplicates, this is why they did not select a primary key. ("1A1Format" uses the records from "1A1temp.xls, which is a linked table)
The "1A1Update" query is run so that records previously appended to "1A1tbl" can be updated if changes were made since they were first appended. (he update query uses the records from "1A1Format")
Clearing and appending the table is not a viable option. The quantity of records that would need to be exported from SAP would be so much that we would get runtime errors constantly, and alot of e-mails asking why we are doing this. So the data is gathered for the previous 3 months and updated.
So, to summarize, 1) the "1A1temp.xls" file is overwritten each time it is updated... 2) New records are "Paste/Appended" to the "1A1tbl" table... 3) existing records in the "1A1tbl" table are updated.
The instructions must be repeated 3 more times for other tables, so I think you can understand why I want to automate this???
Thanks again.....

[catguy]

OK.... now I feel like an idiot!
After finally finding some time to study this DB, I found that the update queries had incorrect joins.... I was wondering why they were doing alot of copying and pasting prior to running the updates.
So, there is a main form that has many command buttons which open other forms and data sets. I added a button to this form named "UpdateDB" and added this code to the OnClick event:

Private Sub UpdateDB_Click()

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "QA32UpdateQry"
db.Execute "QM15UpdateQry"
db.Execute "QM11UpdateQry"
db.Execute "MB51UpdateQry"


Set db = Nothing

End Sub

I have also added a text box in the footer of the form named "txtLastUpdated" where, after the "UpdateDB" command button is clicked, I want the current date and time to display, kind of a time stamp to show when the last update was performed.
Being fairly new to Access VBA, would that need to be a new event, or , being it is triggered by the previous event, would it be better to try and fit it into the above code, and if so, where?

[weekend00]

you can not save the last update time in a form, data in the form disappear when the form closes.
you need to modify the design of you table to add a field to hold the last update time and link to the form.

[pkstormy]

If your goal is to automate the importing of several excel files in a folder, you can use the attached.

[catguy]

Thanks for the replies folks.
I did add a field to my table and everything is working fine.