Update Access DB from Excel Sheet
Hey Experts!!
I have a slight problem and I'm really hoping someone can help me. About a month ago, I exported a query (below) from an Access 2003 DB to Excel. People have been updating it with newer values.
What I need to do now is update the same 3 tables with the updated information from an Excel sheet. I'm not sure how to go about doing that though. Do I use an "UPDATE" instead of "SELECT" statement? Do I do each table individually?
Thanks in advance.
I have a slight problem and I'm really hoping someone can help me. About a month ago, I exported a query (below) from an Access 2003 DB to Excel. People have been updating it with newer values.
What I need to do now is update the same 3 tables with the updated information from an Excel sheet. I'm not sure how to go about doing that though. Do I use an "UPDATE" instead of "SELECT" statement? Do I do each table individually?
Thanks in advance.
SELECT DISTINCT Client.ACT_no, Client.Client, Client.Address, Client.Address2, Client.City, Client.State, Client.Zipplus, Client.County, Client.AREA_Phone, Client.CatSub_ID, Client.Contact_no, Client.notes_id, Client.script, Client.instructor, Client.instructor_phone, Client.user_main, Client.last_user, Client.last_date, Contacts.*, Cat_Sub.*
FROM (Contacts RIGHT JOIN Client ON Contacts.ACT_No = Client.ACT_no) LEFT JOIN Cat_Sub ON Client.CatSub_ID = Cat_Sub.CatSub_ID;ASKER
Hi LSMConsulting. Yes, it's just updating as far as I'm aware but I will verify with the client. So for the Excel sheet, how would I call the file inside the query syntax?
What do you mean "call the file"?
Are you running this in Excel or Access?
Are you running this in Excel or Access?
ASKER
The query will be running in Access, as that's where the tables are. But how do I import(for lack of a better term) all of the updated fields?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This will be a one time thing LSMConsulting. However, I'd much rather hand code it if I can. I suppose I could use the query designer though. The structure of the data should be good though.
So my steps, as I understand you, should be this:
1) Import my Excel sheet into a whole new table and name it
2) Then using an UPDATE Syntax, run the same query as the SELECT statement and reference that new table.
3) I should be done???
So my steps, as I understand you, should be this:
1) Import my Excel sheet into a whole new table and name it
2) Then using an UPDATE Syntax, run the same query as the SELECT statement and reference that new table.
3) I should be done???
UPDATE YourTable SET Field1=Value1, Field2=Value2 WHERE YourPrimaryKeyField=Primar
Of course, you'll be grabbing this data from the worksheet, so it would look something like this:
YourConnection.Execute "UPDATE YourTable SET Field1='" & Cells("A1") & "', Field2=#" & Cells("B1") & "# WHERE YourPrimaryKeyField=" & YourPrimaryKeyValue
My Excel syntax might be rusty, but hopefully that will give you the gist of what to do.
Also, you would need to update each table individually. If you are ADDING records, and if those tables are related, they would need to be done in order - in other words, you'd have to add the Parent records before the Child records.
Note also the syntax in the statement above. Access expects Text value to be surrounded in single or double quotes, and expects Date values to be surrounded with hash marks ( # ). In my example above, Field1 is a Text field, and I've surrounded the value for that field with single quotes. Field2 is a Date field, and I've surrounded the values for that field with hash marks.