Access Macro: Export Table/Query To Excel

17. February 2008 Bob Office Comments (0)

Using this Access VBA Macro

I wrote this Access VBA Macro for a friend to export an Access table or query to a spreadsheet; it might come in handy. ;-]

Access VBA Macro Example Code

Sub ExportTableOrQueryToExcel()

    Const strTitle = "This is my worksheet title"
    Const strTableOrQuery = "Query1"
    
    ' define the path to the output file
    Dim strPath As String
    strPath = "C:\TestFile " & _
        Year(Now) & Right("0" & _
        Month(Now), 2) & Right("0" & _
        Day(Now), 2) & ".xls"
    
    ' create and open an Excel workbook
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.WorkBooks.Add
    objXL.Worksheets(1).Name = strTitle
    objXL.Visible = False
   
    ' delete the extra worksheets
    Dim intX As Integer
    If objXL.Worksheets.Count > 1 Then
        For intX = 2 To objXL.Worksheets.Count
            objXL.Worksheets(2).Delete
        Next
    End If
    
    ' open the database
    Dim objDB As DAO.Database
    Dim objRS As DAO.Recordset
    Dim objField As DAO.Field
    Set objDB = CurrentDb
    
    ' open the query/table
    Dim strSQL As String
    strSQL = "SELECT * FROM [" & strTableOrQuery & "]"
    Set objRS = objDB.OpenRecordset(strSQL)
    
    Dim lngRow As Long
    Dim lngCol As Long
    
    If Not objRS.EOF Then
    
        lngRow = 1: lngCol = 1
        
        For Each objField In objRS.Fields
            objXL.Worksheets(1).Cells(lngRow, lngCol).Value = objField.Name
            lngCol = lngCol + 1
        Next
        
        lngRow = lngRow + 1
    
        ' loop through the table records
        Do While Not objRS.EOF
            lngCol = 1
            For Each objField In objRS.Fields
                objXL.Worksheets(1).Cells(lngRow, lngCol).Value = objField.Value
                lngCol = lngCol + 1
            Next
            lngRow = lngRow + 1
            objRS.MoveNext
        Loop
    
    End If
    
    objXL.DisplayAlerts = False
    objXL.ActiveWorkbook.SaveAs strPath, 46
    objXL.ActiveWorkbook.Close

End Sub

Access: Database Schema Report

16. February 2008 Bob Office Comments (0)

Summary

This article shows you a Windows Script Host (WSH) application that will create a report based on the schema of an Access Database.

More Information

  1. Open Windows Notepad and copy/paste the WSH code listed below into it.
  2. Modify the strDatabaseFile and strOutputFile constants for your database and desired report name.
  3. Save the file as "DatabaseScema.vbs" to your desktop.
  4. Double-click the WSH file to run it.
    Note: A message box that says "Finished!" will appear when the script has finished executing.
Windows Script Host (WSH) Code
Option Explicit

' --------------------------------------------------
' Define variables and constants
' --------------------------------------------------

Const strDatabaseFile = "MusicStuff.mdb"
Const strOutputFile = "MusicStuff.htm"
Const adSchemaTables = 20

Dim strSQL
Dim strCN
Dim objCN
Dim objRS1
Dim objRS2
Dim objField
Dim intCount
Dim objFSO
Dim objFile

' --------------------------------------------------
' Open the output file
' --------------------------------------------------

Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strOutputFile)
objFile.WriteLine "<html><head>" & _
"<style>BODY { font-family:arial,helvetica; }</style>" & _
"</head><body>"
objFile.WriteLine "<h2>Schema Report for &quot;" & _
strDatabaseFile & "&quot;</h2>"

' --------------------------------------------------
' Setup the string array of field type descriptions
' --------------------------------------------------

Dim strColumnTypes(205)

' initialize array

For intCount = 0 To UBound(strColumnTypes)
strColumnTypes(intCount) = "n/a"
Next

' add definitions

strColumnTypes(2) = "Integer"
strColumnTypes(3) = "Long Integer"
strColumnTypes(4) = "Single"
strColumnTypes(5) = "Double"
strColumnTypes(6) = "Currency"
strColumnTypes(11) = "Yes/No"
strColumnTypes(17) = "Byte"
strColumnTypes(72) = "Replication ID"
strColumnTypes(131) = "Decimal"
strColumnTypes(135) = "Date/Time"
strColumnTypes(202) = "Text"
strColumnTypes(203) = "Memo/Hyperlink"
strColumnTypes(205) = "OLE Object"

