Create View using VB

[JohnR]

I recently moved to ADP and SQL server. In regular Access I frequently used
the command in VBA to creat a QueryDef.

Set qdf = CurrentDB.CreateQueryDef("Query Name", "Select * from table")

Is there any comparible command to create a view in VBA?

Thank you in advance

JohnR

 

[Sylvain Lafontaine]

The CreateQueryDef command never created a view on SQL-Server; you can open
the later with Enterprise Manager or SSMS/E and see that nothing has been
created there. Queries definitions and pass-through queries have always
been strictly local to the MDB file and have never showed up on the
SQL-Server as views, functions or stored procedures.

 

[JohnR]

I am aware of that.

What I need to know is if there is some command that I could use in VBA that
would create a view on SQL server.

 

[Sylvain Lafontaine]

You can use the currentProject.Connection.Execute method to execute any
T-SQL instruction on the SQL-Server. This is a standard Execute method of
the ADO Connection object. Some are also using the DoCmd.RunSQL command but
this is not the recommended way with ADP. See:

http://www.activeserverpages.ru/ADO/dameth02_1.htm

You may have some timing issues if you want the newly created View to show
up in the Database window using the Application.RefreshDatabaseWindow
command.

 

[aaron.kempf]

CREATE VIEW MYVIEW AS SELECT * FROM SYSOBJECTS