Adding values into a table with a query

Question

So all I'd like to do is add two queries into a table called tmpGroupSearch. I'm not sure how to do it wit out creating a record set and looping through every record and individually adding them in. I'm thinking there is a much easier way to do this I just can't find the proper structure.

Here are my queries:

"SELECT tblGroupHeader.GroupName" _
                           & ", tblGroupHeader.GroupNum" _
                           & ", tblAlsoKnown.AlsoKnown" _
                    & " FROM tblGroupHeader" _
                       & " LEFT JOIN tblAlsoKnown ON tblGroupHeader.GroupNum = tblAlsoKnown.GroupNum" _
                    & " WHERE tblGroupHeader.GroupName like '" & txtgroupSearch.Value & "*'" _
                          & " OR tblGroupHeader.GroupNum like '" & txtgroupSearch.Value & "*';"


"Select * FROM tblActionLog WHERE AlsoKnown LIKE '" & txtgroupSearch.Value & "*';"

pteranodon · Accepted Answer · 2015-02-20 16:38:37Z

You can follow an INSERT INTO clause with a list of values, like @Asad shows, or also a SELECT query. Do yourself a favor and always list the field names in your SQL or you are just creating time bombs for the future.

Dim strSelect1 As String
Dim strSelect2 As String

strSelect1 = "SELECT tblGroupHeader.GroupName" _
  & ", tblGroupHeader.GroupNum" _
  & ", tblAlsoKnown.AlsoKnown" _
  & " FROM tblGroupHeader" _
  & " LEFT JOIN tblAlsoKnown ON tblGroupHeader.GroupNum = tblAlsoKnown.GroupNum" _
  & " WHERE tblGroupHeader.GroupName like '" & txtgroupSearch.Value & "*'" _
  & " OR tblGroupHeader.GroupNum like '" & txtgroupSearch.Value & "*';"

strSelect2 = "Select * FROM tblActionLog WHERE AlsoKnown LIKE '" & txtgroupSearch.Value & "*';"

Dim strInsert1 As String
Dim strInsert2 As String

strInsert1 = "INSERT INTO tmpGroupSearch (GroupName, GroupNum, AlsoKnown) " & strSelect1

'the next version is valid SQL, but *dangerous* because field names are not enumerated
strInsert2 = "INSERT INTO tmpGroupSearch " & strSelect2

Asad · Accepted Answer · 2015-02-20 15:41:47Z

1

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

answered Feb 20, 2015 at 15:41

Asad

531 gold badge1 silver badge10 bronze badges

2 Comments

SaladSnake Over a year ago

Is there a way to INSERT INTO straight from a query?

Asad Over a year ago

Before inserting data you have to write the query.

HansUp · Accepted Answer · 2015-02-20 17:21:36Z

1

Test this parameter query in the Access query designer and adjust as needed so that it returns the row set you expect for the pSearchText parameter value you supply.

PARAMETERS pSearchText Text ( 255 );
SELECT gh.GroupName, gh.GroupNum, ak.AlsoKnown
FROM
    tblGroupHeader AS gh
    LEFT JOIN tblAlsoKnown AS ak
    ON gh.GroupNum = ak.GroupNum
WHERE
       gh.GroupName Like "'" & pSearchText & "*'"
    OR gh.GroupNum Like "'" & pSearchText & "*'"
UNION ALL
SELECT al.GroupName, al.GroupNum, al.AlsoKnown
FROM tblActionLog AS al
WHERE al.AlsoKnown Like "'" & pSearchText & "*'"

Once you have it returning the correct data, convert it to an INSERT query by changing the beginning of the statement to this ...

PARAMETERS pSearchText Text ( 255 );
INSERT INTO tmpGroupSearch (GroupName, GroupNum, AlsoKnown)
SELECT gh.GroupName, gh.GroupNum, ak.AlsoKnown
... and the rest

Save that query as qryGroupSearchAppend. Then you can execute that query from your VBA code:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryGroupSearchAppend")
qdf.Parameters("pSearchText").Value = Me.txtgroupSearch.Value
qdf.Execute dbFailOnError

answered Feb 20, 2015 at 17:21

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

SaladSnake Over a year ago

Thanks for the help hans. I wasn't able to create queries in access for this project, it had to be strictly hard coded in. So I took the easy way out and made a temp table.

Collectives™ on Stack Overflow

Adding values into a table with a query

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related