Thread: SQL join in VBA
11-10-09, 16:23 #1Registered User
- 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.
11-10-09, 16:49 #2Moderator
- 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)
11-10-09, 18:00 #3Moderator
Provided Answers: 14
- Join Date
- Mar 2009
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 strSQLHave a nice day!