Trouble Creating Record Source

[Dustin Ventin]

Hello... I'm upgrading my Access application from using an Access back-end to
using SQL Server and a ASP front-end. Previously, my main screen displayed a
list of records that was populated by a query that brought data from a table
and a second query. Effectively, it's this:

"SELECT tblUserProjects.UProj_ID, tblUserProjects.UProj_Name,
qryGroupBudgetForecast.SumOfBD_OriginalBudget
FROM tblUserProjects LEFT JOIN qryGroupBudgetForecast ON
tblUserProjects.UProj_ID = qryGroupBudgetForecast.Budget_UProj_ID;"

The SQL code is fine, I've run it not a few times in SQL Server without a
hitch. However, now that my project is an ADP, this query causes my whole
application to fail the moment the main screen is opened. Now, if I simplify
the query so it's only source is the table (tblUserProjects), it works fine -
but obviously it's only showing half the data we want.

Why would increasing the complexity of this query cause my whole application
to bomb without warning? Any help anyone could provide would be wonderful.

Thank you in advance,

Dustin

 

[Sylvain Lafontaine]

I would say that the list of tables/views/queries/UDF and their properties
was not OK when the form has been designed/saved. First thing to verify,
close and reopen the ADP project to make sure that the database window (the
one showing the list of tables and of queries/views/UDF is updated. You can
also use the Refresh Command from the View menu or even better, change the
connection string to some other database and put it back after that.

After that, you can reopen the form in design mode, make some modification
and resave it.

If nothing work, then you should create a blank ADP project and import
everything into it.

Another possibility would be that you have some permission problem because
you're not using the same login account when you are executing this query on
SQL-Server that the one that you are using for your ADP project.

Finally, what version of Access are you using and did you install the latest
service packs?

 

[Danny Lesandrini]

Does qryGroupBudgetForecast exist as a view in SQL Server?