How Do I Hide A Form But Leave It Running?

There are times when it is preferable to hide a form, in other words to have it open but invisible.

1. Global Variables:
   There are circumstances under which global variables lose their values in Access. These circumstances are not common, but they happen often enough to be of concern. One way around this problem is to create a hidden form with unbound controls, each of which would hold the value of one of your global variables. So instead of using a global variable, you set and reference the value of a control on the form.
2. Timer events:
   If you want run a process at a particular time or multiple times throughout the day, you can create a timer event in a form. This form must be open in order for the timer event to fire. So to keep this form active, but out of the way, you can hide it.
3. Performance:
   Some forms take a long time to load. This may be because it has a large, bound dataset, or it may be a complex form with several subforms. Whatever the reason, once the form is loaded, it makes sense to not close it again.

There are several ways to hide a form. The one you use depends on what you're trying to accomplish.

If you're using the form for global variables or a timer event (or both), the simplest thing is to open it in Hidden mode. To do that, create a macro and name it AutoExec. (see How do I run a macro or code when the database first starts? ). The AutoExec macro is a special macro that will automatically execute when the database opens. In this macro, you'll want to choose the OpenForm action, name the form, and choose Hidden in the Window Mode property.

Like this:

Access 2010-2013

 

Access 2000-2007

If some of your forms take a long time to load, however, this is not a good method. It will appear as if Access has frozen while the form or forms load at Start Up. In such cases, it is preferable to create a Splash Screen. A Splash Screen is simply a form which tells the user that the database is opening and to wait. This gives the user feedback and reassures them that everything is functioning normally.

I'll discuss splash screens in a later post.

But once the form is open, it makes sense to hide it rather than close it. To do that, you can use a button. I usually use the button wizard because it creates the button's Onclick event code with error trapping automatically. I use the Form Close wizard, which has a single line of code:

DoCmd.Close

I will replace that with the following:

Me.Visible = False

The next time you "open" the form, it will simply make the already open form visible.   So instead of opening a Form, you can instantly by make the form visible and set the focus on it like this:

Forms("MyFormName").Visible = True

Forms("MyFormName").SetFocus

How do I run a macro or code when the database starts?

Access has a special macro called Autoexec, which automatically executes when the database opens. You can use this macro to do practically anything: open one or more forms in hidden mode, relink your tables, pop up a tickler list, or whatever startup functions your application requires.

At its simplest, simply create a new macro, choose the Actions and associated arguments required, and save it under the name Autoexec. The next time you open your application, this macro will execute.

However, most experienced developers agree that macros are not the optimal solution. Most prefer VBA code to macros. Fortunately, it is simple to convert a macro to VBA code (see: How Do I Convert A Macro to VBA Code?) .

So a much more elegant solution would be to create an Autoexec macro that does what you want it to do. After you test it, convert it to a VBA function.

The conversion will create a new function called Autoexec() in a general module. Next delete all the actions from your existing Autoexec macro and replace it with the RunCode action, with Autoexec() as the function name. Like this:

Access 2010 and 2013

 

In Access 2000 - 2007

How Do I Convert A Macro to VBA Code?

Although Microsoft has made great strides in improving the capabilities of macros in Access, most experienced developers agree that Visual Basic for Applications (VBA) code is still far superior to using macros in Access client apps. (Web apps, of course, cannot use VBA, so the following is only pertinent to applications that run in the Access Client.)

So suppose you have a completed macro that you want to convert to VBA? Fortunately, Access makes it easy.

Access 2010 and 2013

Open the Macro in Design View

 

Click the File Tab then

1. Click Save As
2. Save Object As
3. Save As

You will get a pop-up Dialog box.  Give the macro a meaningful name in the top box and in the AS box, choose Module. Click OK.  

Click OK.  A second box will appear asking you if you want error trapping and comments.

Leave both checked and click Convert.

The macro will be converted and saved in a Module.

