Index table created with Make-table Query option 1

[ksigvart]

After making a query in the design view and choosing the “Make-table Query” option from the Query menu, the query creates a table out of its results. However, that table created by the first query is one that needs to be used in a second query so it must be indexed to be related to the other tables pulled into that second query. How can I index a table that is created by the Make-table Query option?

 

[tlbroadbent]

In Access 97 and 2000, create an index on a table with Data Definition query.

Create Index IdxName On TblName (ColName);

Read more in Access Help. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[ksigvart]

Thanks for the idea. I am actually working in Access 2002. the line of code you mentioned does not seem to be accepted. Maybe I have a syntax error and need quotes or something. Here is what the help file says for CreateIndex, it is somewhat different than what you mentioned:
CreateIndex Method

Creates a new Index object (Microsoft Jet workspaces only).

Syntax

Set index = tabledef.CreateIndex (name)

The CreateIndex method syntax has these parts.

Part Description
index An object variable that represents the index you want to create.
tabledef An object variable that represents the TableDef object you want to use to create the new Index object.
name Optional. A Variant (String subtype) that uniquely names the new Index object. See the Name property for details on valid Index names.



Remarks

You can use the CreateIndex method to create a new Index object for a TableDef object. If you omit the optional name part when you use CreateIndex, you can use an appropriate assignment statement to set or reset the Name property before you append the new object to a collection. After you append the object, you may or may not be able to set its Name property, depending on the type of object that contains the Indexes collection. See the Name property topic for more details.

If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method.

To remove an Index object from a collection, use the Delete method on the collection.


HERE Is the section of code I am working on:
Sub SendSBTInvoice(inv As Integer) 'based on Sub SendBWInvoice KS 4/01

Dim dbs, db As Database
Dim qry, qryUpd, qryInvUpd, qryLn As QueryDef
Dim rst As Recordset
Dim fHandle As Integer
Dim strFileName As String
Dim printfreight As String
Dim idx As Index
Dim tblDef, tblR As TableDef
Dim idxOrderLineID As Index
Dim fld As Field

DoCmd.Hourglass True
DoCmd.Echo False, "Open Databases"

Set dbs = CurrentDb
Set qryLn = dbs.QueryDefs("qryOrderHeadLine&quot

Set rst = qryLn.OpenRecordset()
Set tblR = rst.CreateTableDef("tblCmbOrdHeadLine&quot 'Followed example in printout from tec-tips webpage
'but it doesn't seem to accept qryLn in place of dbs so will try another option
DoCmd.OpenTable "tblCmbOrdHeadLine"

'Create an index and set its properties NEITER TRY NUMBER ONE NOR TWO SEEMED TO BE WORKING

'TRY NUMBER ONE FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
'Set idx = tblR.CreateIndex("idxOrderLineID&quot
'idx.Primary = True
'idx.Required = True
'idx.Unique = True

'TRY NUMBER TWO FROM SOMEONE ELSE'S QUESTUION ON TEC-TIPS
'Create Index "idxOrderLineID" On "tblCmbOrdHeadLine (OrderLineID)"

'Add a field to the index Not sure if this step is necessary
Set fld = idx.CreateField("OrderLineID&quot
idx.Fields.Append fld

'Add the index to the TableDef
tblR.Indexes.Append idx

Set qry = dbs.QueryDefs("qrysbtinvoice&quot
qry.PARAMETERS![invno] = inv
Set rst = qry.OpenRecordset()

DoCmd.Echo False, "Send Data To SBT"

If rst.RecordCount > 0 Then
rst.MoveFirst
strFileName = lookup("filename", "tblfilelocation", "fileid", "SBTOUT&quot
fHandle = stdFileOpen(strFileName, "Append&quot

If fHandle > 0 Then
'If rst!EDIFreight = True And rst!sumoftraderfreight > 0 Then
If rst!BusinessWorksID = "Finger" Then ' 10/20 Do only for FingerHut
printfreight = rst!sumoftraderfreight
Else
printfreight = rst!sumoffreight
End If


If rst!EDIFreight = True Then ' pass freight charges
Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), "", CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), CDec(printfreight)
Else ' Don't pass freight charges
If rst!BusinessWorksID = "12SPIEGEL" Then ' Pass handleing charge for Speigal 5/99
Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), CDec(rst!sumofhandcharge), ""
Else
Write #fHandle, rst!SBTid, CInt(rst!InvoiceNo), CStr(rst!PurchaseOrderNum), CStr(rst!UPSShipDate), CDec(rst!sumofsellprice), "", ""
End If
End If



Close fHandle ' Close file.
DoCmd.Echo True

DoCmd.Echo False, "Update Orders"
Set qryUpd = dbs.QueryDefs("qryStatToDone&quot
qryUpd.PARAMETERS![invno] = inv
qryUpd.Execute 'incriments through records to change them

DoCmd.Echo False, "Update Invoice"
Set qryInvUpd = dbs.QueryDefs("qryInvoiceToDoneSBT&quot
qryInvUpd.PARAMETERS![invno] = inv
qryInvUpd.Execute 'incriments through records to change them

MsgBox "Finished Processing SBT Output File"
End If 'file opened
dbs.Close
Else
MsgBox "No Reccords To Send"
End If

DoCmd.Echo True
DoCmd.Hourglass False

End Sub