Try It Out Controlling an Excel Spreadsheet from Access

In this example, you will create a new Excel workbook and populate some of the cells with data. 1. Add the following code to your module. Dim objExcel As New Excel.Application Dim objWorksheet As New Excel.Worksheet 'add a new workbook to the spreadsheet objExcel.Workbooks.Add 'point the worksheet variable at the active sheet Set objWorksheet objExcel.ActiveSheet 'show Excel to the user objExcel.Visible True 2. Execute the ControlExcel procedure from the Immediate Window by typing ControlExcel...

The Project Class

An object diagram is shown in Figure 12.24 for the Project class. The Contact class will be illustrated later in this chapter. -ProjectId -ProjectTitle -ProjectDescription -Priority The properties are represented in the top portion of the diagram, and the methods are shown in the bottom section. These correspond to the data elements on the form for the most part, except that the tabs with multiple records are not listed here. The methods represent various actions that you must take on the...

Using Data from Web Services

So far in this chapter, you have learned how to interact with the most common external data sources in your application. A common topic of discussion in the high-tech sector these days is Web services. Web services are reusable components that are based on standard Internet protocols. They enable systems on different platforms to talk to each other. In the simplest terms, I like to describe a Web service as some procedure or function that someone has made available over Internet protocols so...

The Basics of Writing and Testing VBA Code

Chapter 1 introduced the Access VBA programming environment and some general software development concepts. At this point, you are ready to learn the basics of writing and testing code using VBA. This chapter will cover Creating and calling procedures Using variables to store values Controlling the flow of programs Debugging and handling errors This chapter will serve as a building block for the more advanced VBA concepts covered in later chapters. The techniques covered in this chapter and in...

Building the Database and Database Objects

In this section, you will build the database in an Access project, which stores the data tables in SQL Server, and the user interface in the Access project itself. You should have a version of Microsoft SQL Server installed in order to create this application. Please refer to Chapter 9 for more information on obtaining and setting up SQL Server. Try It Out Building the CustomerServiceSQL Database Now, let's build the Access Project and SQL Server database that will store the data tables. You...

Can You Use Vba To Connect In Place Of

To add a reference to a Web service, you first selected the Tools C gt Web Services References option. Because you're planning to use a service located at http terraserver-usa.com TerraService2.asmx, you selected the Web Service URL option and specified the Terra Server path in the URL field of Figure 7.7. You also could have searched for available Web services using certain keywords or specified the location of another Web service. You then selected the Terra Server Web service and clicked the...

Using ADOX to Manipulate Data Objects

The ADOX library has an object model that allows you to create database objects such as tables, indexes, and keys, as well as to control security, establish referential integrity in a database, and perform cascade updates and deletions. The Catalog object is at the top of the ADOX object model, with Tables, Groups, Users, Procedures, and Views collections. Please consult the online help for the complete ADOX object model. Just as with the ADODB library, if you want to make use of the ADOX...

Vba Code Access2003 Samples

Microsoft Access 2003 is a powerful database application that allows you to build standalone and client-server database applications. Access applications are used in many enterprises for storing inventory and maintaining customer records, sales data, or nearly any other type of data that needs tracking. Access has powerful wizards and tools that make it relatively easy for users to build a database application. However, such databases must often be expanded to include additional features not...

Working with Reports from VBA

Reports can be manipulated in numerous ways from VBA code. For example, you can change the data source on which the report is based from within VBA code. You can also create reports from scratch or modify existing reports from within VBA code. Let's look at a few examples to further illustrate these concepts. Many of the examples in this chapter that deal with manipulating existing reports use the Northwind database. If you want to create those particular examples yourself, open the Northwind...

Form Navigation and Flow

Switchboard And Forms

You can control the order and manner in which forms can be opened within your Access applications in various ways. For example, you can design switchboard forms that allow the user to select which form to open. Another option is to open a main form and allow a user to open separate forms from the main form. The style of navigation you choose depends on the type of system and the manner in which users will interact with the various forms. During the design phase, you should consider the flow of...

Adding Controls to the Report

The CreateReportControl method allows you to add new controls to a report. The CreateReport Control method has the following basic syntax. CreateReportControl ReportName, ControlType, Section, Parent, ColumnName, Left, Top, Width, Height Now, let's modify the prior code to add four fields to the report CompanyName, ContactName, Title, and Phone. Each field needs a corresponding text box and label. The following modified procedure is one way to accomplish this. Dim rptCustomers As Access.Report...

Using Property Let Property Get and Property

Property Let, Property Get, and Property Set procedures enable you to create properties in a more flexible way than by using public variables. Property Let procedures determine what happens when you assign a value to the property. Here is a simple example showing the general syntax for a Property Let procedure. Public Property Let TestProperty ByVal strMessage As String 'code for assigning the property goes here strTestPropertyVal strMessage End Property In many cases, you assign the value of...

Adding Editing and Deleting Records in a Recordset

Source or the new record will be lost. This is called working with a disconnected recordset and is explained in detail in an upcoming example. Updating an Existing Record with the Update Method The Update method of the Recordset object updates the current record. If the Update method is used in conjunction with the AddNew method, the information from the empty record is moved to the end of the recordset. If the recordset is connected to a data source, the changes are saved in the underlying...

Spreadsheets

The TransferSpreadsheet method is very similar to the TransferDatabase method in that it enables you to import, link, and export, only in this case it deals with spreadsheets. The syntax is shown in the following code. SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA Various parameters are used to specify how the method should execute. The following table explains the use of each parameter. Type of transfer to be performed. Valid choices are acImport default , acLink, and...

Building the User Interface

Access Rename Control Page

Now that the tables have been created, it's time to move on to creating the empty forms for the user interface. You can then write the code in class modules and standard modules, and finish off by adding code to each form to call the class modules and standard modules. The example in this chapter uses ADO to connect to the ProjectTrackerDb database, but you can alternatively or additionally add an explicit link to the separate database so you can see the tables from within your user interface...