'------------------------------------------------------------
' MyMacro
'------------------------------------------------------------
Function MyMacro()
On Error GoTo MyMacro_Err

    DoCmd.TransferSpreadsheet acExport, 8, "table7", "table7.xls", True, ""

MyMacro_Exit:
    Exit Function

MyMacro_Err:
    MyMacroError$
    Resume MyMacro_Exit

End Function

'------------------------------------------------------------

In Access 2007

Open the macro in Design View, Click the Office Button (Pizza), Choose Save As, and you will immediately be given the Save As dialog.

A second box will appear asking you if you want error trapping and comments. Leave both checked and click Convert.

In Access 2003 or previous

In the Database Window, right-click on the macro you want to convert and choose Save As. Everything else works the same.

How Do I Configure My Access Database Start Up?

This article has moved: How Do I Configure My Access Database Start Up?

New Sample: Document Query SQL, Form and Report RecordSources

by Crystal Long

Document Query SQL, Form and Report RecordSources, and create query to show main objects

Here is code to DOCUMENT the SQL stored for each QUERY. You can also document the source for FORMS and REPORTS. You can also create a query that lists the main object names and types in your database.

click in first procedure, Run_Word_CreateDocumention_SQL, and press F5 to Run!

A Word document showing the SQL for all your queries will be created.
To use this module to document RecordSource for forms or reports, run one of the following procedures:

Run_Word_CreateDocumention_Forms
Run_Word_CreateDocumention_Reports

To create a query from the MSysObjects table with a list of all the main object names and types in your database, run Run_Create_qObjex_byCrystal_Query
BAS module

to import a module into your database, UNZIP it to pull out the BAS file
then open the database you want to document, press Alt-F11 to go to a Visual Basic window. From the menu, choose: File, Import
Navigate to:
bas_crystal_Document_QrySQL_FrmRptRecordSource_2Wrd_Create_qObjx.bas

NEEDS reference to
Microsoft DAO Object Library or Microsoft Office ##.0 Access Database Engine Object Library
(from the menu: Tools, References...)

Debug, Compile and then Save

then run each of the four procedures at the top that are named Runblahblah

You can find the sample here: http://www.rogersaccesslibrary.com/forum/Document-query-sql-Form-and-report-recordsources_topic606.html

For more samples by Crystal look here: http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html

Microsoft MVP
remote programming and training
Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access
http://www.YouTube.com/LearnAccessByCrystal

Recreate Access Tables in SQL Server

by Roger Carlson

Introduction

Microsoft Access is a terrific prototyping tool for SQL Server. But upsizing your database structure to a SQL Server database can be a problem. It would be nice if you could simply export the table structure from Access to SQL Server like you can between Access databases, but you can't. Fortunately, it's fairly easy to read the table structure of an Access table and from that build a SQL Create Table script to recreate the table in SQL Server.

Problems with the Access Upsize Wizard

Of course, Access comes with the Upsizing Wizard, which attempts to recreate your Access table in SQL Server. But the Upsizing Wizard has its problems.

First of all, the Upsize Wizard doesn't give you much flexibility. It chooses the SQL Server data types for your table. You don't have a choice. For instance, the wizard will create your text fields as nvarchar, but suppose you want them created as varchar? What if you want your Yes/No fields created as tinyint rather than bit?

And then there are SQL Server specific properties, like Padding and Clustered Indexes, over which you have no control. The Upsizing Wizard will apply a default padding value, which you cannot change. It will also always create your primary key as a clustered index, whether you want it or not.

Another problem with the Upsize Wizard is you must be connected to a SQL Server database for it to work. This is fine if you have a connection, but suppose you don't have rights to create tables? What if the SQL Server DBA asks you for a script to create them? What if you need to send the table definitions to a remote location to be created there?

Lastly, the Upsize Wizard will only work with SQL Server. The following process could be easily modified to create your tables in Oracle or another SQL-based database.

SQL Server's Enterprise Manager has a function that will save the structure of a table as a Create Table statement in a text file. Your DBA can then to run this script in the Query Analyzer to create the tables for you. It would be really useful if Access had a similar facility, but it does not. Fortunately, you can write one of your own.

