User FAQs

If you have a question, please send it to us.


1) Where can I find the Northwind Access database ?
In Access 365 this is no longer installed by default.
It can be installed/downloaded from within Access though
Search for online templates "northwind".


2) What does ODBC stand for ?
ODBC - Open DataBase Connectivity
Introduced in 1992.
This is an open industry standard interface API for accessing databases on different operating systems.
You can also get ODBC drivers for data sources that are not databases (for example Workbooks, CSV files, etc)
All databases will have an ODBC driver.


3) What is an ODBC Driver ?
Also known as an ODBC provider.
A database driver is a piece of software that is installed to allow communication between a client application and a database (or data source) that supports ODBC.
You can have multiple ODBC drivers installed which can target different types of databases.
These drivers can be managed from the ODBC Data Source Administrator dialog box.


4) What does DNS stand for and how it used ?
DNS - Data Source Name
A data source name (DSN) is a data structure that contains the information about a specific database that an ODBC Driver needs in order to connect to it.
There are three different types:
User DNS - Only the user who created the DSN can see this and use it. Stored in the Windows registry under the following key: HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources
System DNS - This DSN must be created on the machine where the program is located. Stored in the Windows registry under the following key: HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources
File DNS - This is a DSN that is stored in a text file with a .DSN extension (not in the Windows registry). The file must contain the information required to connect to the desired data source. The ODBC driver must be installed locally. The File DSNs are stored by default in the following location: Program Files\Common Files\Odbc\Data Sources folder.


5) What does DAO stand for ?
DAO - Data Access Objects
Introduced with Microsoft Access in 1994.
Provided a specific interface for accessing a Microsoft Access / Jet database.
The Jet database engine was superseded by MSDE which in turn was superseded by SQL Server Express.


6) What is the default interface used in Microsoft Access ?
DAO was the default up until Access 2000.
In Access 2000 and 2002 the default interface was replaced with ADO.
In Access 2003 DAO was back as the default, and has been ever since.


7) What does ACE stand for ?
ACE - Access Connectivity Engine
Introduced in Access 2007.
Also known as ACEDAO, DAO or JET.
In Access 2007 the DAO interface was upgraded and renamed to ACE.
This change also corresponded with a new file extension (.ACCDB) which replaced (.MDB).
ACE is Microsoft's recommended approach when communciating with a Microsoft Access database


8) Can you describe the main objects in DAO ?
Workspace - Contains information about the current session and open databases.
Connection - Contans information about the ODBS data source.
Database - Represents an open database.
Recordset - Contains a group of records returned from the data source.


9) What is the difference between the DAO 3.6 and Database Engine Object references ?
Office 2016 uses "Microsoft Office 16.0 Access Database Engine Object"
Office 2013 uses "Microsoft Office 15.0 Access Database Engine Object"
Office 2010 uses "Microsoft Office 14.0 Access Database Engine Object"
Office 2007 uses "Microsoft Office 12.0 Access Database Engine Object"
Office 2003 used "Microsoft Office DAO 3.6 Object Library"


10) How can I get a copy of the Northwind Access database ?
This has to be installed from inside Access.
Find the "Search for online template" and type in northwind.


11) Write the code to connect to the Northwind Access database using DAO and execute a SQL statement ?

Dim dbDAODatabase As DAO.Database 
Dim dbDAORecordset As DAO.Recordset 
Dim sSQLQuery As String 

sSQLQuery = "SELECT * FROM Customers" 
Set dbDAODatabase = DAO.DBEngine.OpenDatabase("C:\Temp\Northwind.accdb") 
Set dbDAORecordset = dbDAODatabase.OpenRecordset(sSQLQuery) 

Set dbDAORecordset = Nothing 
Set dbDAODatabase = Nothing

12) Can you use DAO to connect to a SQL Server database ?
Yes. Although the preferred method is ADO.

Dim sConnectionString As String 
Dim dbDAOWorkspace As DAO.Workspace 
Dim dbDAOConnection As DAO.Connection 
Dim dbDAODatabase As DAO.Database 
Dim dbDAORecordset As DAO.Recordset 

Set dbDAOWorkspace = CreateWorkspace("", "admin", "", dbUseODBC) 
sConnectionString = "ODBC;DATABASE=MyDB;DSN=MYDSN" 
Set dbDAOConnection = dbDAOWorkspace.OpenConnection _("", , ,sConnectionString) 
Set dbDAODatabase = dbDAOConnection.Database 
Set dbDAORecordset = dbDAODatabase.OpenRecordset(sSQLQuery, dbOpenDynamic)

13) What does OLE DB stand for ?
OLE DB - Object Linking and Embedding Database
Introduced in 1996.
This is a Microsoft specific COM based interface for accessing databases and other data sources (text files and spreadsheets).
Not all databases will have an OLEDB driver.
All Microsoft data sources allow access using both OLEDB and ODBC.


14) What is a OLE DB Driver ?
Also known as an OLE DB provider.


15) What does ADO stand for ?
ADO - ActiveX Data Objects
Also known as ADODB
This was designed to be an object orientated interface over the top of OLE DB.
There is this special OLE DB provider called the MSDASQL which allow ADO to use an ODBC driver.


16) Can you describe the main objects in ADO ?
Connection - Contains information about the OLE DB data source.
Command - Contains the instructions either SQL or stored procedures.
Parameter - Contains any additional parameters that are required by the Command object.
Recordset - Contains a group of records returned from the data source.


17) Write the code to connect to the Northwind Access database using ADO and execute a SQL statement ?

