Alter View Error

The part with:

Alter view test2
 as

must be removed as Access won't allow this.
Only a "plain" SELECT/UPDATE/DELETE statement will work.

What is the update you want to perform, adding rows or updating values ?

Nic;o)

Hi John,

Try using an ADO.Command
https://www.experts-exchange.com/questions/20742009/How-to-redefine-a-view-using-ADO-Command.html

Alan

Hi Alan,
I changed
strSQLStmt = "alter table Test2  as SELECT A.contractid,Sum(B.chqamt) AS sum123, B.cleardt,1 as A " & _
" FROM Contract AS A ,ChequeDetails AS B Where A.ContractID = B.ContractID and " & _
" B.Status = True GROUP BY A.contractid,B.cleardt Order by B.cleardt " & _
" UNION SELECT A.contractid,Sum(A.Cashamt+A.ccamt) AS Cashamt, A.contractDt, 2 " & _
" FROM Contract AS A GROUP BY A.contractid,A.contractDt ORDER BY B.cleardt DESC "
Set Cmd = New ADODB.Command
With Cmd
    .ActiveConnection = connAlter
    .CommandText = strSQLStmt
    .CommandType = adCmdText
    .Execute
End With

Still It gives the same error

regards
John Philip

Hi John Philip,


Check your sql string, make sure you have spaces after commas for field sperators.
A.contractid,B.cleardt
-------------^-------
A.contractid,Sum(B.chqamt) AS sum123  etc...
--------------^-------------------
Also wondering if you should using a sql server connect string here?
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

Alan

Hi Alan,
I am using Access database here, not SQL Server
SQL Syntax is correct

Hi John,

Just thought because you are defining a new connection and not using currentproject.connection to modify a sql server view that that you might need the con to be sql driver.

Have you tried CurrentProject.Connection?

With Cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = strSQLStmt
    .CommandType = adCmdText
    .Execute
End With

Unfortunately I don't have a sql server here at home, but I have done this, it can be done. Can't remember if I used Adox, but I dont think so.

Try setting the cursorType for the conn, this has resolved things for me in the past.

Alan

Hi John,

This is an example of a create view procedure that I used to dynamically modify a view that was used as a recordsource for report that was attached to an email using sendobject.
Platform: Access 2000 ADP


Sub xxx()
  ' Reference: Microsoft ActiveX Data Objects Library 2.xx
   
  Dim sql As String
  'need to change this to ALTER VIEW second time round
  sql = "Create View vw_ReferralResultsMailReport As"
  sql = sql & " SELECT [First Name],[Last Name], Name, Sold, Product, Notes, ReferalID"
  sql = sql & " FROM Referral_Type INNER JOIN Customer ON"
  sql = sql & " Referral_Type.ReferralTypeID = Customer.[Referred to]"
  sql = sql & " AND Customer.ReferalID=" & Forms!frmReferralResults!txtReferralID
 
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
 
  cmd.ActiveConnection = CurrentProject.Connection
  cmd.CommandText = sql
  cmd.CommandType = adCmdText
  cmd.Execute , , adExecuteNoRecords
 
  Dim stDocName, strSubject, strSendTo, strCC, strBody As String
 
  stDocName = "rptReferralResultsforMail"
  stSubject = "Results of your Referral"
  stSendTo = Me!ReferredBY
  stBody = Me!txtFirstName & " " & Me!txtLastName
 
  DoCmd.SendObject acReport, stDocName, acFormatRTF, stSendTo, , , stSubject, stBody, False
 
End Sub

Alan :)

How goes the battle John Philip?

Hi Alan,
The problem is still persisting
I am not clear abt the Currentproject object?
I am not not trace that object
I am using a simple Access 2000 Database.

I always get the syntax error......

Please help

Regards
John Philip

Hi John Philip,

RE: this is created in database in remote place

Tell me something about the database in the remote place.
Is it an MDB or a sql server? Because you originally wanted to "Alter View" I assumed sql server.
How remote is the database, Lan, Wan?
Can I see your connection string please?
To use "Alter Table" you cannot use ADO, you must use either ADOX or DAO.
To use "Alter View" you can use ADO. But not on WAN connection. I have had very little success with any WAN connections.


Tell me something about the current database you are using.
Is the application you are using an ADP or an MDB?
Again I assumed ADP because of the original "Alter View"

Most of this information can be got through the Access CurrentProject Class

Re: Currentproject object

