Make-Table query and macro

P

Paul Emmons

Using Access95, I have a make-table query whose SQL reads:

"Select distinct zid.crc into [Zid Distinct CRC] from Zid;"

Two questions:

1) I think it would be better if the field in the created table were indexed for faster lookup.
This code does not produce an indexed field. How can I get this to happen?

2) I would also like a macro (or VBA) which would check the times that both tables were last updated
or edited, and if the status of Zid is later than the status of "Zid Distinct CRC" would run the
above query. Is this feasible?

Thank you for your help.
 
G

Geoff

Paul,

I've not used Access 95, but I think the comments below will still apply.

Yes - you can use VBA to do both the things you want, ie remake the table
when it's out-of-date and index the table.

You'll need a reference to "Microsoft DAO" to run the sample code below. To
set up the reference, in the VBA editor, open the Tools menu, click
References, and select "Microsoft DAO" (whichever is the latest version on
your computer).

Copy and paste the following code into a module.

Note the interesting caveat about the LastUpdated property.

Geoff

Public Sub UpdateZidDistinctTable()

' This procedure needs a reference to DAO.
' (Open Tools menu, click References, and
' check Microsoft DAO).

' Declare constants for table names:
Const STRC_ZID As String = "ZID"
Const STRC_ZID_DISTINCT As String = "Zid Distinct CRC"

' Declare variables:
Dim objDB As DAO.Database
Dim objTBL As DAO.TableDef
Dim objTBL_ZID As DAO.TableDef
Dim objTBL_ZID_DISTINCT As DAO.TableDef
Dim objQDF As DAO.QueryDef
Dim objIDX As DAO.Index
Dim objFLD As DAO.Field

Dim blnZIDExists As Boolean
Dim blnZIDDistinctExists As Boolean
Dim datZIDLastUpdate As Date
Dim datZIDDistinctLastUpdate As Date

' Ensure all tables show:
Access.Application.RefreshDatabaseWindow

' Point to database:
Set objDB = CurrentDb()

' Point to ZID table if it exists:
blnZIDExists = False
For Each objTBL In objDB.TableDefs
If objTBL.Name = STRC_ZID Then
blnZIDExists = True
Set objTBL_ZID = objTBL
Exit For
End If
Next
If blnZIDExists = False Then GoTo ZID_TableNotFound

' Point to ZID DISTINCT table if it exists:
blnZIDDistinctExists = False
For Each objTBL In objDB.TableDefs
If objTBL.Name = STRC_ZID_DISTINCT Then
blnZIDDistinctExists = True
Set objTBL_ZID_DISTINCT = objTBL
Exit For
End If
Next

' If "Distinct" table exists, compare LastUpdated dates.
' Comparing the LastUpdated property of the two tables
' works OK when changes to the ZID table records are SAVED
' by clicking the SAVE toolbar button, but not if the table
' is closed without saving the changes to records. If the
' table is closed without saving changes, then the
' LastUpdated property of the table won't be updated even
' though Access will save the changes to the records
' automatically. You can force this procedure to run without
' changing any records (in the ZID table) simply by opening
' the table and clicking the SAVE toolbar button. This will
' update the LastUpdated property:
If blnZIDDistinctExists = True Then
datZIDLastUpdate = objTBL_ZID.LastUpdated
datZIDDistinctLastUpdate = objTBL_ZID_DISTINCT.LastUpdated
If datZIDLastUpdate <= datZIDDistinctLastUpdate Then
GoTo UpdateNotNeeded
End If
End If

' The Distinct table does not exist or it needs updating.

' If "Distinct" table exists, delete it:
If blnZIDDistinctExists Then
objDB.TableDefs.Delete STRC_ZID_DISTINCT
Access.Application.RefreshDatabaseWindow
End If

' Create temporary Make-Table query (by not naming it):
Set objQDF = objDB.CreateQueryDef("")

' Set query's SQL:
objQDF.SQL = _
"SELECT DISTINCT " & STRC_ZID & ".CRC INTO " _
& "[" & STRC_ZID_DISTINCT & "] FROM " & STRC_ZID & ";"

' Run the query:
objQDF.Execute

' Refresh database window and TableDefs collection:
Access.Application.RefreshDatabaseWindow
objDB.TableDefs.Refresh

' Point to the new table:
Set objTBL_ZID_DISTINCT = objDB.TableDefs(STRC_ZID_DISTINCT)

' Create an index for the new table called "CRC Index":
Set objIDX = objTBL_ZID_DISTINCT.CreateIndex("CRC Index")

' If every value in the index is unique,
' then include next code line:
'objIDX.Unique = True

' If the index represents the Primary Key,
' then include the next code line:
'objIDX.Primary = True

' Add the CRC field to index:
Set objFLD = objIDX.CreateField("CRC")

' Append field to index:
objIDX.Fields.Append objFLD

' Append index to table:
objTBL_ZID_DISTINCT.Indexes.Append objIDX

Bye:

MsgBox "Finished.", vbOKOnly + vbInformation, _
"Information" & Space(30)
GoSub CleanUp
Exit Sub

ZID_TableNotFound:

MsgBox "The ZID table was not found.", _
vbOKOnly + vbInformation, "Finished"
GoTo Bye

UpdateNotNeeded:

MsgBox "The table Zid Distinct CRC is up-to-date.", _
vbOKOnly + vbInformation, "Finished"
GoTo Bye

CleanUp:

' Destroy object variables:
Set objFLD = Nothing
Set objIDX = Nothing
Set objQDF = Nothing
Set objTBL_ZID_DISTINCT = Nothing
Set objTBL_ZID = Nothing
Set objTBL = Nothing
Set objDB = Nothing
Return

End Sub




Paul Emmons said:
Using Access95, I have a make-table query whose SQL reads:

"Select distinct zid.crc into [Zid Distinct CRC] from Zid;"

Two questions:

1) I think it would be better if the field in the created table were
indexed for faster lookup.
This code does not produce an indexed field. How can I get this to
happen?

2) I would also like a macro (or VBA) which would check the times that
both tables were last updated
or edited, and if the status of Zid is later than the status of "Zid
Distinct CRC" would run the
above query. Is this feasible?

Thank you for your help.
 
P

Paul Emmons

Wow! Thanks, Geoff, I'll try it!

I wasn't expecting anyone to go to so much trouble for my question.

Studying it and adapting it (if any adaptation to Access 95 is needed) should be a good learning
experience.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top