Results 1 to 3 of 3

Thread: SQL join in VBA

  1. #1
    Join Date
    Nov 2009

    Unanswered: SQL join in VBA

    I am new to both SQL and VBA. I have successfully coded some SQL action queries within vba using DoCmd****nSQL. I am now trying to perform a LEFT MERGE of two tables. I know that SELECT statements cannot be executed with DoCmd****nSQL.

    So how do I run a SELECT ... LEFT JOIN in vba. I have read several sites/forums and seen multiple suggestions, none of which have worked for me. Creating a recordset is suggested often, but I haven't understood enough of any of the sample code to get it to work for me. The SQL code works as desired in the query SQL script editor.

    Here is what I am trying to run:

    SQLString = "SELECT p.*, a.associate " & _
    " FROM patients AS p LEFT JOIN assoc AS a " & _
    " ON (p.First_Name = a.FirstName AND p.Last_Name = a.LastName; "

    I would like the old patients table to be replaced by the new file resulting from the JOIN. Or at least a new table created with the results of the JOIN.

    Any help is greatly appreciated.
    Last edited by kyle7; 11-10-09 at 16:27.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I would FIRST design the query using query designer and then see if the query is updatable. If it's not, it'll give you problems in code trying to update. If it doesn't, in query designer, change the VIEW to "SQL" versus "Design" view and then copy/paste your SQL Statement, edit the syntax a bit in the vba code (removing extra " or ' syntax), and you have your SQL statement.

    Keep in mind that whenever you do a join for a query/SQL syntax, depending on how you do the join, you may or may not be able to update the recordset. If you can't update it, then you need to figure out a way where you CAN update it. Sometimes you need to open 2 separate recordsets in code (without joins), updating the 1st, closing it, and then opening the 2nd and updating it. I typically do not use joins when updating a specific recordset (and instead, use criteria to identify the specific record(s) I'm updating - ie. I'll open the recordset based on the primary/autonumber field value.)

    strSQL = "Select * from MyTableName where CustomerID = " & Forms!MyFormName!CustomerID & ""

    If you need to return other field values from a different table (to a form), create an unbound field on the form and utilize the dlookup, make a combobox on the form (or write a function) to return these values. Then you keep the form's recordset only based on the table you need to update and avoid hassles of the recordset being updatable or not.
    Last edited by pkstormy; 11-10-09 at 17:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    If you want to insert the dataset resulting from a SELECT (with a join or not), you can use the following syntax:

    strSQL = "INSERT INTO <NewTable> (<Column1>, <Column2, ...,<ColumnN>) SELECT <...>"
    CurrentDb.Execute strSQL
    You can also create a new table and fill it in one operation, using the SELECT ... INTO syntax (see Access help).
    Have a nice day!

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