In any debug window

?Currentproject.Connection

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Documents and Settings\Alan\Desktop\Zed\C-Drive\My Documents\db1.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

?Currentproject.BaseConnectionString

PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Documents and Settings\Alan\Desktop\Zed\C-Drive\My Documents\db1.mdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW

?Currentproject.FullName

C:\Documents and Settings\Alan\Desktop\Zed\C-Drive\My Documents\db1.mdb

?Currentproject.Name

db1.mdb

?Currentproject.ProjectType

2

****Note****
Blank out passwords before posting connection here please. pwd=***** ok.


Happy to help :)

Alan

Hi Alan,
My Software is deployed in a remote place.
I dont have any access to that. its in (*.MDB) in Access 200 format.
Now i am continoulsy working on this software.
So any change which i make in the database has to be reflected in the database.

Like eg 'qry1'  has some query i want to change (add one more field in that or removed/added a condition to that);

So all these command will put in an Vb application and once the user executes in his machine , his database will get updated.
I can create a new view, but not able to drop existing one.
and not weven able to alter the view also

All the time i get a error message like
'Syntax error in ALTER TABLE statement'

Regards
John

John,

You need ADOX for this.
Reference: Microsoft ADO Ext for DDL and Security
Want some samples?
Looking...

Alan

Hi John,

Lets see if we can access the table properties before we start trying to modify them

Public Function doEnumerateTables()
' Reference: Microsoft ADO Ext. 2.7 for DDL and Security
  Dim cat As New ADOX.Catalog
  Dim tbl As ADOX.Table

  Dim i As Integer
  Dim j As Integer
  ' Open the Catalog
  cat.ActiveConnection = conn1              '<= your connection object
  For i = 0 To cat.Tables.Count - 1
    Debug.Print "+ " & UCase(cat.Tables(i).Name)
    Set tbl = cat.Tables(i)
    For j = 0 To tbl.Columns.Count - 1
      Debug.Print "   -" & tbl.Columns(j).Name
    Next j
  Next i
End Function

Alan :)

Hi Alan,
Yes it is giving the follwing display, but starting from queries of the database.
+ ACTUAL_COMMISSION
   -amt
   -commrate
   -EmpId
   -Name
   -sim1
+ AVERAGE ON CONTRACT AMOUNT
   -AvgContract
   -clearmonth
   -clearyear
   -cntContract
   -ContAmount
   -n1
+ AVG_IRO
   -avgcontract
   -clearmonth
   -clearyear
   -contAmount
   -contcontract
+ AVG_MARKETING
   -avgcontract
   -clearmonth
   -clearyear


Regards
John Philip

Hi John,

Well that is good news about the connection, but the 'starting at queries' is a bit puzzling?
We explicitly referenced cat.Tables

I'm wondering if your previous attempts using 'Alter Table' were in fact succesful, and you have created tables with names of queries in your db?

Can you open the db and check the tables in databse window?

Alan

Hi,
Even we specify the tables in Access it will show both Tables & Queries from the access database.

Regards
John Philip

Back in 10 minutes :)

Hi John,

Sorry, the phone line was needed for a phone call. On dial up here.

Quite right about the queries, anyway, lets move on.
What is it that you want to modify, a table or a query?

Alan

hi alan,
Well i want to modify an existing query.
Regards,
John Philip

Hi John,

Sorry about the delay, all my code for this sort of stuff is at work, so I had to go looking for an example.

Here ya go a nice short example:

Using ADOX to Create and Destroy Queries on the Fly
http://www.utteraccess.com/forums/access/access172655.html

Alan

Hi John,

I suggest you try it with a simple query first, like "select * from Table1"  prove the technology before jumping in to your complex Union queries.

Hoping to hear good news soon :)

Alan

Hi John,

How you doing, I'm getting some unexpected results here using Access 2000
Getting no Errors but...
Cant find the new query in the databse window.
If I run the function doEnumerateTables the new query is in the catalog.
If I open mySysObjects it is there too but columns lvExtra and lvprop are empty. hmm.

I have done this succesfully with sql server before and was led to believe it could be done with access catalogs, maybe I am missing something.

Alan

Hi Alan,
Eureka,
I found one thing, my query is union query and access is not detecting the union queries.in tables or views collection.
Here I can Create the query , but i am not able delete since it is not detecting the union query.

    strSQLStmt = "SELECT A.contractid, sum(B.chqamt) AS sum123,  " & _
