Changing Stored Procedures

[Darkside12]

Hi,

I'm trying to build a dynamic query by form. The idea is that a user can select a table in the database via a combo box and this will then change all of the text box labels on the form to match the field headers for the selected table. It also hides any text boxes that are surplus to the number of fields of the selected table.

The user can then add their search criteria into the text boxes and search the selected table. To achieve this I've written some VBA that changes a stored procedure so that it fires off the correct SQL for the users search. The code works fine but I'm getting errors when trying to open the Stored Procedure.

The first error I encountered was after running the stored procedure for the first time which worked fine. When I selected a new table and ran a new search the old tables columns are displayed but blank.

When opening the procedure in design view the SQL was correct so I saved the procedure (without changing any settings) and ran it again. This time it displayed the correct data. So the SQL is being generated correctly by the VBA but the stored procedure isn't updating properly.

To try and solve this so far I have tried:

Opening the procedure (via VBA) saving and closing it and then opening it again in normal view. This produced the same error.

Changing the SQL in my code from an Alter Procedure to a Create Procedure and deleting the previous Procedure each time the code is run. This produced a new error in that every time the code was run Access would lose the procedure when it came to trying to open it. It magically reappears after closing and reopening the .adp

The database is built in Access Project (.adp) by the way.

Any help you can give me would be greatly appreciated.

Darkside

[Darkside12]

Hi Again,

I solved the issues by closing and reopening the connection to SQL Server which refreshes the stored procedures.

Darkside

[tokoloshi]

Have you considered using:
select * from information_schema.tables and
select * from information_schema.columns as the control source of your controls?

You can bind your controls to these 2 views and get all the info you need without the overhead of closing & reopening your connections..

[Darkside12]

It wasn't so much getting to the column headers rather than updating the stored procedures themselves. The SQL in the proceudres ran correctly after opening them in design view and saving them.

Rather than having to do that each time a user wants to run the procedure closing and reopening the connection to SQL Server automatically updates everything in the .adp

If it took a while I would have been tempted to carry on searching for a different solution but it doesn't even take a second.