temporary tables and FoxPro

[myatia]

Hi, all,

Does anyone know how to access a temporary table in FoxPro 6 via ADO connections?

I am currently tracking how many people log into my website, and I would like to find out how many people log in how many times. In other words, I want to know the number of people that log in 1 time, the number that log in 2 times, the number that log in 3 times, etc. I then want to group this data up by month.

From the command line in FoxPro, I can run the following queries to get this info:

-- Query 1: Select into cursor
SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount 
FROM logintrack 
GROUP BY M, id 
INTO CURSOR temp_logintrack

-- Query 2: Get count of counts
SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount 
FROM temp_logintrack 
ORDER BY M, LCount 
GROUP BY M, MN, LCount

But, if I try to execute these queries like so:

conn.execute(query1)
set rs = conn.execute(query2)

I get an "temp_logintrack.dbf not found" error when I try to execute query2.

If anyone has any advice, I'd really appreciate it! Let me know if you need any more info.

Thanks,

Misty

 

[bhuninghake]

You getting the error because the dbf doesn't exist.
See if this helps:

SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount 
	INTO #temp_logintrack
	FROM logintrack 
	GROUP BY M, id 

SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount 
	FROM #temp_logintrack 
	ORDER BY M, LCount 
	GROUP BY M, MN, LCount

drop table #temp_logintrack

 

[myatia]

I'm still getting the same error:

-2147217865ASP Code=
Number=-2147217865
Source=
Filename=/admin/Other/login_reports.asp
Description=[Microsoft][ODBC Visual FoxPro Driver]File '#temp_logintrack.dbf' does not exist.

I tried several variations, like:

qry1 = "SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount " &
	"INTO TABLE #temp_logintrack " & _
	"FROM logintrack " & _
	"GROUP BY M, id "
			  
conn.execute(qry1)
		
qry2 = "SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount " & _
	"FROM #temp_logintrack " & _
	"ORDER BY M, LCount " & _
	"GROUP BY M, MN, LCount" 
set rs = conn.execute(qry2)  '<<== ERROR HERE

and also:

qry = qry1 & "; " & qry2
set rs = conn.execute(qry)

I also tried it with and without the # sign.

Do you have any other ideas? Is something wrong in my code?

Thanks for all your help,

Misty