Database Components  «Prev 

Breaking Data into Fields

You may have noticed that I have broken down name and address pieces into small parts. For instance, I do not have one field for name information; I have two: one for first name and one for last. And I do not have one address field; I have three:
  1. one for the street address,
  2. one for the city, and
  3. one for the state.
Breaking up the data this way gives me more flexibility when I need to analyze or use the data. For instance, if I had only one field for the full name, I wouldn not be able to sort by last name, or address a letter to Ms. Young, using just the last name. If I had only one field for address I would not be able to sort the data by state or zip code. When data is broken into small (but meaningful) pieces it gives the database more flexibility for sorting and analysis.

My work involves financial and statistical number crunching. The business data is stored in Access, but I use many of the functions available in Excel to get my work done. Is there a way to use the Excel functions directly from Access, rather than copying and pasting my data from Access into Excel? Thanks to the availability of an object model paradigm for coding, it is relatively easy to hook into Excel and make use of Excel functions from within Access.

Passing Parameters to SQL Server

Problem
Most of my data is stored in a SQL Server database. The DBA only allows me to access the database by using a stored procedure, but the stored procedures require parameters. How can I pass parameters to SQL Server to get the data I want?

Solution:
Let us assume that the following stored procedure exists on the SQL Server database. The stored procedure takes two parameters,
  1. @StartDate and
  2. @StopDate,
both of which have a type of datetime: 

Select *
From DowJones
Where [Date] Between @StartDate And @StopDate

To call this procedure from an Access VBA script, you can use an ADO Command object to populate a Recordset object, like this: 
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "provider=sqloledb;Data Source=Athena;" & _
"Database=Access;Uid=sa;pwd="
cmd.CommandText = "GetData"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@StartDate").Value = CDate("1/1/2005")
cmd.Parameters("@StopDate").Value = CDate("1/1/2006")
Set rs = cmd.Execute

After creating a new instance of the Command object, you need to specify how to connect to your database. This value is known as a connection string, and it can be assigned to the ActiveConnection property of the Command object. Next, specify the name of the stored procedure in CommandText, and indicate that this value refers to a stored procedure, not a table or a SQL statement.

The Parameters

The Refresh method instructs ADO to connect to the database to get the definitions of all the parameters. The results are saved in the Parameters collection. Finally, you can specify the value for each parameter, and use the Execute method to create a new Recordset object populated with the results of the stored procedure.