How it works

The following code module accepts the name of a table in your Access database. It will then read the field names and data types and build a SQL Create Table statement, converting the Access data types to SQL Server equivalent data types. It then writes this SQL statement to a text file named after the table and having a .SQL extension.

Next, it reads all the Indexes in the table, determines the fields in the index and whether it is Unique or a Primary Key index, and finally builds Create Index statements to recreate these indexes.

Public Sub RecreateTableInSQLServer(TableName As String)
On Error GoTo Err_RecreateTableInSQLServer

The first thing is to create some object and scalar variables.

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim strSQL As String
Dim indexfields As String
Dim indexunique As String
Dim path As String

Next, you need to initialize the object variables, opening a connection to the current database and to the particular table you want to recreate.

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(TableName)

You also want to find the path to the folder where the database currently resides, so the program will know where to store the text file.

path = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

If you want some other directory, you could hard code a folder path here.

path = "C:\My Documents\Access"

In Access, any field can be defined as part of a primary key as long as there aren't any null values in the field. In SQL Server, however, you must define the field as Not Null ahead of time. Therefore, you have to know which fields are in the primary key before you start defining the fields. To do that, you have to loop through the Indexes collection of the TableDef object. When you find the primary key index (as determined by the Primary property of the Index object), assign it to a string variable (indexfields).

For Each idx In tdf.Indexes
    If idx.Primary = True Then
    indexfields = idx.Fields
    End If
Next

The fields that make up the index are stored in the Fields property of the Index object, but they are stored in an odd format. For a multiple field index, they're stored in a string that looks something like this: +TextField;+LongIntField. Fortunately, you can read this string for the fieldnames with the InStr() function. You'll do this later in the routine.

Now that all the variables are created and initialized, and you know what fields are in the primary key, you can begin to build the SQL statement. Every Create Table statement always starts with the word CREATE TABLE and the name of the table. So you'll write that to a string variable (strSQL).

strSQL = "CREATE TABLE [" & TableName & "] ("

The brackets surrounding both the table name above and the field names below are necessary for table and field names that have spaces or special characters in them.

Next, the code needs to read through all the fields in the table, read the data types, and in some cases, determine some field attributes. It will add the field to the field list of the Create Table statement and depending on the data type, add the corresponding SQL Server data type.

For Each fld In tdf.Fields
  Select Case fld.Type
    Case 4 'Long Integer or Autonumber field
      If fld.Attributes = 17 Then
        strSQL = strSQL & "[" & fld.Name & "] INT IDENTITY"
      Else
        strSQL = strSQL & "[" & fld.Name & "] INT"
      End If
    Case 10 'Text field
       strSQL = strSQL & "[" & fld.Name & "] VARCHAR(" & fld.Size & ")"
    Case 12 'Memo field
       strSQL = strSQL & "[" & fld.Name & "] NTEXT"
    Case 2 'Byte field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 3 'Integer field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 6 'Single-precision field
       strSQL = strSQL & "[" & fld.Name & "] REAL"
    Case 7 'Double-precision field
       strSQL = strSQL & "[" & fld.Name & "] FLOAT"
    Case 15 'ReplicationID field
       strSQL = strSQL & "[" & fld.Name & "] UNIQUEIDENTIFIER"
    Case 8 'Date/Time field
       strSQL = strSQL & "[" & fld.Name & "] DATETIME"
    Case 5 'Currency field
       strSQL = strSQL & "[" & fld.Name & "] MONEY"
    Case 1 'Yes/No field
       strSQL = strSQL & "[" & fld.Name & "] SMALLINT"
    Case 11 'OleObject field
       strSQL = strSQL & "[" & fld.Name & "] IMAGE"
  End Select

This is where you'll use the index string of the primary key. If the field is part of the primary key, add NOT NULL to the field data type, otherwise just append a comma.

  If InStr(indexfields, fld.Name) > 0 Then
    strSQL = strSQL & " NOT NULL, "
  Else
    strSQL = strSQL & ", "
  End If

