• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

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.
0
dyarosh
Asked:
dyarosh
  • 5
  • 4
  • 3
1 Solution
 
tbsgadiCommented:
Hi dyarosh,

Try using a new connection to Access


Good Luck!

Gary
0
 
CodeCruiserCommented:
You could have right clicked the dataset in the dataset designer and selected Refresh Schema.
0
 
dyaroshAuthor Commented:
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).
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tbsgadiCommented:
Does the query work on it's own or is it based on open forms ,functions etc?
0
 
CodeCruiserCommented:
Can you show us a screenshot of your dataset designer surface?
0
 
tbsgadiCommented:
While you're at it maybe post the SQL
0
 
dyaroshAuthor Commented:
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
0
 
tbsgadiCommented:
Try changing NZ() to isnull()
0
 
CodeCruiserCommented:
Does this query run and bring results in Access as expected?
0
 
dyaroshAuthor Commented:
The query runs as expected in Access.
0
 
dyaroshAuthor Commented:
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.
0
 
dyaroshAuthor Commented:
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.  
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now