" B.cleardt, 1 as A FROM Contract AS A ,ChequeDetails AS B Where A.ContractID = B.ContractID and " & _
" B.Status = True GROUP BY A.contractid, B.cleardt Order by B.cleardt " & _
" UNION SELECT A.contractid, Sum(A.Cashamt + A.ccamt) AS Cashamt, A.contractDt, 2 " & _
" FROM Contract AS A GROUP BY A.contractid, A.contractDt ORDER BY B.cleardt DESC  "

catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= D:\bOUNCE\accounts.mdb;Jet OLEDB:Database Password=india9876;"
catDB.Views.Delete "test3"
cmd.CommandText = strSQLStmt
catDB.Views.Append "test5", cmd

Hi John,

Eureka is good, but I'm not convinced yet.
Think we are getting closer.

Found the motherload of information at msdn
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adoddlx2_12.asp

Ran this sub:

Public Sub testIt()
Dim strSQLStmt As String
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command

Set catDB = New ADOX.Catalog
Set cmd = New ADODB.Command
strSQLStmt = "SELECT Table1.* FROM Table1"

catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= C:\temp\db5.mdb;"

' remmed this first time through, got an error "cant find it" second time when un-remmed

catDB.Views.Delete "Query1"   catDB.Views.Refresh

cmd.CommandText = strSQLStmt
catDB.Views.Append "Query1", cmd
catDB.Views.Refresh


End Sub

Same result as before:
Cant find the new query in the databse window.
If I run the function doEnumerateTables the new query is in the catalog.
If I open mySysObjects it is there too but columns lvExtra and lvprop are empty

Will keep investigating.

Alan

Hi alam,
I found out, it has to done in the following way:

Thank You Alan, it was wonderful for your help. It is all the limitation of access database.

Now i have sent exe, so they will update the database first.
Regards
John Philip

CatDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= accounts.mdb;Jet OLEDB:Database Password=india9876;"
CatDB.Procedures.Delete "Total_Sales_Realised"
lblDisplay.Caption = "Query Sales removed"
Me.Refresh
Set cmd = New ADODB.Command
strSQLStmt = "SELECT A.contractid, sum(B.chqamt) AS sum123,  " & _
" B.cleardt, 1 as A FROM Contract AS A ,ChequeDetails AS B Where A.ContractID = B.ContractID and " & _
" B.Status = True GROUP BY A.contractid, B.cleardt Order by B.cleardt " & _
" UNION SELECT A.contractid, Sum(A.Cashamt + A.ccamt) AS Cashamt, A.contractDt, 2 " & _
" FROM Contract AS A GROUP BY A.contractid, A.contractDt ORDER BY B.cleardt DESC  "
cmd.CommandText = strSQLStmt
CatDB.Views.Append "Total_Sales_Realised_New", cmd
CatDB.Views.Refresh

Hi John,

This looks interesting, haven't tried it yet.

Information About Jet 4.0 Service Pack 8
http://support.microsoft.com/?kbid=829558

Thanks for posting your final code, still couldn't get it work properly on my home system.
It creates the the query and append to to the catalogue, but if you open the remote mdb and try to execute the query, Access can't find it. But if you expose the catalog using ADOX it is there. Go Figure.

No problem at all with sql server.

It worked propeerly using VB6 exe, you say, that is interesting.

Alan

Hi All,
Sorry for the wrong post.
All these will work only with access 2002 or XP,
in 2000 version it will execute.


Regards
John Philip

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer

A request from the asker for a reduction in points and award to alanwarren might be considered a gratuity for diligent assistance rendered in lieu of alan having produced the working solution.

Just a thought.

Hi John, Jack.

Actually got this working on an asp thread.
https://www.experts-exchange.com/questions/20834351/mdb-structure-synchronization-script.html#10000844

        Set colNew = Nothing
        Set colNew = Server.CreateObject("ADOX.Column")
        colNew.Name = cat.Tables(i).Columns(j).Name
        colNew.Type = cat.Tables(i).Columns(j).Type
        colNew.DefinedSize = cat.Tables(i).Columns(j).DefinedSize
        colNew.Attributes = cat.Tables(i).Columns(j).Attributes
        tblNew.Columns.Append colNew.Name, colNew.Type, colNew.DefinedSize

Alan :)

With the last entry recommendation changes to:
Points for alanwarren.

sometimes it just takes a brainstorm.