And go to the next field.

Next fld

There will be an extra comma at the end the field list that needs to be removed. You also have to close off the Create Table statement with a closing paren and semicolon. (Technically, the semi-colon is unnecessary).

strSQL = Left(strSQL, Len(strSQL) - 2) & ");"

Now, you need to create a text file and write the string variable to that file. The easiest way to do this is through low-level IO. The following Open statement will create a text file with the name of the table preceded by the word "Create" and followed with a .sql extension in the folder where the database resides. So for table "Customers", the text file would be "CreateCustomers.sql". Then it will use the Print command to write the SQL statement to the file followed by a blank line.

Open path & "Create" & TableName & ".sql" For Output As #1
Print #1, strSQL
Print #1, ""

Next you need to recreate the indexes themselves, so you need to read the indexes again.

For Each idx In tdf.Indexes

As I said earlier, the index property returns a string like this: +TextField;+LongIntField. To make it useful for a SQL statement, you need to remove the pluses and convert the semicolons to commas. This you can do with the Replace function.

  indexfields = idx.Fields
  indexfields = Replace(indexfields, "+", "")
  indexfields = Replace(indexfields, ";", "], [")

In the first Replace function, you will replace all the pluses with the empty string, effectively deleting them. In the second Replace function, you are replacing each semicolon with "], [". The brackets are necessary for field names that have spaces in them. The first and last fields in the field list will not have brackets on the outer edge, but we'll add them when we put the whole SQL statement together.

You also need to determine whether the index is unique. If it is, the Unique property of the Index object will be true.

  If idx.Unique = True Then
    indexunique = " UNIQUE "
  Else
    indexunique = ""
  End If

You are almost ready to put the SQL Statement together. There is just one more property you need to consider whether the index is a primary key. In Access, you can create the Primary Key index in the Create Index statement. However, in SQL Server, you have to create it in the Create Table or Alter Table statement. I've chosen to do it in the Alter Table statement.

So if the index is a primary key index, use the Alter Table statement to create the primary key. Otherwise, build a Create Index statement.

  If idx.Primary = True Then
    strSQL = "ALTER TABLE [" & TableName & _
    "] ADD CONSTRAINT [PK_" & TableName & "_" & idx.Name & _
    "] PRIMARY KEY ([" & indexfields & "]);"
  Else
    strSQL = "CREATE " & indexunique & _
    " INDEX [IX_" & TableName & "_" & idx.Name & _
    "] On [" & TableName & "] ([" & indexfields & "]);"
  End If

Since your text file is still open, you can simply write the SQL string to it. Then loop for the next index.

  Print #1, strSQL
  Print #1, ""
Next

When you're done reading the indexes, close the text file and destroy the object variables.

Close #1
Set idx = Nothing
Set tdf = Nothing
Set dbs = Nothing

Add error trapping, and you're done.

Exit_RecreateTableInSQLServer:
   Exit Sub
Err_RecreateTableInSQLServer:
   MsgBox Err.Description
   Resume Exit_RecreateTableInSQLServer
End Sub

Implementing the Routine

Implementing this routine is relatively easy. Create a form with a listbox and a button. Fill the listbox (we'll call it lstTableList) with all the tables in your database by putting the following in the RowSource property of your listbox:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*"
  And (MSysObjects.Name) Not Like "~*")
  AND ((MSysObjects.Type)=1))
  ORDER BY MSysObjects.Name;

Then call the routine from the button like this:

Call RecreateTableInSQLServer(Me.lstTableList)

Figure 1: Form used to implement the RecreateTableInSQLServer routine

The resulting text file might look like this:

CREATE TABLE [tblAllDataTypes] ([AutoNumberField] INT IDENTITY NOT NULL,
[TextField] VARCHAR(50) NOT NULL, [MemoField] NTEXT, [LongIntField] INT, [ByteField] SMALLINT, [IntegerField] SMALLINT, [SingleField] REAL, [DoubleField] FLOAT, [ReplicationField] UNIQUEIDENTIFIER, [DateTimeField] DATETIME, [CurrencyField] MONEY, [YesNoField] SMALLINT, [OLEObjectField] IMAGE, [HyperLinkField] NTEXT);

