Creating a Make-Table Query in VBA 2

[cwadams]

In Access, Using VBA Only, I am trying to write A Make-Table Query from a table named CompSales INTO a table named Temp using conditions from a form, and then get a count of records. If I write the query in design view it works, but coding in VBA it does not return a record count or update my table called Temp. Suggestions?
'------------------
Option Explicit
Dim X As Integer
Dim FirstPull_Cnt As Integer
Dim strSQL As String

Private Sub FirstPull()

strSQL = "SELECT CompSales.Stat, CompSales.Property, CompSales.[Nbh Code] INTO Temp" & _
"FROM CompSales" & _
"HAVING (((CompSales.Property) Not Like [Forms]![frmSubject]![Property]) AND" & _
"(CompSales.[Stat]) Is Not Null) AND" & _
"((CompSales.[Nbh]) Like [Forms]![frmSubject]![Nbh])"

X = DCount("[Property]", "Temp")
FirstPull_Cnt = X
End Sub

 

[ESquared]

What are you doing with strSQL once you've built it? The statement has to be submitted to the query engine, somehow.

Given the DCount function you used, I presume this is MS Access. And mdb, not adp.

The last time I did something like this using native Access stuff I had to programatically create a query, set the query source to my SQL string, execute the query, then delete/discard the query.

If you use ADO, you probably can just connect to the database and issue the statement, but I'm not 100% sure. Perhaps asking in the Access forum would be appropriate? Sorry I wasn't more help. If it was an adp or SQL Server I could tell you exactly what to do...

 

[cwadams]

I went ahead and posted to the appropiate Forum. Being new to the forums takes some time navigating.
I AM using .mdb and DAO
Thanks for the quick reply. In answer...
I AM able to create a query and execute however I am trying to re-write my program so that the everything is automated through VBA.

 

[sugarflux]

Simply follow up what you have there with

Currentdb.execute strSQL

to run the query you have written.

sugarflux

 

[JerryKlmns]

Give it a little of space ...

strSQL = "SELECT C.Stat, C.Property, C.[Nbh Code] INTO Temp " & _
"FROM CompSales As C " & _
"WHERE ((C.Property Not Like [Forms]![frmSubject]![Property]) AND " & _
"(C.[Stat]) Is Not Null) AND " & _
"(C.[Nbh] Like [Forms]![frmSubject]![Nbh]));

 

[JerryKlmns]

Hit submit to soon..

Do you need just the record count or you 'll use the result set for extra work?

 

[cwadams]

Yes and Yes.
I needed to execute the SQL AND I needed to add a "little space" after the quotes in my string statement.
Program has passed this phase on to the next hurdle. Thanks all.