Query Varible not changing

[n3buo]

I have 3 queries from 3 different databases that I paste into 3 tables on a fourth database. In the past I was removing the data from the tables and then pasting the new data from the 3 different queries. I can not use a create table query because of links on the 3 tables.

I wrote code to do this for me but when the second insert into query starts it paste the first inserts query data into the second... Its like the first query does not clear before the second query runs. Here's the code

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM HPIWH_HCFA"
DoCmd.RunSQL "DELETE * FROM HPRI_HCFA"
DoCmd.RunSQL "DELETE * FROM DNI_HCFA"
DoCmd.SetWarnings True

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL = "INSERT INTO HPIWH_HCFA SELECT HPIWH_HCFA_Source.* FROM HPIWH_HCFA_Source IN 'R:\PS\DATAMATE\DATAMATE2002.mdb'"
strSQL1 = "INSERT INTO HPRI_HCFA SELECT HPRI_HCFA_Source.* FROM HPRI_HCFA_Source IN 'S:\PS\DATAMATE\DATAMATE2002.mdb'"
strSQL2 = "INSERT INTO DNI_HCFA SELECT DNI_HCFA_Source.* FROM DNI_HCFA_Source IN 'Q:\PS\DATAMATE\DATAMATE2002.mdb'"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

Should I be clear the first query before the second and third runs? Is there a different way I should be doing this?

Thanks,
Dave

 

[lupins46]

Looks OK to me. You need to compact your database regularly or it will bloat with this type of activity.
Don't know what you mean by 'clear the first query'.

 

[PHV]

Are by chance R: and S: mapped to the same share ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[n3buo]

R and S are not mapped to the same share. I have other VBA code that refers to the same paths. The databases are compact every morning. I am banging my head against the wall with this issue.

This morning I ran strSQL2 first, then I ran the entire query, This time it put the data from strSQL2 into each of the tables.

I then closed the database and reopened the database and ran strSQL1 first and the data from strSQL1 was placed into each of three tables.

Man, this is a wierd one....
Dave