Unanswered: Updating and adding records from one table to another
I am new to Access, but learning quickly. So, I need to update tables on a biweekly basis from .CSV files. I have the create table & delete table functions, but I am having problems with my update existing and adding programming. I know NO SQL, but am familiar with VBA. Can someone help?
Existing Table: tblProjExp
Temp table (.csv loads to): tblImportPE
Primary key field: IRBNet ID
Below is what I have that is not working
Private Sub updatPE_Click()
"UPDATE tblProjExp O INNER JOIN tblImportPE I ON O.IRBNetID = I.IRBNet ID " & _
"SET O.Board = I.Board, O.Title = I.Title, O.PI Name = I.PI Name, O.Sponsor = I.Sponsor, O.Keywords = I.Keywords, O.Internal Reference Number = I.Internal Reference Number, O.Submission ID = I.Submission ID, O.Board Reference Number = I. Board Reference Number, O.Submission Type = I.Submission Type, O.Submission Date = I.Submission Date, O.Review Type = I.Review Type, O.Action = I.Action, O.Effective Date = I.Effective Date, , O.Project Status = I.Project Status, O.Initial Approval Date = I.Initial Approval Date, O.Project Expiration Date = I Project Expiration Date, O.Days to Expiration = I.Days to Expiration", _
For starters, all of the fields with the inadvisable spaces in them have to be bracketed, like:
I also see at least one instance of the period between table and field missing. I also see there won't be a space before the word WHERE in the second one. I personally would build a string variable and then execute it; much easier to debug.
Dim strSQL As String
strSQL = "UPDATE..."
If/when you have trouble, that lets you add
after the string is built, which prints out the finished SQL to the VBA Immediate window. If you don't spot the problem there you can copy/paste it into a blank query and try to run it, which will often give you a more descriptive error than you'll get in VBA.