Dim dbADORecordset as ADODB.Recordset 
Dim sConnectionString as String 
Dim sSQLQuery as String 

sConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=C:\Temp\Northwind.accdb" 
sSQLQuery = "SELECT * FROM Customers" 

Set dbADORecordSet = New ADODB.Recordset            
dbADORecordSet.Open sSQLQuery, sConnectionString 

Set dbADORecordset = Nothing

18) Write the code to connect to a SQL Server database using ADO and execute a stored procedure that takes one argument and returns a recordset ?

Dim dbADOConnect As ADODB.Connection 
Dim dbADOCommand As ADODB.Command 
Dim dbADORecordset As ADODB.Recordset 
Dim dbADOParameter As ADODB.Parameter 
Dim sConnectionString As String 
Dim vaReturnValues As Variant 

sConnectionString = "Provider=MSOLEDBSQL;Server=myServer; Database=myDataBase;" 
Set dbADOConnect = New ADODB.Connection       
dbADOConnect.Open sConnectionString, sUserID, sPassword, adConnectUnspecified 
dbADOConnect.CursorLocation = ADODB.adUseClient 

Set dbADOCommand = New ADODB.Command      
dbADOCommand.CommandText = "ProcedureName" 
dbADOCommand.CommandType = ADODB.dbCmdStoredProc 
dbADOCommand.ActiveConnection = dbADOConnect 

Set dbADOParameter = New ADODB.Parameter 
dbADOParameter = dbADOCommand.CreateParameter("ReturnedName", _ 
                              datatype, ADODB.adParamOutput, size) 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADOParameter = New ADODB.Parameter 
dbADOParameter = dbADOCommand.CreateParameter("ParameterName1", _ 
                              datatype, ADODB.adParamInput, size, "ParameterValue1") 
dbADOCommand.Parameters.Append dbADOParameter 
Set dbADOParameter = Nothing 

Set dbADORecordSet = New ADODB.Recordset            
Set dbADORecordSet = dbADOCommand.Execute 
vaReturnValues = dbADOCommand("ReturnedName") 

Set dbADORecordset = Nothing 
Set dbADOCommand = Nothing 
Set dbADOConnect = Nothing

19) What is the quickest way to display a recordset on a worksheet ?
You can use the CopyFromRecordset property assuming the recordset does not contain any OLE object fields or array data such as hierarchical recordsets.

Range("A1").CopyFromRecordset dbRecordSet

This works with both ADO and DAO Recordsets.


20) What is the difference between a DBMS and a RDBMS ?
The biggest difference is that DBMS applications store data as files while RDBMS stores data in tables.
An RDBMS is a DBMS which is based on the relational model.


21) What are the main differences between Access and SQL Server ?
Both are RDBMS applications.
Access has a maximum size of 2GB.


22) What is SQL Server Express ?
SQL Server 2005 Express replaced MSDE and is a FREE scaled down version of SQL Server 2005.
It comes with SQL Server Management Studio Express.
The database has a maximum size of 4GB.
The sample databases (AdventureWorks and Northwind) are not installed by default but can be downloaded.


23) What is Normalisation ?
This is the term given to efficiently organising the tables in a database by removing duplicate data and ensuring the correct data dependencies.
By normalising your database you make it more efficient and help to reduce its size.
The database community has developed a series of guidelines for ensuring that databases are normalized.
These are referred to as normal forms and are numbered from one 1NF to six 6NF.


24) What is Denormalisation ?
This is the process of adding redundant data/columns in an attempt to optimise the performance.


25) What is Referential Integrity ?
This means that the foreign key in any referencing table must always refer to a valid row in the referenced table.
Referential integrity constraints can be applied to ensure that a relationship between two tables remains synchronized during updates and deletes.
When applied referential integrity can protect against accidental additions and deletions.
For example before you delete a Customer you must delete all the Orders first.


26) What is an Orphaned Record ?
This is a record in one table that does not have a corresponding entry in a related table.


27) What is an Entity Relationship Diagram ?
An entity relationship (ER) diagram is a graphical representation of entities and their relationships to each other (one-to-one, one-to-many, many-to-many).
It is commonly used to describe the organisation of data within a database.
These diagrams use square, circle and rhombus.


28) What is Replication ?
Replication is a way of keeping data synchronised in multiple databases and is normally monitored by a qualified DBA (DataBase Administrator).


29) What is a Transaction ?
A transaction is a group of queries (and/or statements) that must all be either completed or aborted.
Using transactions allows you to ensure data integrity in a database.
If any of the queries fail for whatever reason the transaction can be rolled back to return the database to its original state.


30) What is a Table ?
A table is a set of related records organised in horizontal rows with a specific number of columns. Tables often have meta-data associated with them, for example constraints.


31) What is a Domain Table ?
A domain table is a table whose sole purpose is to provide a list of acceptable values, like a lookup table.


32) What is a View ?
A view is a pre-compiled query which pulls data from one or more tables.
A view is a virtual table composed of the result set of a query.
A view does not store any actual data.
A view is not actually part of the database schema and is automatically updated when the corresponding table(s) are updated. Views can be read only or updatable.


33) What is the difference between a View and a Table ?
*) Views allow you to restrict access to particular subsets of data.
*) You can add/remove columns easily in a view without modifying the underlying schema.
*) Views can hide joins (ie complexity) and present the user with a more denormalised subset of the data.
*) Retrieving data from a view is faster than from a table.
*) Views cannot be used to override constraints or referential integrity defined on the tables.
*) Never use a view to access or call another view.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext