error in creating a view

[progvar]

Hi!
i want to create a view in my program but this query generates the syntax error
but i am not getting the answer
Set RSmn = dbMn.OpenRecord set("CREATE VIEW CUS_NAME AS SELECT CMPNYNM ,INDNT FROM " & txttable & " ")

please provide help

[debasisdas]

CREATE VIEW is a DDL command so use ADOX for the purpose

[progvar]

CAN I CREATE TABLE WHICHHAVE ONLY TWO ROWS

Set NewRec = dbMn.OpenRecord set("CREATE TABLE CUS_NAME AS SELECT CMPNYNM ,INDNT FROM " & txttable & " WHERE INDNT='D' ")

BUT IT IS ALSO SHOWING THE SAME ERROR ,THIS COMMAND WORKS IN MS-ASSCESS BUT NOT HERE
WHAT IS WRONG IN MY SYNTAX

CREATE VIEW is a DDL command so use ADOX for the purpose

[debasisdas]

What are you using ADO or DAO ???

You need to understand first what is the use of OpenRecordset method.

[progvar]

I AM USING DAO OBJECT

What are you using ADO or DAO ???

You need to understand first what is the use of OpenRecordset method.

[debasisdas]

and what is the database ?

[QVeen72]

Hi,

Your Query works for Oracle,
but in Access, DDL SQL syntax is a bit different.. Try This :

[CODE=VB]
dbMn.Execute "SELECT CMPNYNM ,INDNT INTO NEW_TABNAME From " _
& txttable & " WHERE INDNT='D' "
[/CODE]

Regards
Veena

[debasisdas]

Try to use this for access database using DAO

[code=vb]

Dim W As Workspace
Dim D As Database
Dim T As TableDef
Dim F(5) As Field

Private Sub Command1_Click( )
Set W = DBEngine.Worksp aces(0)
Set D = W.CreateDatabas e("D:\EMP.MDB ", dbLangGeneral)
Set T = D.CreateTableDe f("EMPLOYEE")
Set F(0) = T.CreateField(" EMPNO", dbInteger)
Set F(1) = T.CreateField(" ENAME", dbText, 20)
Set F(2) = T.CreateField(" DEPTNO", dbInteger)
Set F(3) = T.CreateField(" SALARY", dbSingle)
Set F(4) = T.CreateField(" JOINDATE", dbDate)
T.Fields.Append F(0)
T.Fields.Append F(1)
T.Fields.Append F(2)
T.Fields.Append F(3)
T.Fields.Append F(4)
D.TableDefs.App end T
MsgBox ("DATABASE CREATED")
End Sub

Private Sub Command2_Click( )
D.Close
Set D = Nothing
Unload Me
End Sub


[/code]