Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linked ODBC Query

    I have many queries in an Access2000 database that I insert into Word as fields using a simple SELECT sql statement like 'SELECT * FROM qryMYQUERY WHERE ID = 1'.

    This works fine, except I do not want to show the ID field in the resulting Word table. I want to be able to change the qryMYQUERY in Access (including changing fields) and have it update in word. Basically, I want a query more like 'SELECT * EXCEPT ID from qryMYQUERY WHERE ID = 3'. (or a visibility option like Access allows)

    Apparently there is no 'EXCEPT' reserved word in the SQL Language.

    The only field that I guarantee to be in the query is the ID field. I don't want to read in the field names and 'build' the query because then I would lose the flexibility of keeping the query in Access.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Pune
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC Query

    This question may get a more educated answer and more discussion on the VBA Board. Moderator may please consider this.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC Query

    Colleen,

    Rajesh is probably right- this is really a VBA question- except that I'd propose a Word solution.

    If you're populating a Word table, put in the ID- and then delete the column of the table containing the ID- eg, if the ID was column 2 for the first table in the Word document:
    ActiveDocument.Tables(1).Columns(2).Delete

    Cross-post in the VBA or Access forum for some more expert help- but make a reference to this thread to help anybody looking for a similar answer later on.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Linked ODBC Query

    Hi
    It is my belief that you should be more explict in you select statement. Select * selects all fields.

    In the FROM clause you can specify a field even if you havent explicitly named it in the select clause.

    e.g
    ---------
    SELECT tblProperty.Address1, tblProperty.Town, tblPropHazard.Hazard, tblOwnershipDetails.OwnerID
    FROM (tblProperty INNER JOIN tblPropHazard ON tblProperty.PropId = tblPropHazard.PropId) INNER JOIN (tblOwners INNER JOIN tblOwnershipDetails ON tblOwners.OwnerId = tblOwnershipDetails.OwnerID) ON tblProperty.PropId = tblOwnershipDetails.PropId;
    ------------------------

    Note that tblProperty.PropID is not explicitly listed in the select clause.
    Hope I have helped
    Geof

  5. #5
    BAM
    Guest

    Re: Linked ODBC Query

    Hi Colleen,

    I don't know what you want exactly, but as another possibility, but you might try using a DDE field instead. For example:
    { DDE MSAccess "C:MyStuffTest.MDB;qryMyQuery" All r }

    You could also add in a Parameter for the ID field in the Query so when you refresh the field, the Parameter will prompt first so you can enter your criteria.

    The only drawbacks to this are the Access Database needs to be open and if you use a parameter, you need to switch to the Access task to answer the prompt.

    Other than that, I believe you would need to use VBA for this since as you pointed out, SELECT * means select all fields from the table, so therefore you get all fields. :-)
    ~~~~~~~~~~~~
    Cheers!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC Query

    This is a way I might do it using ADO. If you're using DAO, it might be similar:

    <pre>Dim cnnConnection As Connection
    Dim rstRecordset As Recordset
    Dim strSQL As String
    Dim strOutput As String
    Dim i As Integer

    Set cnnConnection = New ADODB.Connection
    With cnnConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "D:FOLDERMYDATABASE.MDB"
    End With

    Set rstRecordset = New ADODB.Recordset

    With rstRecordset
    strSQL = "select * from myTable"
    .Open Source:=strSQL, ActiveConnection:=cnnConnection, _
    CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic
    While Not .EOF
    strOutput = ""
    For i = 1 To .Fields.Count - 1
    If .Fields(i).Name <> "ID" Then
    strOutput = strOutput & .Fields(i).Value & vbTab
    End If
    Next
    Selection.InsertAfter strOutput & vbCrLf
    .MoveNext
    Wend
    .Close
    End With

    Set cnnConnection = Nothing
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    New Lounger
    Join Date
    Nov 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linked ODBC Query

    Thanks for the responses. I think I will forward the question to the VBA board also as Rajesh suggested. My current workaround is to do as gwhitfield suggests - delete the ID column entitled ID. This is not too bad, since I have to format the tables anyway after a field update but it just feels kludgey to me.

    The Word docs that I create easily have upwards of 500 of these query fields so options like the PARAMETER are not viable and I would still have the problem of displaying the ID field.

    The ADO code provided by gwhitfield would give me the proper query but would eliminate the flexibility that I want to retain to change the query after the initial document run.

Posting Permissions

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