Hi i have a command button on a form that inserts specified records based on a whereclause into a new table:
LOCATION_QRY is a query:
Dim SQL1 As String
SQL1 = "INSERT INTO [TBLTEMP_LOC]" & _
"SELECT LOCATION_QRY.*" & _
"FROM [LOCATION_QRY]" & _
Whereclause & _
"ORDER BY [SECTION]"
This works fine
Each record in the LOCATION_QRY contains 4 or 5 geology records. These geology records should go into a table called TBLTEMP_GEO. The records and geo records are linked by ID. So, I want to insert all geo records based on the ID of what is already in the TBLTEMP_LOC table. Like so:
Dim SQL2 As String
SQL2 = "INSERT INTO [TBLTEMP_GEO]" & _
"SELECT GEOLOGY_QRY.*, [TBLTEMP_LOC].[ID1]" & _
"FROM [GEOLOGY_QRY], [TBLTEMP_LOC]" & _
"WHERE [GEOLOGY].[ID] = [TBLTEMP_LOCHEAD].[ID1]"
ID and ID1 are text fields. The SQL2 works ok, it just takes a really long time. About 10 minutes. I dont think this is correct because SQL1 runs instantaneously. Also, if i want to append more records to the tables, the SQL2 will insert duplicates into the TBLTEMP_GEO. This is because it will insert the geo records for the records already in TBLTEMP_LOC and the ones that were just added by running SQL1. Any help would be appreciated. Thank you.
I want to select the ID field in TBLTEMP_LOCHEAD and insert all the geo records that have the same id's as records in the TBLTEMP_LOCHEAD. In SQL1 the "Whereclause" is based on the search form criteria.
Is TBLTEMP_LOCHEAD generated on the fly every time? If so, you should be using SELECT INTO as opposed to INSERT INTO. Also, INSERT INTO does unreliable things unless you specifically define the fields being inserted:
INSERT INTO yourTable (field1, field2)
SELECT yourField1, yourField2
The order of columns and records id arbitrary and unreliable when you call "SELECT *". I wouldn't be surprised if that's what's making access crap itself.
Yea it sorta is generated on the fly. I need the INSERT INTO because I want the user to be able to append more records to the table after doing a search on the form. So, more records could be added. And then once the user is done with all the searches, I have a open report command button that has the TBLTEMP_LOCHEAD as the underlying table base. The report has a subreport with TBLTEMP_GEO as the underlying table for the subreport.