' --------------------------------------------------
' Open database and schema
' --------------------------------------------------

strCN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & strDatabaseFile
Set objCN = WScript.CreateObject("ADODB.Connection")
objCN.Open strCN
Set objRS1 = objCN.OpenSchema(adSchemaTables)

' --------------------------------------------------
' Loop through database schema
' --------------------------------------------------

Do While Not objRS1.EOF
  If Left(objRS1("TABLE_NAME"),4) <> "MSys" Then
    objFile.WriteLine "<p><big>" & objRS1("TABLE_NAME") & "</big></p>"
    objFile.WriteLine "<blockquote><table border=1>" & _
      "<tr><th>Field Name</th><th>Data Type</th></tr>"
    strSQL = "SELECT * FROM [" & objRS1("TABLE_NAME") & "]"
    Set objRS2 = objCN.Execute(strSQL)
    For Each objField in objRS2.Fields
      objFile.WriteLine "<tr><td>" & objField.Name _
      & "</td><td>" & strColumnTypes(objField.Type) & "</td></tr>"
    Next
    objFile.WriteLine "</table></blockquote>"
  End If
  objRS1.MoveNext
Loop

' --------------------------------------------------
' Close the output file
' --------------------------------------------------

objFile.WriteLine "</body></html>"

MsgBox "Finished!"

Determining Access Column Types for FrontPage Database Results Fields

14. December 2006 Bob FrontPage Comments (0)

Summary

Sometimes you want to know where the database column types values are obtained when looking at the database results auto-generated code, but there is no easy way of determining that information by looking at the database results code. This is because the database column types are defined as field types in the database definition. This blog lists those definitions.

More Information

There are two different places where the column/field types will be listed:

  • In the ASP code = fp_sColTypes
  • In the WEBBOT code = s-columntypes

Below is a list of the column/field types in an Access database:

Data Type Value
AutoNumber - Long Integer  3
AutoNumber - Replication ID  72
Text  202
Memo  203
Number - Byte  17
Number - Integer  2
Number - Long Integer  3
Number - Single  4
Number - Double  5
Number - Replication ID  72
Number - Decimal  131
Date/Time  135
Currency - General Number  6
Currency - Currency  6
Currency - Euro  6
Currency - Fixed  6
Currency - Standard  6
Currency - Percent  6
Currency - Scientific  6
Yes/No - True/False  11
Yes/No - Yes/No  11
Yes/No - On/Off  11
OLE Object  205
Hyperlink  203

References

For more information on database field types, see the following Microsoft KB article:

This article contains the following section of code that lists several database column/field types:

* DEFINEs for field types - provided for reference only.

#DEFINE ADEMPTY 0
#DEFINE ADTINYINT 16
#DEFINE ADSMALLINT 2
#DEFINE ADINTEGER 3
#DEFINE ADBIGINT 20
#DEFINE ADUNSIGNEDTINYINT 17
#DEFINE ADUNSIGNEDSMALLINT 18
#DEFINE ADUNSIGNEDINT 19
#DEFINE ADUNSIGNEDBIGINT 21
#DEFINE ADSINGLE 4
#DEFINE ADDOUBLE 5
#DEFINE ADCURRENCY 6
#DEFINE ADDECIMAL 14
#DEFINE ADNUMERIC 131
#DEFINE ADBOOLEAN 11
#DEFINE ADERROR 10
#DEFINE ADUSERDEFINED 132
#DEFINE ADVARIANT 12
#DEFINE ADIDISPATCH 9
#DEFINE ADIUNKNOWN 13
#DEFINE ADGUID 72
#DEFINE ADDATE 7
#DEFINE ADDBDATE 133
#DEFINE ADDBTIME 134
#DEFINE ADDBTIMESTAMP 135
#DEFINE ADBSTR 8
#DEFINE ADCHAR 129
#DEFINE ADVARCHAR 200
#DEFINE ADLONGVARCHAR 201
#DEFINE ADWCHAR 130
#DEFINE ADVARWCHAR 202
#DEFINE ADLONGVARWCHAR 203
#DEFINE ADBINARY 128
#DEFINE ADVARBINARY 204
#DEFINE ADLONGVARBINARY 205
#DEFINE ADCHAPTER 136

FrontPage and Text File Databases

25. June 2005 Bob FrontPage Comments (0)

Summary