CREATE INDEX [IX_tblAllDataTypes_Index1] On [tblAllDataTypes] ([TextField], [LongIntField]);

CREATE UNIQUE INDEX [IX_tblAllDataTypes_LongIntField] On [tblAllDataTypes] ([LongIntField]);

ALTER TABLE [tblAllDataTypes] ADD CONSTRAINT [PK_tblAllDataTypes_PrimaryKey] PRIMARY KEY ([AutoNumberField], [TextField]);

Considerations for Access Versions

This routine requires a reference set to the DAO (Data Access Objects) object model. This is not a problem in Access 97, 2003 or 2007, but in Access 2000 and 2002 (XP), you'll have to set this reference.

To do that, go to the VB editor (Alt+F11), and from the menu bar select Tools > References. In the References dialog box, scroll down the list until you find Microsoft DAO 3.6 Object Library. Check the box next to it and click OK to close the box.

Access 97 has an additional problem. The Replace function used above does not exist in Access 97, so you'll need to create one. Fortunately, it's relatively simple.

Function ReplaceCharacter(Target As String, SearchChar, ReplaceChar) As String

Dim i As Integer
Dim tempstring As String
  For i = 1 To Len(Target)
    If Mid(Target, i, 1) = SearchChar Then
      tempstring = tempstring & ReplaceChar
    Else
      tempstring = tempstring & Mid(Target, i, 1)
    End If
  Next i
ReplaceCharacter = tempstring
End Function

To use this function, simply replace these lines:

indexfields = Replace(indexfields, "+", "")
indexfields = Replace(indexfields, ";", "], [")

with these:

indexfields = ReplaceCharacter (indexfields, "+", "")
indexfields = ReplaceCharacter (indexfields, ";", "], [")

Conclusion

There are a variety of circumstances where you might want to recreate an Access table in other database environments. This routine demonstrates a fairly simple method to save the table structure complete with indexes into a SQL script file that can be run in the target environment to recreate the table.

Sample Database

You can find a sample which implements this here: http://www.rogersaccesslibrary.com/forum/topic222.html

New Sample: GetDistance function for Latitudes and Longitudes

By Crystal Long

The shortest distance between 2 points is a straight line.  Here is a function to do that.  It does not take curvature or routes into account.
GetDistance Function for VBA

Function GetDistance(pLat1 As Double, pLng1 As Double _
   , pLat2 As Double, pLng2 As Double _
   , Optional pWhich As Integer = 1 _
   ) As Double
'12-13-08, 12-22
   ' calculates distance between 2 points of Latitude and Longitude
   ' in Statute Miles, Kilometers, or Nautical Miles
   ' crystal strive4peac2012 at yahoo.com
   ' http://www.rogersaccesslibrary.com/forum/topic604_post622.html#622
   
   'PARAMETERS
   ' pLat1 is Latitude of the first point in decimal degrees
   ' pLng1 is Longitude of the first point in decimal degrees
   ' pLat2 is Latitude of the second point in decimal degrees
   ' pLng2 is Longitude of the second point in decimal degrees
   
   On Error Resume Next
   Dim EarthRadius As Double
   
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7
   Case 3:
      EarthRadius = 3437.74677
   Case Else
      EarthRadius = 3963
   End Select
   
   ' Radius of Earth:
   ' 1  3963.0 (statute miles)
   ' 2  6378.7 (kilometers)
   ' 3  3437.74677 (nautical miles)
   ' to convert degrees to radians, divide by 180/pi, which is 57.2958
   GetDistance = 0
   
   Dim X As Double
    
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958))
      
   GetDistance = EarthRadius * Atn(Sqr(1 - X ^ 2) / X)
End Function

You can find this sample here: http://rogersaccesslibrary.com/forum/getdistance-function-for-latitudes-and-longitudes_topic604.html