Running multiple queries using VBA in a specific order

[compooper]

Hi,

I have 10 queries I need to run using vba.
The methods I've tried are as follows:

dbs.Execute "query1"
dbs.Execute "query2" .. etc etc
this process was extremely slow and the end result was not what I wanted



I also tried:
DoCmd.OpenQuery "query1" etc etc
this process was a little faster, but it also did not give me the end result I wanted

I also added DoCmd.Close & DoEvents to the processes above and it did not speed things up or give me the result I wanted

If I run the queries manually step by step, I get the result I want.
Thats why I am assuming its all about the method I am trying.
All help is appreciated

[TheShabz]

A series of DoCmd.OpenQuery is how I do it. If you're 100% sure the queries work fine, just turn off the warnings at the start and turn them back on at the end. set that to a button and let it rip. I have workflows that are 25+ queries long and my vba is a mass of DoCmd.OpenQuery with some on the fly stuff in between.

[compooper]

Hi thanks for the response.

I'm positive the queries are correct. Its working when if I click on them individually perfectly. Just not when running with vba. More specifically the qry that is screwing up is qryCO1updtPolicyNum

Function Upload(Filepath As String, CoName As Integer, InvType As Integer, InvDate As Date)
Dim dbs As Database
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "UPDATE TestTable SET TestTable.CompanyID ='" & CoName & "', TestTable.InvTypeID ='" & InvType & "',TestTable.InvPeriod ='" & InvDate & "' WHERE Testtable.CompanyID Is Null AND Testtable.InvTypeID Is Null AND TestTable.InvPeriod Is Null;"
'more code
ElseIf CoName = 1 And InvType = 5 Then
DoCmd.TransferText acImportDelim, , "Inv_CO1", Filepath, True
DoCmd.SetWarnings False
'dbs.Execute "qryCO1dltBlanks"
DoCmd.OpenQuery "qryCO1dltBlanks", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1dltBlanks"
'dbs.Execute "qryCO1apdName"
DoCmd.OpenQuery "qryCO1apdname", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdName"
'dbs.Execute "qryCO1updtNotApp"
DoCmd.OpenQuery "qryCO1updtNotApp", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1updtNotApp"
'dbs.Execute "qryCO1apdPolicyDOB"
DoCmd.OpenQuery "qryCO1apdPolicyDOB", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdPolicyDOB"
'dbs.Execute "qryCO1apdMerge"
DoCmd.OpenQuery "qryCO1apdMerge", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdMerge"
'dbs.Execute "qryCO1apdCity"
DoCmd.OpenQuery "qryCO1apdCity", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdCity"
'dbs.Execute "qryCO1apdProvince"
DoCmd.OpenQuery "qryCO1apdProvince", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdProvince"
'dbs.Execute "qryCO1apdlname"
DoCmd.OpenQuery "qryCO1apdlname", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdlname"
'dbs.Execute "qryCO1apdfname"
DoCmd.OpenQuery "qryCO1apdfname", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1apdfname"
'dbs.Execute "qryCO1updtPolicyNum"
DoCmd.OpenQuery "qryCO1updtPolicyNum", acViewNormal, acReadOnly
DoCmd.Close acQuery, "qryCO1updtPolicyNum"
DoCmd.RunSQL strSQL
DoCmd.RunSQL "DELETE * FROM Inv_CO1;"
DoCmd.SetWarnings True

[TheShabz]

Try it without the acView info, just:
DoCmd.OpenQuery "qryCO1dltBlanks"
DoCmd.OpenQuery "qryCO1apdname"
DoCmd.OpenQuery "qryCO1updtNotApp"
etc

[compooper]

Yeah I tried that, still incorrect result.

[TheShabz]

Can I see the SQL of the query that's messing up? I can't think of another reason for it not to work other than something in the SQL that's throwing off the VBA.

[compooper]

I think its qryCO1apdMerge

INSERT INTO TestTable ( App_policynum, App_lname, App_fname, App_dob, App_city, App_province, Svc_code, Svc_description, Svc_fee, date_rcvd, date_completed )
SELECT IIf(Inv_CO1.[City, Province] Is Null,Null,Inv_CO1.[Policy #/DOB]), 
Left(Inv_CO1.[Applicant/Notes],InStr(Inv_CO1.[Applicant/Notes],",")-1), 
IIf(Inv_CO1.[Applicant/Notes] Like "*,*",Right(Inv_CO1.[Applicant/Notes],len(Inv_CO1.[Applicant/Notes])-InStr(Inv_CO1.[Applicant/Notes],",")-1),Null), 
IIf(Format(Inv_CO1.[Policy #/DOB],"Medium Date"),
Inv_CO1.[Policy #/DOB],Null), Left(Inv_CO1.[City, Province],InStr(Inv_CO1.[City, Province],",")-1), 
Right(Inv_CO1.[City, Province],len(Inv_CO1.[City, Province])-InStr(Inv_CO1.[City, Province],",")-1), 
Inv_CO1.Svc, Inv_CO1.description, 
Inv_CO1.Fee, IIf([Case #/Date] Is Not Null And [City, Province] Is Null,
[Case #/Date],Null), Inv_CO1.CompletedFROM Inv_CO1
ORDER BY Inv_CO1.CO1ID;

Its the first part IIf(Inv_CO1.[City, Province] Is Null,Null,Inv_CO1.[Policy #/DOB]) which is going screwy. Its just repeating the same record over and over, instead of selecting the different records.

(just for ease of readibility I've spaced out the lines, in the code there are no spaces)

[TheShabz]

I dont think you can set a field to null. Try replacing that with Nz(Inv_C01.[City, Province], 0) and see if it runs properly, putting 0s in places where it would otherwise be null.

Also, for the future, try to not use spaces and special characters in your table and field names. It leads to so many headaches.

[compooper]

Unfortunately Null & 0 isn't the issue.

What's happening is, its taking a value from another table... (see attached)

I only want the Polcy # in the app_policynum column, hence this IIF statement

IIf(Inv_CO1.[City, Province] Is Not Null,Inv_CO1.[Policy #/DOB],Null)
or the one I show you earlier

This query works perfectly if I click on it manually. Maybe VBA is reading it incorrectly. Could there be a syntax problem here?

When VBA runs this query, it repeats policynum = "n/a" and does the null part correctly. Somethings wrong

[TheShabz]

A confusion in vba is most likely the issue. try:
Dim strSQL As String
strSQL = 'the SQL of the query
DoCmd.RunSQL (strSQL)

and see if it works directly in VBA. I think it's going to be a matter of fidgeting around with it until it jives. I'm really not sure what else it could be.

[compooper]

sigh...
I think the mistake is actually in this query...
UPDATE Inv_EXPARA SET Inv_EXPARA.[Policy #/DOB] = "n/a"
WHERE Inv_EXPARA.[Policy #/DOB] Is Null AND Inv_EXPARA.[City, Province] Is Not Null;

I tried:
strNA = "UPDATE Inv_EXPARA SET Inv_EXPARA.[Policy #/DOB] = " & Chr$(39) & "n/a" & Chr$(39) & " WHERE Inv_EXPARA.[Policy #/DOB] Is Null AND Inv_EXPARA.[City, Province] Is Not Null;"

Same thing

[compooper]

AHH ITS WORKING NOW!
I had to add an import spefication, who knew!

Thanks for all your support
Just a side question:
which is better to use: dbs.execute "queryname" or DoCmd.OpenQuery