[RESOLVED] Vb & MS Access

[mowafy]

i have application working on Sql server and Access on the same time
how can i create view on access from visual basic
i can make it with Sql server like create view [myview] as (sqlstatment)
but it dosn't work with access
please help

[Mark Gambo]

i have application working on Sql server and Access on the same time
how can i create view on access from visual basic
i can make it with Sql server like create view [myview] as (sqlstatment)
but it dosn't work with access
please help

There is no such thing as a view in MS Access. But Access has an object called a query which is similiar but not the same. You would probably be better off connecting to your databases via ado. Take a look at the links in my signature regarding ado.

[mowafy]

thanks mark
i'm connecting to data by using ADO but i don't know how to create that query from vb

[RobDog888]

Moved to Office Development

[Hack]

thanks mark
i'm connecting to data by using ADO but i don't know how to create that query from vb

Like this

Dim sSQL As String
sSQL = "SELECT * FROM  table WHERE condition = '" & whatever & "'"
adoRecordsetObject.Open sSQL adoConnectionObject

Where adoRecordsetObject is a preDimmed ADO Recordset and adoConnectionObject is what you used to open your database.

[RobDog888]

Yes, you can create queries in Access via VB.

Dim sSQL As String
'qryLogAnalysisErrors
sSQL = "CREATE VIEW qryLogAnalysisErrors AS SELECT"
sSQL = sSQL & " Q1.ID,"
sSQL = sSQL & " Q1.DateTime,"
sSQL = sSQL & " Q1.Source,"
sSQL = sSQL & " Q1.Type,"
sSQL = sSQL & " Q1.Computer,"
sSQL = sSQL & " Q1.Message,"
sSQL = sSQL & " Q1.EventID,"
sSQL = sSQL & " Q1.SessionDate"
sSQL = sSQL & " FROM"
sSQL = sSQL & " [" & cboComputer & "_" & cboLogType & "] AS Q1"
If cboLogType = "Application" Or cboLogType = "System" Then
    sSQL = sSQL & " WHERE Q1.Type = 'Error'"
Else
    sSQL = sSQL & " WHERE Q1.Type = 'Failure'"
End If
sSQL = sSQL & " AND"
sSQL = sSQL & " (((Q1.SessionDate)=#" & dCurrSessDate & "#))"
sSQL = sSQL & " GROUP BY"
sSQL = sSQL & " Q1.ID,"
sSQL = sSQL & " Q1.SessionDate,"
sSQL = sSQL & " Q1.Computer,"
sSQL = sSQL & " Q1.Source,"
sSQL = sSQL & " Q1.Type,"
sSQL = sSQL & " Q1.DateTime,"
sSQL = sSQL & " Q1.EventID,"
sSQL = sSQL & " Q1.Message;"
goEnv.Cnn.Execute sSQL

[mowafy]

thanks rob
but it's not working her is my code

create view ItemsSales as  SELECT SUM(Transact.TransQuantity *  (Transact.TransUnitPriceKD - Transact.trnDiscAmount)) AS TotSale, SUM(Transact.TransQuantity) AS TotQty,  Transact.TransItemCode, SUM(Transact.TrnTotCost) AS TotCost,  Transact.TrnstrNo FROM Invoice INNER JOIN  Transact ON Invoice.InvCode = Transact.TransCode AND  Invoice.InvNo = Transact.TransNo  Where Transact.TransCode = 10  )

its give me ann error " Excepected token : select "

[RobDog888]

The easiest way to debug these is to go into Access and create a new query of just the SELECT part. Then once its running correctly you can bring it back into vb with some minor mods. I see your missing a GROUP BY clause since your not performing an aggregraste function on all fields included in the select list.

[mowafy]

thanks all
i resolved

[RobDog888]

What was the offending issue?