This article discusses how to use a SCHEMA.INI file to access information that is stored in various text file databases.

More Information

Start a blank SCHEMA.INI file
  1. Open a Web in FrontPage 2003 using HTTP
  2. Start a new text file
    1. Click File -> New
    2. Click "Text file" on the Task Pane
    3. Save the file as "_private/schema.ini"
  3. Close the text file
Add a database connection for text file databases
  1. Click Tools -> Site Settings
  2. Click the "Database" tab
  3. Click the "Add" button
  4. Name the connection "TEXTFILES"
  5. Choose "File or folder in current web site"
  6. Click the "Browse" button
    1. Double-click the "_private" folder
    2. Choose "Microsoft Text Driver (*.txt; &.csv)" as the file type
    3. Click "OK" to close the dialog
  7. Click "OK" to close the database connection dialog
  8. Click "OK" to close the site settings dialog
Using a Comma-Separated Value file
  1. If not already open, open the Web form earlier in FrontPage 2003 using HTTP
  2. Start a new page
  3. Save the page as "CSVTEST.htm" in the root of your web site
  4. Insert a form on the page:
    1. Click Insert -> Form -> Textbox
    2. Right-click the form and select "Form Field Properties"
    3. Name the field "Name"
    4. Click "OK" to close the text box properties dialog
    5. Right-click the form and select "Form Properties"
    6. Click the "Options" button
    7. Specify "_private/CSVTEST.csv" as the output path
    8. Choose "Text database usign comma as a separator"
    9. Make sure that the "Include field names" box is checked
    10. Click the "Save Fields" tab
    11. Check the boxes for "Remote computer name", "Browser type", and "Username"
    12. Specify a format for both the date and time
    13. Click "OK" to close the options dialog
    14. Click "OK" to close the form properties
  5. Save and close the page
  6. Preview the page in your browser and submit several data items
  7. Open the "_private/schema.ini" file from earlier
  8. Enter the following information:
    [CSVTEST.csv]
ColNameHeader=True
MaxScanRows=25
Format=CSVDelimited
CharacterSet=ANSI
  9. Save and close the "_private/schema.ini" file
  10. Start a new page
  11. Insert a database results region on the page:
    1. Click Insert -> Database -> Results
    2. Choose "TEXTFILES" for the connection and click "Next"
    3. Choose "CSVTEST.csv" for the record source and click "Next"
    4. Click "Next"
    5. Choose "Table - one record for row" and click "Next"
    6. Click "Finish"
  12. Save the page as "CSVTEST.asp" in the root of your web site
Using a Tab-Separated Value file
  1. If not already open, open the Web form earlier in FrontPage 2003 using HTTP
  2. Start a new page
  3. Save the page as "TABTEST.htm" in the root of your web site
  4. Insert a form on the page:
    1. Click Insert -> Form -> Textbox
    2. Right-click the form and select "Form Field Properties"
    3. Name the field "Name"
    4. Click "OK" to close the text box properties dialog
    5. Right-click the form and select "Form Properties"
    6. Click the "Options" button
    7. Specify "_private/TABTEST.txt" as the output path
    8. Choose "Text database usign tab as a separator"
    9. Make sure that the "Include field names" box is checked
    10. Click the "Save Fields" tab
    11. Check the boxes for "Remote computer name", "Browser type", and "Username"
    12. Specify a format for both the date and time
    13. Click "OK" to close the options dialog
    14. Click "OK" to close the form properties
  5. Save and close the page
  6. Preview the page in your browser and submit several data items
  7. Open the "_private/schema.ini" file from earlier
  8. Enter the following information: 
    [TABTEST.txt]
ColNameHeader=True
MaxScanRows=25
Format=TabDelimited
CharacterSet=ANSI
  9. Save and close the "_private/schema.ini" file
  10. Start a new page
  11. Insert a database results region on the page:
    1. Click Insert -> Database -> Results
    2. Choose "TEXTFILES" for the connection and click "Next"
    3. Choose "TABTEST.txt" for the record source and click "Next"
    4. Click "Next"
    5. Choose "Table - one record for row" and click "Next"
    6. Click "Finish"
  12. Save the page as "TABTEST.asp" in the root of your web site

References

The following articles discuss the SCHEMA.INI format and related concepts in detail; MSDN keeps rearranging their hyperlinks, so hopefully they are still live:

How to enable or change multiple FrontPage/ASP.NET database editor users

23. November 2004 Bob FrontPage Comments (0)

