Microsoft Bob

Just a short, simple blog for Bob to share some tips and tricks.

Be sure to check out my non-technical blog at www.bobsbasement.net.

Month List

Access Macro: Export Table/Query To Excel

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
Posted: Feb 17 2008, 01:06 by Bob | Comments (0)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Office
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us

Access: Database Schema Report

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!"
Posted: Feb 16 2008, 23:58 by Bob | Comments (0)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: Office
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us

Determining Access Column Types for FrontPage Database Results Fields

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
Posted: Dec 14 2006, 10:29 by Bob | Comments (0)
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: FrontPage
Social Bookmarks: E-mail | Kick it! | DZone it! | del.icio.us