Solved

Alter View Error

Posted on 2003-10-31
32
834 Views
Last Modified: 2008-02-01
Hi All,
I am using a Access Table; in this i have createad a query
as 'test2' name , now this is created in database in remote place i am doing updates and i want set in an application , so when the user executes teh updates automatically takes place.

But when i execute this statemnt ast conn1.execute
it gives me an error like the following.
'[Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.'


--------------------
Alter view 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

Regards
John Philip
0
Comment
Question by:jshanoo
  • 17
  • 10
  • 3
  • +2
32 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 9662361
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)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9662479
0
 

Author Comment

by:jshanoo
ID: 9662566
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9662585
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
0
 

Author Comment

by:jshanoo
ID: 9662630
Hi Alan,
I am using Access database here, not SQL Server
SQL Syntax is correct
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9662665
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9662738
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 :)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9664793
How goes the battle John Philip?
0
 

Author Comment

by:jshanoo
ID: 9668890
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669212
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



0
 

Author Comment

by:jshanoo
ID: 9669290
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

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669306
John,

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

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669324
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 :)
0
 

Author Comment

by:jshanoo
ID: 9669362
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669392
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

0
 

Author Comment

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

Regards
John Philip
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669483
Back in 10 minutes :)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669528
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

0
 

Author Comment

by:jshanoo
ID: 9669541
hi alan,
Well i want to modify an existing query.
Regards,
John Philip
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669613
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669630
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669727
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
0
 

Author Comment

by:jshanoo
ID: 9669735
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9669827
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


0
 

Author Comment

by:jshanoo
ID: 9670818
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9677413
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
0
 

Author Comment

by:jshanoo
ID: 9816423
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10011095
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10011107
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.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10013676


Hi John, Jack.

Actually got this working on an asp thread.
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20834351.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 :)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10014689
With the last entry recommendation changes to:
Points for alanwarren.

sometimes it just takes a brainstorm.
0
 

Accepted Solution

by:
SpazMODic earned 0 total points
ID: 10041887
PAQed, with points refunded (250)

SpazMODic
EE Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now