Behavior/Symptoms

When you create a database editor using the FrontPage 2003 ASP.NET Database Interface Wizard (DIW), you are prompted to create a user account for editing the database. After running the wizard, there is no interface for changing the user or password, and there is no provision for adding more than one user account as an editor.

Cause

This behavior is by design. The user account specified when created the DIW pages is hard-coded into the "web.config" files used by the database editor.

Workaround

To resolve this issue, you can modify the necessary "web.config" files to modify or add users.

When creating the database editor, FrontPage 2003 creates two "web.config" files, one will be in the root of the site, and the other will be in the folder containing the database editor. Currently, ASP.NET Security supports the MD5 and SHA-1 hash algorithms when configuring any user accounts in your "web.config" files for use with forms-based authentication. FrontPage 2003 creates user account information using the SHA-1 hash algorithm, but this article will explain how to customize that.

To modify or add users, use the following steps:

  1. Open the web site where you have used FrontPage 2003's Database Interface Wizard (DIW) to create an ASP.NET Database Editor.
  2. Open the "web.config" file in the root folder of your web site.
  3. Locate the section that resembles the following: 
    <authentication mode="Forms">
  <forms loginUrl="login.aspx">
    <credentials passwordFormat="SHA1">
       <user name="msbob" password="21BD12DC183F740EE76F27B78EB39C8AD972A757"/>
    </credentials>
  </forms>
</authentication>
  4. As previously mentioned, ASP.NET Security supports clear text and the MD5 and SHA-1 hash algorithms when configuring user accounts. To change the security method to clear text, change the passwordFormat to "clear". For example: 
    <credentials passwordFormat="Clear">
    NOTE - You could just as easily configure "MD5" for the passwordFormat.
  5. If you are configuring the passwordFormat as "SHA1" or "MD5", you can use the following sample code to create the password hashes: 
    <html>
<head>
<title>MD5/SHA-1 Hash Generator</title>
</head>
<body>
<h2>MD5/SHA-1 Hash Generator</h2>
<%
Dim strPassword As String = Request.Form("txtPassword")

If Len(strPassword)>0 Then
Dim objFormAuth As New System.Web.Security.FormsAuthentication()

Dim strHashSHA1 As String = 
objFormAuth.HashPasswordForStoringInConfigFile(strPassword, "SHA1")
Dim strHashMD5 As String = 
objFormAuth.HashPasswordForStoringInConfigFile(strPassword, "MD5")

Response.Write("<p>Clear: " & strPassword & "</p>")
Response.Write("<p>SHA-1: " & strHashSHA1 & "</p>")
Response.Write("<p>MD5: " & strHashMD5 & "</p>")
End If
%>
<form method="post">
<input type="text" name="txtPassword">
<input type="submit" value="Create Hashes">
</form>
</body>
</html>
  6. Modify or remove the existing user account, which may resemble the following: 
    <user name="msbob" password="21BD12DC183F740EE76F27B78EB39C8AD972A757"/>
  7. Add any aditional users as desired.
  8. The resulting credentials section of the "web.config" in the root of the web site may now resemble something like the following: 
    <credentials passwordFormat="Clear">
  <user name="user1" password="Password1"/>
  <user name="user2" password="Password2"/>
  <user name="user3" password="Password3"/>
</credentials>
  9. Save and close the "web.config" for the root folder of your web site.
  10. Open the "web.config" file in the "editor" folder of the ASP.NET database editor that you created in your web site. (For example, if you created a database editor for one of the tables in the built-in sample "Northwind" database, the default folder path from the root of your web site might resemble one of the following paths:
    • /Sample_interface/Categories/editor
    • /Sample_interface/Employees/editor
    • /Sample_interface/Products/editor
  11. Locate the section that resembles the following: 
    <authorization>
  <allow users="msbob"/>
  <deny users="*"/>
</authorization>
  12. Remove or add any users as desired, separating individual users with a comma for the delimiter.
  13. The resulting authorization section of the "web.config" in the "editor" folder for your database editor may now resemble something like the following: 
    <authorization>
  <allow users="user1,user2,user3"/>
  <deny users="*"/>
</authorization>
  14. Save and close the "web.config" in the "editor" folder for your database editor.

When you browse your database editor, you should now be able to enter the credentials for any user accounts that you created.

Additional Information

For additional information on ASP.NET Security and forms-based authentication, please see the following Microsoft Knowledge Base articles: