applications using VB and Access

[venukrish]

I've got 2 queries in MS Access.The queries are as follows.
query1:

SELECT qryPanelsandParts.Type, Sum(qryPanelsandParts.Sales) AS [SumOfTotal Sales], qryPanelsandParts.QuoteOnly
FROM qryPanelsandParts
GROUP BY qryPanelsandParts.Type, qryPanelsandParts.Year, qryPanelsandParts.QuoteOnly
HAVING (((qryPanelsandParts.Year)=[Forms]![frmMenu]![cboYear]) AND ((qryPanelsandParts.QuoteOnly)=False));

Query 2 (qryPanelsandParts) :

SELECT Customers.CustomerName, PanelsandParts.CustomerID, PanelsandParts.Job, PanelsandParts.Sales, PanelsandParts.Material, PanelsandParts.EngHrs, [EngHrs]*[EngRate] AS EngCost, PanelsandParts.PnlHrs, [PnlHrs]*[PnlRate] AS PnlCost, [EngCost]+[PnlCost] AS Labor, [Material]+[Labor] AS Cost, [Sales]-[Cost] AS GMDollars, [GMDollars]/[Sales] AS GMPct, PanelsandParts.QuoteOnly, PanelsandParts.Month, PanelsandParts.Year, PanelsandParts.Type
FROM Rates, Customers INNER JOIN PanelsandParts ON Customers.CustomerID = PanelsandParts.CustomerID
WHERE (((PanelsandParts.QuoteOnly)=False));

here query1 is using query2 and quey2 uses only tables. I can able to execute query2 directly in a vb form but not query1 since it is using query2 in it.


I just want to join these two queries to make a single sql statement and use it in a VB form at run time.


or please tell me is there anyother way to use both the access "queries" directly in a vb form without writing a new sql statement .Please send me the reply soon.

[Hack]

In your Project References, set a reference to the Microsoft Access Object Library.

In the declarations section of your form, put:

Private Axs As Access.Application

In a click event somewhere, put:

Set Axs = CreateObject("Access.Application")
Axs.OpenCurrentDatabase (DBPath)
Axs.DoCmd.OpenQuery "Query2"

If you already have the database open, omit line 2.
In line 3, replace "Query2" with the name of the Access Query that you want to run.

Hope this helps.

[venukrish]

hi,
Hope u'r approach for my problem will help me a lot.But I have some problems in understanding your approach.So please explain u'r solution to me in detail.

[Hack]

The approach is very simple. You are making an OLE connection to MS Access, and telling Access to run the Access query that you wrote.

[venukrish]

Private AXS As Access.Application
Dim stDocName As String
Dim stLinkCriteria As String




Private Sub Command1_Click()
Set AXS = CreateObject("Access.Application")
AXS.OpenCurrentDatabase "c:\windows\desktop\converted_new.mdb", False
stDocName = "datainput"
AXS.DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
End Sub

*********************************

I did the samething what you told.please check whether it is correct or not.
But when I run my VB application its not opening the "datainput" form in MS ACCESS

[Hack]

Ok. I'm confused. Your orginial post stated that you wanted to be able to run a query that you had written in Access from VB (at least, that what I thought it said)

So, why are you opening a form?

[venukrish]

I thought of converting an MS Access program to VB.And I'm in the process of writing codes for all the forms and reports present in Access.I didn't had the idea of using the "docmd" in vb.But once u gave me the idea of calling the access program from the VB,I changed the idea of converting the MS Access application to VB application.This will reduce 90% of my job if I call all the forms in MS Access from VB.

Now also I have a problem In doing that.

***************************
Private Axs As Access.Application

Private Sub Command1_Click()
Set Axs = CreateObject("Access.Application")
Axs.OpenCurrentDatabase "c:\windows\desktop\converted_new.mdb", False
Axs.DoCmd.OpenTable "Customers", acViewNormal, acReadOnly
End Sub
****************************
before writing the above code I addes the MS Access 9.0 reference library from the references.

I just tried to open a form, table and a report by using your approach.I wrote the above code in a click event of a button.when I clicked on the button at run time the cursor blinks but nothing has happened.when I pressed the Ctl+Alt+Del the close window showed a MS Access application trying to open.But its not actually opening.