Create and execute view using VBA

[lradael]

Hi

I've a problem creating and executing a view using VBA.
This is my code:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "CREATE VIEW TEST AS SELECT * FROM MYTABLE"
cmd.Execute
Set cmd = Nothing
DoCmd.OpenView "TEST", acNormal, acEdit

When I execute this code, Access can't find the new view and show an error message box (error code 7874).

I use MSDE 2000 SP3 and Ms Access 2000 SP3.

Anyone can help me?

Thanks
Luca

 

[cmmrfrds]

Are you using NT Authentication or sql server Authentication?

Who is the database owner? One of the above?

Does the login id have permissions to create a View?

Unless your login is the database owner or it is a member of the admin role, then all the objects must be prefixed with dbo. It is a good idea to always prefix the objects with dbo anyway. i.e. dbo.test

 

[lradael]

Thanks for your reply.

I've enought rights to create the view.
In fact the view was created correctly but is not visible until I refresh the Views Window.
I think is a problem about the refresh of the database schema.
I've tried calling the Application.RefreshDatabaseWindow method but it doesn't work.

Have you any ideas?

Thanks
Luca