It is always a good idea to delete a table, especially for temporary tables, before you start creating one. The question is how do you determine if a table exists?
Two methods will be examined, INFORMATION_SCHEMA and OBJECT_ID.
INFORMATION_SCHEMA
This is the easiest and most forward compatible way to get what you want. It is actually a view based on existing stored procedures/functions.The schema describes the structure of the database, including tables, view, etc. It will tell you for example, a list of tables, a list of the parameters of a particular stored procedure, a list of all user-defined functions, and so on.
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE SCHEMA_NAME = 'MySchema' AND TABLE_NAME = 'MyTable'))
BEGIN
--Do Stuff
END
To get a list of stored procedures/functions.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES -- 'FUNCTIONS'
For curious mind wants to know how INFORMATION_SCHEMA really works, here is the way to see how Microsoft implemented it
execute sp_helptext "information_schema.tables"
-- Identifies tables accessible to the current user
CREATE VIEW INFORMATION_SCHEMA.TABLES
AS
SELECT
db_name() AS TABLE_CATALOG,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
FROM
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type IN ('U', 'V')
OBJECT_ID
It actually is a system stored procedure. An issue of being a sys sproc is that it tend to change version by version, in terms of input parameters and return value(s). It will tell you if the object exists but not guarantee it is a table type. there is the syntax.
IF OBJECT_ID('*objectName*') IS NOT NULL
When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').
Conclusion:
Use INFORMATION_SCHEMA whenever possible and only use systems stored procedure like OBJECT_ID, when the info you want is not in the INFORMATION_SCHEMA.
Category Archives: Database
Access: How to print a report footer at the end of the last page.
In MS Access, a report footer only shows at the end of the report, not the end of last page. In my Microsection report, I want a fixed height signature area to be at the end of last page. In that way it just looks professional.
Microsoft KB 208979 introduced three methods to achieve it.
The 2nd method is the one I used, it involves using the repot’s MoveLayout, PrintSectoin, and NextRecord properties.
Create a function named SetGrpFtrLoc in the repot’s module section.
Function SetGrpFtrLoc(Rpt as Report, GrpFtrLoc as Double)
GrpFtrLoc=GrpFtrLoc*1440 'Convert from inches to twips.
If Rpt.top < GrpFtrLoc Then 'Not at location yet, so
Rpt.movelayout = True 'move to next print location.
Rpt.nextrecord = False 'Do not go to next record.
Rpt.printsection = False 'Do not print the section.
End If 'Until the required offset is reached
End Function
Create a dummy group and set it to “One to Many†relation so that all sections will show up. Set the dummy group to be the very top level in the “grouping and sorting†of the report and show only the footer of the dummy group. Set the dummy footer properties as follow:
Height: 0.3 in. (or the height of the signature area)
ForceNewPage : After Section
OnFomat: = SetGrpFtrLoc([Report], 10)
The parameter 10 indicated that I want the signature area to begin at least 10 inches from the top of the page. The calculation is following: my page margin is 0.25, page footer is about 0.33 and the page is 11 inch high. Some margin need to be preserved between signature area and page footer.
Different ways to achieve this can be found at various forums, but I found the one from Microsoft KB is the most straight forward one.
You can not add report footer if you go this way! The footer will appear after the end of group footer.
Use Cross Join In MS Access
Our Microsection report needs to have same serial number for each and every hole type. Using VBA to generate a table, which has a field of hole_type and a field of SN, sure will work. However; a proper and elegant way is to use a cross join to link two tables.
Tbl_Hole:
H_Type       Text   (PTH; Blind_Via; Micro_Via etc)
H_Name      Text   (Thermal Stress; As is; L1-3; L8-9 etc)
Tbl_SN:
SN          Text   (1, 2, 3, 4, 5, etc)
SQL (qry_HoleType_SN):
SELECT *
FROM tbl_Hole, tbl_SN;
From a graphical query builder
Add both tables and ensure there is no link between them.
Below is what I got.
H_Type H_Name SN
Blind Via L1-2 1
Blind Via L1-2 2
Blind Via L1-2 3
Blind Via L1-2 4
Blind Via L1-2 5
Blind Via L8-9 1
Blind Via L8-9 2
Blind Via L8-9 3
Blind Via L8-9 4
Blind Via L8-9 5
PTH As Is 1
PTH As Is 2
PTH As Is 3
PTH As Is 4
PTH As Is 5
PTH Thermal Stress X 1
PTH Thermal Stress X 2
PTH Thermal Stress X 3
PTH Thermal Stress X 4
PTH Thermal Stress X 5
PTH Thermal Stress Y 1
PTH Thermal Stress Y 2
PTH Thermal Stress Y 3
PTH Thermal Stress Y 4
PTH Thermal Stress Y 5