Just a short, simple blog for Bob to share his thoughts.
17 February 2008 • by Bob • Office
I wrote this Access VBA Macro for a friend to export an Access table or query to a spreadsheet; it might come in handy. ;-]
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
17 February 2008 • by Bob • Office
This article shows you a Windows Script Host (WSH) application that will create a report based on the schema of an Access Database.
strDatabaseFile
and strOutputFile
constants for your database and desired report name.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 "" & _
strDatabaseFile & ""</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!"
14 December 2006 • by Bob • FrontPage
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.
There are two different places where the column/field types will be listed:
fp_sColTypes
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 |
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 |
25 June 2005 • by Bob • FrontPage
This article discusses how to use a SCHEMA.INI file to access information that is stored in various text file databases.
[CSVTEST.csv] ColNameHeader=True MaxScanRows=25 Format=CSVDelimited CharacterSet=ANSI
[TABTEST.txt] ColNameHeader=True MaxScanRows=25 Format=TabDelimited CharacterSet=ANSI
The following articles discuss the SCHEMA.INI format and related concepts in detail; MSDN keeps rearranging their hyperlinks, so hopefully they are still live:
23 November 2004 • by Bob • FrontPage
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.
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.
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:
<authentication mode="Forms"> <forms loginUrl="login.aspx"> <credentials passwordFormat="SHA1"> <user name="msbob" password="21BD12DC183F740EE76F27B78EB39C8AD972A757"/> </credentials> </forms> </authentication>
<credentials passwordFormat="Clear">NOTE - You could just as easily configure "MD5" for the passwordFormat.
<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>
<user name="msbob" password="21BD12DC183F740EE76F27B78EB39C8AD972A757"/>
<credentials passwordFormat="Clear"> <user name="user1" password="Password1"/> <user name="user2" password="Password2"/> <user name="user3" password="Password3"/> </credentials>
<authorization> <allow users="msbob"/> <deny users="*"/> </authorization>
<authorization> <allow users="user1,user2,user3"/> <deny users="*"/> </authorization>
When you browse your database editor, you should now be able to enter the credentials for any user accounts that you created.
For additional information on ASP.NET Security and forms-based authentication, please see the following Microsoft Knowledge Base articles: