How to create a view in access (from vb6)

[RH+]

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!

[dglienna]

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)

' Like This

[RH+]

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!

[DataMiser]

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

[Leon Kennedy]

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:

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.

[dglienna]

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

[RH+]

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

[HanneSThEGreaT]

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?

[RH+]

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 =/

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