Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Question 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()
    CurrentDb.Execute _
    "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", _
    dbFailOnError

    "INSERT INTO tblProjExp (IRBNetID, Board, Title, PI Name, Sponsor, Keywords, Internal Reference Number, Submission ID, Board Reference Number, Submission Type, Submission Date, Review Type, Action, Effective Date, Project Status, Initial Approval Date, Project Expiration Date, Days to Expiration") " & _
    "SELECT I.Board, I.Title, I.PI Name, I.Sponsor, I.Keywords, I.Internal Reference Number, I.Submission ID, I.Board Reference Number, I.Submission Type, I.Submission Date, I.Review Type, I.Action, I.Effective Date, I.Project Status, I.Initial Approval Date, I.Project Expiration Date, I.Days to Expiration" & _
    "FROM tblImport I LEFT JOIN tblProjExp O " & _
    "ON I.IRBNet ID = O.IRBNetID" & _
    "WHERE O.IRBNetID Is Null", _
    dbFailOnError

    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What are the error code(s) and message(s)?
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    3

    RE:Updating and Adding Records from one

    Quote Originally Posted by Sinndho View Post
    What are the error code(s) and message(s)?
    Compile error: Syntax error

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, all of the fields with the inadvisable spaces in them have to be bracketed, like:

    O.[PI Name]

    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.
    Paul

  5. #5
    Join Date
    Aug 2011
    Posts
    3

    RE: Updating & String Variables

    I am a novice at this, is there a good place (web) to learn more about string variables programming.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In this instance there wouldn't be much to it:

    Code:
    Dim strSQL As String
    
    strSQL = "UPDATE..."
    
    CurrentDb.Execute strSQL
    If/when you have trouble, that lets you add

    Code:
    Debug.Print strSQL
    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.
    Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •