G
Guest
Greetings!
I have searched the net everywhere, and I can't find a good solution on how
to alter the 'Select' statement of a SQL Server view from within a ADP
project using VBA. Strangely enough some tutorials expound using ADOX's
catalogue object linked to "CurrentProject.Connection". But this gives
errors when on the next line you try and set a command to a SQL View. The
error is: "Object or provider is not capable of performing requested
operation". Then I checked under MS and they say that you cannot use ADOX
views collection with SQL Server (KB292791).
This whole things does not make sense: why do some tutotrials and newsgroups
say youu should use the currentconnection when MS says you can't. Am I
missing something?
Another newsgroup said you should try creating a new connection and that
currentproject.connection is the problem. tried that too, same problem. The
only thing that worked was docmd.runsql "ALTER VIEW....". But is that the
ONLY way to change the querie's SELCT statement.
PS: followiing the VBA code that does not work!
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryStaffListQuery").Command
cmd.CommandText = strSQL
Set cat.Views("qryStaffListQuery").Command = cmd
Set cat = Nothing
I have searched the net everywhere, and I can't find a good solution on how
to alter the 'Select' statement of a SQL Server view from within a ADP
project using VBA. Strangely enough some tutorials expound using ADOX's
catalogue object linked to "CurrentProject.Connection". But this gives
errors when on the next line you try and set a command to a SQL View. The
error is: "Object or provider is not capable of performing requested
operation". Then I checked under MS and they say that you cannot use ADOX
views collection with SQL Server (KB292791).
This whole things does not make sense: why do some tutotrials and newsgroups
say youu should use the currentconnection when MS says you can't. Am I
missing something?
Another newsgroup said you should try creating a new connection and that
currentproject.connection is the problem. tried that too, same problem. The
only thing that worked was docmd.runsql "ALTER VIEW....". But is that the
ONLY way to change the querie's SELCT statement.
PS: followiing the VBA code that does not work!
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryStaffListQuery").Command
cmd.CommandText = strSQL
Set cat.Views("qryStaffListQuery").Command = cmd
Set cat = Nothing