asked on

Can't see my MS Access Query in Visual Studio

I have a windows application that is written in Visual Basic .NET in Visual Studio. I created a query in MS Access that joins multiple tables and queries. At one point I was able to add the query to my dataset. I used the query as a basis for a report.

I had to make a change to the query so I went into MS Access and modified the query to include another field. When I went back into Visual Studio, the dataset did not reflect the change in the query. I couldn't find a way to refresh the query. I went to Server Explore and did a refresh on the database. Under Views I looked for the query but it is not displayed. Other queries are. I deleted the query from my Dataset before I did the refresh and now I can't get the query back. Since the query is gone I now get an error when I compile because my report is still looking for the query. Any idea how I can get my dataset to see the query from ms access?

I appreciate any help on this since I am under a deadline to get this project delivered.

tbsgadi

Hi dyarosh,

Try using a new connection to Access

Good Luck!

Gary

Nasir Razzaq

You could have right clicked the dataset in the dataset designer and selected Refresh Schema.

dyarosh

ASKER

I created a brand new project and connected to my access database. The tables show up and the simple queries show up but the complicated query does not. Any idea how to access the complicated query? (complicated query is made up of tables and simple queries).

tbsgadi

Does the query work on it's own or is it based on open forms ,functions etc?

Nasir Razzaq

Can you show us a screenshot of your dataset designer surface?

tbsgadi

While you're at it maybe post the SQL

dyarosh

ASKER

Here is the SLQ as shown in the MS Access Database. This query when run from Access works fine. I've also included a screen shot of my dataset. The AccountSnapShot query shows up under Functions but when I try and add it to my dataset I get the error that is shown. When I preview the data it is not what I am expecting. The columns that show up do not match what is displayed when the query is run from Access.
SELECT Employers.EmployerName, Employees.SocialSecurityNumber, [LastName]+", "+[FirstName] AS Name, Contributions.ContHours, Summary.DollarValue AS PriorYrBalance, TotalFees.totFees, Contributions.Contributions, Nz([Summary.DollarValue],0)+Nz([Contributions.Contributions],0)+Nz([TotalFees.totFees],0) AS AcctVal
FROM (((Employers LEFT JOIN Employees ON Employers.EmployerID=Employees.EmployerID) LEFT JOIN Summary ON Employees.EmployeeID=Summary.EmployeeID) LEFT JOIN TotalFees ON Employees.EmployeeID=TotalFees.EmployeeID) LEFT JOIN Contributions ON Employees.EmployeeID=Contributions.EmployeeID
GROUP BY Employers.EmployerName, Employees.SocialSecurityNumber, [LastName]+", "+[FirstName], Contributions.ContHours, Summary.DollarValue, TotalFees.totFees, Contributions.Contributions, Nz([Summary.DollarValue],0)+Nz([Contributions],0)+Nz([totFees],0), Employees.EmployeeID
ORDER BY [LastName]+", "+[FirstName];

dataset.png

ASKER CERTIFIED SOLUTION

tbsgadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nasir Razzaq

Does this query run and bring results in Access as expected?

dyarosh

ASKER

The query runs as expected in Access.

dyarosh

ASKER

I don't know what I have done differently other than reconnecting to the database after making changes, but I can now see my queries. I would like to request that my points be refunded.

dyarosh

ASKER

I finally am able to see my queries. I changed NZ to IIF(IsNull(field),0,field) and this does the trick. I no longer get an error message when trying to run the query.