[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 vbLike thisDim sSQL As String
sSQL = "SELECT * FROM table WHERE condition = '" & whatever & "'"
adoRecordsetObject.Open sSQL adoConnectionObjectWhere 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?