Linked ODBC Query

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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.

Reply | Quote