How to create a view in access (from vb6)

I have a access file, and need to create a view from VB6.
This isnt working:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SJA068.mdb;Persist Security Info=False"

sql = "CREATE VIEW DatosLiq(field1) AS "
sql = sql + "SELECT nov_det.ndet_impcal"
cn.Execute sql

The view I need to do has 30 fields, I use the code above as example.

That gives me an error in cn.execute sql
I read that with this method I cant create a view in access, but cant find the correct way to do this.
Thanks!

You can use a FLEXGRID as a view, or a LISTBOX as a view, or even TEXT-BOXES for each field.

Why don't you look into one of these methods?

(Also, when posting code samples, use TAGS)

Code:

---

' Like This

---

Im trying to use a system I developed for MSSQL in access doing the minimun amount of changes, in that SQL I have a view where I execute all queries, I'll try with other methods then...or just skip the view and query for data (I prefer the view approach)
thanks!

Why not just import the SQL database into access? It should create the proper queries in access based on your views.

Without doing an in-depth examination of what's going on, the first thing I see is that you didn't complete your SQL statement. All you have is the SELECT portion. You need to include the FROM:

Code:

---

CREATE VIEW DatosLiq(field1) AS
SELECT nov_det.ndet_impcal
FROM nov_det

---

Now, I'm not very familiar with creating views in Access, but I know you have to have the FROM portion of the SQL in order for this to work.

I don't think you can create a VIEW that way. You'd have to specify the fields at the very least.

The query is:
Create view NAME(field1,field2)
AS select field1, field2 from table where condition

I dont think im having an error there. The database is user locked because is from a software not maintained by me (so I cant enter to access to manually create the query), the developer doesnt give me some things I need, from the SQL version is easy, but I have this problem with the access version.

Thanks

What is your exact statements for creating the view RH? Because Leon Kennedy's way is the correct way for creating a view.

How many tables are we dealing with here?
Are you making use of Primary and Foriegn keys?

If I dont use the view the query is something like this, I know the view works and this query is VERY hard to follow =/

Code:

---

SELECT Leg.leg_numero as legajo,month(liq.liq_fecha) as mes,nov_det.ndet_impcal as importe FROM tli RIGHT OUTER JOIN sec INNER JOIN con INNER JOIN car RIGHT OUTER JOIN leg INNER JOIN nov_det ON leg.leg_numero = nov_det.leg_numero INNER JOIN liq ON nov_det.liq_nroint = liq.liq_nroint ON car.car_codigo = leg.car_codigo ON con.con_codigo = nov_det.con_codigo INNER JOIN nov ON nov.leg_numero = nov_det.leg_numero AND nov.liq_nroint = nov_det.liq_nroint AND liq.liq_nroint = nov.liq_nroint AND leg.leg_numero = nov.leg_numero ON sec.sec_codigo = leg.sec_codigo ON tli.tli_codigo = liq.tli_codigo Where nov_det.ndet_impcal <> 0 and (DatosLiq.con_codigo=397 or DatosLiq.con_codigo=400) and (tli_descrip='normal mensual' or tli_descrip='retroactivo') and DatosLiq.liq_fecha between '20100130' and '20100630' and (DatosLiq.sec_descrip='Primaria' or DatosLiq.sec_descrip='Inicial' or DatosLiq.sec_descrip='Terciario' or DatosLiq.sec_descrip='Medio' or DatosLiq.sec_descrip='Nva PF Inicial' or DatosLiq.sec_descrip='Nva PF Medio' o
r DatosLiq.sec_descrip='Nva PF Primaria' or DatosLiq.sec_descrip='Comp Inicial' or DatosLiq.sec_descrip='Comp Primaria' or DatosLiq.sec_descrip='Comp Medio' or DatosLiq.sec_descrip='Comp Terciario' or DatosLiq.sec_descrip='Primaria Adultos' or DatosLiq.sec_descrip='Suplente Primaria Adultos' or DatosLiq.sec_descrip='Suplente Primaria' or DatosLiq.sec_descrip='Suplente Inicial'  or DatosLiq.sec_descrip='Suplente Medio'  or DatosLiq.sec_descrip='Suplente Terciario' or DatosLiq.sec_descrip='Suplente Nva PF Inicial' or DatosLiq.sec_descrip='Suplente Nva PF Medio' or DatosLiq.sec_descrip='Suplente Nva PF Primaria' or DatosLiq.sec_descrip='Suplente Comp Inicial'  or DatosLiq.sec_descrip='Suplente Comp Primaria' or DatosLiq.sec_descrip='Suplente Comp Medio'  or DatosLiq.sec_descrip='Suplente Comp Terciario') order by DatosLiq.leg_numero,month(DatosLiq.liq_fecha);

---

But I try to use the query then...it should work

Edit: Now I have "sintax error in FROM clause", the from clause is BIG, is there any way to have a more detailed error. Or a way to open a DB with user protection (not password)
thanks