
Excel VBA/Macintosh/Q+E FoxPro ODBC driver
I'm writing an application in Excel 5.0 on the Mac. It's designed to help
sales supervisors track the performance of their sales agents. When a
supervisor enters his ID number and password, my application performs an
SQL query to retrieve sales data for the agents on his team, and for the
requested month/year. The query string is something like
SELECT * from STATS WHERE TEAM = 99 AND YEAR = 1995 AND MONTH = 12
This works fine on a standalone Mac. When I run it on my client's network,
with the FoxPro tables on a file server, I have problems. It creates an
empty QEDBF.INI file in the folder that contains the table, and the call to
SQLRetrieve fails with a 2042 error. The MSDN CD describes this problem,
and indicates that it is caused by a missing or misplaced CDX (index) file.
I tried creating indices in FoxPro (both Team+Year+Month+AgentID, and
Team+Year+Month), and I still get the same error. If I decrease the size
of the table (deleting the data for all but one team, which takes it from
4200 records to 150), it works fine.
Can anyone tell me how to configure the indices, or modify my query, so
that this works? I have searched the docs and MSDN, but I have yet to find
any documentation on it.
Thanks,
Kent