Web Development

Ask something
Discussion Locked

Access Project/SQL Server-Display Data

0 helpful
1 reply

by bob sellman 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Using Access 2000 Project and SQL Server 7. I want to display in the spreadsheet type format the results of a SQL statement I create. The SQL code works fine and the display works with Acces/ODBC to SQL, but in Project I can't get the spreadsheet display unless from the database window I open a stored procedure I defined (that just takes the entire SQL statement as a parameter and executes it within the procedure). And then I'm prompted to enter the SQL code manually.

What is the VBA codeto either pass the parameter to the stored procedure and open it in the spreadsheet type format or, open a recordset in the same format? (I've already been able to create the recordset, but I have the same problem.)

1 reply

by bob sellman

I've written a stored procedure that successfully opens a SQL query based on query text stored as a field in a table. The problem that occurs is that in Access and Access Project, the first time a stored procedure, query, or (in the case of Access)a pass-through query is executed, Access figures out what each column is called and displays (in the spreadsheet type format) the data accordingly. Using the stored procedure I am able to change the actual query; however, Access and Access Project don't recognize the changed columns (names of columns as well as how many columns there are in the specific query), so the data is not displayed if the new columns don't match the original SQL query columns.

In Access (not Project) if I delete thestored procedure and then redefine it, the display is correct. Apparently Access then considers it as the first time the query has been opened in the session. But you can't delete and add stored procedures in Project. (Actually, it says you can in 2000, but won't be able to in 2002, so I don't want to even try.)

Any ideas?

This discussion is closed to new replies.