Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    France
    Posts
    4

    Question Unanswered: PB using UNION ALL with VB6 and SQL-server

    I'm working on a softwear and I have to create some Update. This softwear used first MS Access as a database, and now I have to create the code which allows it to use MS SQL-server. This softwear is created in VB6.

    I've got some error when I'm using the UNION ALL function. This error appears:
    "incorrect syntax towards the key word ' union' "

    Here is the code in VB6 :

    " GOpenLocalDb (FOR_READ) ' open the database

    SQLStmt = "select * from ol_table_temp"
    SQLStmt = SQLStmt & " where [Subject_no] = " & dlr_subject & " "
    ord$ = SQL_Order()
    SQLStmt = SQLStmt & ord$

    'dalafouzos: 24/09/2002 I added the lines below to include the DLR entries in the ol_old table
    SQLStmt = SQLStmt & " union all select * from ol_table_old where [Subject_no] = " & dlr_subject & " "
    ord$ = SQL_Order()
    SQLStmt = SQLStmt & ord$

    'GOpenLocalDb (FOR_READ) ' open the database
    Set DLRset = New ADODB.Recordset
    DLRset.CursorLocation = adUseClient
    DLRset.Open SQLStmt, g_objConn, adOpenKeyset, adLockReadOnly, adCmdText "

    when the error appears the value of SQLStmt is :

    "select * from ol_table_temp where [Subject_no] = 1020 Order by [Date] DESC, [Time] DESC union all select * from ol_table_old where [Subject_no] = 1020 Order by [Date] DESC, [Time] DESC "

    Thanks for your help...

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    and both tables has the same DDL?

  3. #3
    Join Date
    Mar 2004
    Location
    France
    Posts
    4
    I think so. Both of them are similar...but how can I see if they have the same DDL? because I'm not sure as I didn't have to create these tables.

    But this syntaxe works well with MS Access...the problem appears only with MS SQL-server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •