Table Name and Column Name as Parameters in MS Access
No, you can’t use table name and column name as parameters in MS Access, unfortunately.
Here is a solution: Use a uniformed names and structures in your automations, and copy your table into a temp table with a uniformed name. After process, you copy back and overwrite the temp table to its original table name. Reasonable labour work load.
Error Massage: “Operation must use an updateable query"
In MS Access, sometimes this error massage with error number 2950 may be a problem. It might be permission issue (see http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error), or an update from issue from joined table. For later, try following method to see if it does work:
UPDATE table SET field = 3
WHERE keyfield IN(SELECT keyfield FROM query)
;
It also can be caused by calculation within the UPDATE query. In this case, the best possible solution is to calculate before save into a temp table, then to join that temp table to get the calculated result.
Add Sequence Number to an Existing Table in MS Access
Add series number to an existing table can be done by following method:
SELECT t.myDate, (
SELECT Count(*)
FROM myTable AS s
WHERE t.myDate <= s.myDate) as myRank
FROM myTable AS t
However, if it does not work for you, try following option:
Alter Table Temp_SequenceChange Add Column SequenceNo AutoIncrement;
Cope With Deleted Records Recovery Issue in MS Access
MS Access does not have functionality to recover or UNDO or UNDELETE deleted records. It is easy to lose data when you mistakenly forget put () between AND and OR in WHERE clauses. There are several options to cope with it, for instance creates trail tables or backup whole database regularly. Here is another option which might be easier, better and more practicable:
1. Centralize all DELETE queries into one for better management purpose with unique query name. Since MS Access does not allow table and column name as parameters, this DELETE query will be hard coded every time when you try to delete any record.
2. Never run this query directly.
3. Put this DELETE as part of a Macro.
4. Within the Macro, before the DELETE query, put some backup queries for the table you are working with or all related key tables. The simplest way to code backup query is:
SELECT *
INTO BackupTable1
FROM WorkingTable
;
Thus, you only backup table before the deletion and can be recovered once you find the fatal mistake. You can only backup one copy only since the table name in Macro cannot be changed. Another stupidity of Microsoft.
Q&A: SQL Server
Checklist for Analyzing Slow-Running Queries?
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.
Q&A: Visual Basic 6
What is module?
variables and functions that are used throughout your program and on different forms can be stored in modules so that not to rewrite again each time the variables and functions when you need them in a form.
What is namespace?
A namespace is a collection of different classes. All VB applications are developed using classes from the .NET System namespace. The namespace with all the built-in VB functionality is the System namespace. All other namespaces are based on this System namespace. Namespaces organize the objects defined in an assembly. Assemblies can contain multiple namespaces, which can in turn contain other namespaces. Namespaces prevent ambiguity and simplify references when using large groups of objects such as class libraries.For example, the .NET Framework defines the ListBox class in the System.Windows.Forms namespace.
What is Lambda Expression?
A lambda expression is a function or subroutine without a name that can be used wherever a delegate is valid. Lambda expressions can be functions or subroutines and can be single-line or multi-line. You can pass values from the current scope to a lambda expression.
What is delegate?
A delegate is a form of object-oriented function pointer that allows a function to be invoked indirectly by way of a reference to the function. Delegates can be used to hook up event handlers and pass a procedure from one procedure to another. Delegates are objects that refer to methods. They are sometimes described as type-safe function pointers because they are similar to function pointers used in other programming languages. But unlike function pointers, Visual Basic delegates are a reference type based on the class System.Delegate. Delegates can reference both shared methods — methods that can be called without a specific instance of a class — and instance methods.
What is interface?
Interfaces define the properties, methods, and events that classes can implement. Interfaces allow you to define features as small groups of closely related properties, methods, and events; this reduces compatibility problems because you can develop enhanced implementations for your interfaces without jeopardizing existing code. You can add new features at any time by developing additional interfaces and implementations. There are several other reasons why you might want to use interfaces instead of class inheritance:
* Interfaces are better suited to situations in which your applications require many possibly unrelated object types to provide certain functionality.
* Interfaces are more flexible than base classes because you can define a single implementation that can implement multiple interfaces.
* Interfaces are better in situations in which you do not have to inherit implementation from a base class.
* Interfaces are useful when you cannot use class inheritance. For example, structures cannot inherit from classes, but they can implement interfaces.
What is assembly?
An assembly is the building block of a .NET application. It is a self describing collection of code, resources, and metadata (data about data, example, name, size, version of a file is metadata about that file). An Assembly is a complied and versioned collection of code and metadata that forms an atomic functional unit. Assemblies take the form of a dynamic link library (.dll) file or executable program file (.exe) but they differ as they contain the information found in a type library and the information about everything else needed to use an application or component. All .NET programs are constructed from these Assemblies. Assemblies are made of two parts: manifest, contains information about what is contained within the assembly and modules, internal files of IL code which are ready to run. When programming, we don't directly deal with assemblies as the CLR and the .NET framework takes care of that behind the scenes. The assembly file is visible in the Solution Explorer window of the project.
variables and functions that are used throughout your program and on different forms can be stored in modules so that not to rewrite again each time the variables and functions when you need them in a form.
What is namespace?
A namespace is a collection of different classes. All VB applications are developed using classes from the .NET System namespace. The namespace with all the built-in VB functionality is the System namespace. All other namespaces are based on this System namespace. Namespaces organize the objects defined in an assembly. Assemblies can contain multiple namespaces, which can in turn contain other namespaces. Namespaces prevent ambiguity and simplify references when using large groups of objects such as class libraries.For example, the .NET Framework defines the ListBox class in the System.Windows.Forms namespace.
What is Lambda Expression?
A lambda expression is a function or subroutine without a name that can be used wherever a delegate is valid. Lambda expressions can be functions or subroutines and can be single-line or multi-line. You can pass values from the current scope to a lambda expression.
What is delegate?
A delegate is a form of object-oriented function pointer that allows a function to be invoked indirectly by way of a reference to the function. Delegates can be used to hook up event handlers and pass a procedure from one procedure to another. Delegates are objects that refer to methods. They are sometimes described as type-safe function pointers because they are similar to function pointers used in other programming languages. But unlike function pointers, Visual Basic delegates are a reference type based on the class System.Delegate. Delegates can reference both shared methods — methods that can be called without a specific instance of a class — and instance methods.
What is interface?
Interfaces define the properties, methods, and events that classes can implement. Interfaces allow you to define features as small groups of closely related properties, methods, and events; this reduces compatibility problems because you can develop enhanced implementations for your interfaces without jeopardizing existing code. You can add new features at any time by developing additional interfaces and implementations. There are several other reasons why you might want to use interfaces instead of class inheritance:
* Interfaces are better suited to situations in which your applications require many possibly unrelated object types to provide certain functionality.
* Interfaces are more flexible than base classes because you can define a single implementation that can implement multiple interfaces.
* Interfaces are better in situations in which you do not have to inherit implementation from a base class.
* Interfaces are useful when you cannot use class inheritance. For example, structures cannot inherit from classes, but they can implement interfaces.
What is assembly?
An assembly is the building block of a .NET application. It is a self describing collection of code, resources, and metadata (data about data, example, name, size, version of a file is metadata about that file). An Assembly is a complied and versioned collection of code and metadata that forms an atomic functional unit. Assemblies take the form of a dynamic link library (.dll) file or executable program file (.exe) but they differ as they contain the information found in a type library and the information about everything else needed to use an application or component. All .NET programs are constructed from these Assemblies. Assemblies are made of two parts: manifest, contains information about what is contained within the assembly and modules, internal files of IL code which are ready to run. When programming, we don't directly deal with assemblies as the CLR and the .NET framework takes care of that behind the scenes. The assembly file is visible in the Solution Explorer window of the project.
Tutorial: SQL Server Integration Services (SSIS)
SSIS Tutorial: SQL Server 2008 Integration Services Tutorial:
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
Subscribe to:
Comments (Atom)
Posts
