In my last post I noted that we were having a problem where the connection to the server was being lost, which would make Access stop working. Our IT people determined that a utility in Windows 7 was automatically refreshing the network connections and that was dropping the connections. After they turned off that utility the problem has not shown up again, so it was not the Access Based Enumeration Directory Structure Server that was causing the problem.
Tuesday, May 15, 2012
Tuesday, April 3, 2012
MS Access, Windows 7, and an Access Based Enumeration Directory Structure Server
Our IT department recently switched us to Windows 7 and an access based enumeration directory structure server. I have not really noticed any difference for most applications. Except MS Access. Since the switch the connection to the server is frequently dropped. What this means is that when you ask Access to do something that requires a form that is not currently active you will get a “network access interrupted” error and the database freezes. To unfreeze the database you have to close it and reopen it. Even with an active form, refreshing it can generate a “network access interrupted” error. Trying to open the VBA editor will get you a “File Not Found” error that you cannot get out of: clicking “Okay” just generates a new “File Not Found” error. You have to crash the database.
Continuing to try to work around these dropped server connections will corrupt your database. I had that happen yesterday. This morning I had to close and reopen my database five times in one hour. And I wasn’t even working very actively with the database.
Friday, February 10, 2012
Line breaks in FileMaker Pro exports
We have a FileMaker Pro database as our reference database that is about to be retired. I exported the data from the database in order to see what was there and how it could be handled in order to get it into another database. What I found when I opened it up in Microsoft Excel and Microsoft Access was that some of the fields contained line breaks that show up as little boxes with arrows. I needed to either hide or get rid of these visible line breaks. Neither Excel or Access would find these using the Find function or the Find and Replace function, even using “^l” as the search term. The question was: How to get rid of those line breaks.
My solution was to open the file in Excel, then save it as a csv file. The csv file was then opened in Microsoft Word, which will find the line breaks as “^l”. I did a Find and Replace for all the line breaks, replacing them with a unique character so that I could later parse the data, putting the data from the separate lines into separate fields. In searching the data I found that any character I could both use and later parse on was already being used. I finally settle on the tilde “~” since it was used in the data to signify an approximate date. First I replaced the tilde with “ca.” then I replaced the line breaks with tildes. The file was then saved as a text file and reopened in Excel. No line breaks and ready to parse.
My solution was to open the file in Excel, then save it as a csv file. The csv file was then opened in Microsoft Word, which will find the line breaks as “^l”. I did a Find and Replace for all the line breaks, replacing them with a unique character so that I could later parse the data, putting the data from the separate lines into separate fields. In searching the data I found that any character I could both use and later parse on was already being used. I finally settle on the tilde “~” since it was used in the data to signify an approximate date. First I replaced the tilde with “ca.” then I replaced the line breaks with tildes. The file was then saved as a text file and reopened in Excel. No line breaks and ready to parse.
Friday, January 13, 2012
Getting Data From Another Form in MS Access
Sometimes in order to run the VBA code I need data from a form other than the one that contains the code. For example, I have a form that shows all the data for an folder in a collection, frmFolderData. In order to pull the data to populate that form I need to know the folder’s index number. If frmFolderData is being opened from the list of folders in frmSeriesFolderList it is pretty simple:
lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)
Where lngFolderID is a long variable for the folder index number, which is in the first column of the list box.
However if that is the code I use and then try to open frmFolderData from frmEADFolders I will get an error because frmSeriesFolderList is not open. I need to test for whether or not the form I want is open, or in this case, which of the two forms I could use is open. To do that I use the IsLoaded property.
Here is the code used in frmFolderData to decide whether to pull the needed data from frmSeriesFolderData or frmEADFolders:
If CurrentProject.AllForms("frmSeriesFolderList").IsLoaded Then
lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)
Where lngFolderID is a long variable for the folder index number, which is in the first column of the list box.
However if that is the code I use and then try to open frmFolderData from frmEADFolders I will get an error because frmSeriesFolderList is not open. I need to test for whether or not the form I want is open, or in this case, which of the two forms I could use is open. To do that I use the IsLoaded property.
Here is the code used in frmFolderData to decide whether to pull the needed data from frmSeriesFolderData or frmEADFolders:
If CurrentProject.AllForms("frmSeriesFolderList").IsLoaded Then
lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)
ElseIf CurrentProject.AllForms("frmEADFolders").IsLoaded Then
lngFolderID = Forms!frmEADFolders.lstFolders.Column(0)
End If
Friday, December 9, 2011
Input Box Error Capture in MS Access
I find input boxes quite useful for getting data from users, especially if it is just a single number, but input boxes can present a problem if the user cancels or exits the input box, or clicks “Okay” without entering anything in the input box. In all three of these cases the input box returns a zero-length string. You not only need to capture this error, but you need to discover the intent of the user in order to redirect the code in the right way. If the user meant to exit or cancel the input box you’ll want to exit the sub. If the user simply clicked “Okay” before entering the data you’ll want to offer them the chance to enter the data.
The initial error will be a “type mismatch” if the variable for the input data is dimensioned as an integer. Instead, use a string variable, then latter pass the value to an integer variable if needed.
Here is the error capture code for an input box where the user tells how many folders an overfilled folder should be divided into.
The initial error will be a “type mismatch” if the variable for the input data is dimensioned as an integer. Instead, use a string variable, then latter pass the value to an integer variable if needed.
Here is the error capture code for an input box where the user tells how many folders an overfilled folder should be divided into.
strSplit = InputBox("Split Folder " & intFolder & " into how many folders?")
'If the user fails to specify how many folders the folder is to be split into display an error
'message and re-present the input box.
If strSplit = "" Then
'Since VBA input boxes do not differentiate between "Cancel", "Exit", and "Okay (with no data entered)"
'the error capture needs a mechanism to allow the user to exit or cancel if they wish or enter data
'if they had forgotten to enter it.
If MsgBox("You have not specified the number of folders or have clicked 'Cancel'" & vbNewLine & "Do you wish to Cancel?", vbYesNo) = vbYes Then
'The user meant to exit or cancel, so exit the sub
Exit Sub
Else:
'The user did not mean to exit or cancel, so re-present the input box.
Call cmdSave_Click
End If
Else:
intSplit = strSplit
[rest of the code to split the folder]
End If
Friday, November 4, 2011
Capturing a list box error in MS Access.
There are several places in KIDB where the user can select from a list box and then click a button to perform an action on the selection. A good example of this is you can select a box from the list box on the Box Location form and click a button to move it, that is to assign it a new address in the stacks. A potential problem with this is if someone clicks the button without first selecting a row in the list box, which chokes the code. Here is how I capture that error in the On Click event of the Move Box button:
Private Sub cmdMoveBox_Click()
On Error GoTo Err_cmdMoveBox_Click
'Sub allows user to move a box from one address to another.
'Limits ability to move boxes to users with a permission level of 3 or higher.
Dim strDocName As String
Dim strLinkCriteria As String
Dim lst As Access.ListBox
Set lst = Me.BoxList
Dim intUser As Integer
strDocName = "frmBoxMove"
'Check for user authorization to delete boxes.
intUser = Nz(DLookup("[Level]", "tluUsers", "[UserID]= '" & Environ("username") & "'"))
If intUser > 2 Then
'Check to see if any boxes have been selected.
If lst.ItemsSelected.Count = 0 Then
'Let the user know what is wrong.
MsgBox "No box has been selected to move."
'Exit the sub.
Exit Sub
'Check that no more than 1 box has been selected.
ElseIf lst.ItemsSelected.Count > 1 Then
'Let the user know what is wrong.
MsgBox "Please select only one box to move."
'Clear the listbox selections.
lst.SetFocus
lst.ListIndex = -1
'Exit the sub.
Exit Sub
'If only one box is selected open the move box form.
Else:
strLinkCriteria = "[BoxID]=" & Me!BoxList.Column(11)
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If
Else:
MsgBox "Sorry, you are not authorized to move boxes."
End If
Exit_cmdMoveBox_Click:
Exit Sub
Err_cmdMoveBox_Click:
MsgBox Err.Description
Resume Exit_cmdMoveBox_Click
End Sub
Friday, September 30, 2011
EAD and Microsoft Access
Our collection database, and database of record, KIDB, is an Access database. We also have most of our folder lists in an Access database. What this means is that virtually all the metadata needed to construct EAD guides for our collections exists in Access databases. So instead of having a collection cataloged in MARC, extracting EAD from the MARC record using Terry Reese’s MarcEdit, tagging the container list with Ead McTaggart, merging the two resultant documents, and finally doing a fair amount of editing. I wanted push-button EAD.
I am not including all the code for doing this here. There is just too much of it, in reality it is most of the KIDB database. I will include the code that actually writes the front matter portion of the EAD document. You can find the code for the container list in an earlier post about EAD McTaggart. First a little bit about how to get ready to push the EAD button.
When I started this KIDB already had many of the elements needed to create an EAD guide. The collection title, creator, collection number, extent, etc. Some of the other material is pretty much boilerplate: contact information, repository, restrictions, citation, etc. What was missing were the descriptive elements like biography, abstract, organizational history, related collections, subjects. Step one was to build into KIDB a way to hold those elements and keep track of them. I did this with four tables. One, tblFrontMatter, has fields for the collection ID number, abstract, scope and content note, biography, organizational history, related collections, and subjects. A second, tblEntities, has fields for contact information, user information, and repository. The third keeps track of who added what to tblFrontMatter for which collection and when. The fourth is used to record who made any changes to the data in tblFrontMatter and when it was done.
With all the necessary data now contained in one database it was simply a matter of writing the code to tag it properly. The entire tagged code for the front matter is compiled in one variable, which is then added to a table designed to hold it. Then Ead McTaggart is called and he adds the tagged container list to the table, one row for each folder. When that is done a report pulls out the data row by row and exports it as an xml file. The table is then emptied.
Here is the code for tagging the front matter:
A few notes on some of the variables. The collection number is in two variables: strCollNum has the collection number as it appears in KIDB (i.e. 5169/043 AV), strPathNum has the collection number formatted to work as a valid file name (i.e. 5619-043av). How you choose to store multiple value fields will determine how you populate variables like strCreator, strSubjects, strRelated. I list creators in one field, delimited with a semi-colon. It is the same with related collections. When I pull the data I use the split function to separate out the values, format and tag then for EAD, then reassemble them in a single variable. Subjects are stored with a line break between them, each one beginning with the MARC field code (600: for a person and so on). For subjects I split on the line break (chr(10)) and use the MARC field to set the tags (persname, corpname, etc.) and the attributes.
I am not including all the code for doing this here. There is just too much of it, in reality it is most of the KIDB database. I will include the code that actually writes the front matter portion of the EAD document. You can find the code for the container list in an earlier post about EAD McTaggart. First a little bit about how to get ready to push the EAD button.
When I started this KIDB already had many of the elements needed to create an EAD guide. The collection title, creator, collection number, extent, etc. Some of the other material is pretty much boilerplate: contact information, repository, restrictions, citation, etc. What was missing were the descriptive elements like biography, abstract, organizational history, related collections, subjects. Step one was to build into KIDB a way to hold those elements and keep track of them. I did this with four tables. One, tblFrontMatter, has fields for the collection ID number, abstract, scope and content note, biography, organizational history, related collections, and subjects. A second, tblEntities, has fields for contact information, user information, and repository. The third keeps track of who added what to tblFrontMatter for which collection and when. The fourth is used to record who made any changes to the data in tblFrontMatter and when it was done.
With all the necessary data now contained in one database it was simply a matter of writing the code to tag it properly. The entire tagged code for the front matter is compiled in one variable, which is then added to a table designed to hold it. Then Ead McTaggart is called and he adds the tagged container list to the table, one row for each folder. When that is done a report pulls out the data row by row and exports it as an xml file. The table is then emptied.
Here is the code for tagging the front matter:
strText = "<" & Chr(63) & "xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & Chr(63) & ">"
strText = strText & "<" & Chr(63) & "xml-stylesheet type=" & Chr(34) & "text/xsl" & Chr(34) & " href=" & Chr(34) & "../styles/style.xsl" & Chr(34) & Chr(63) & ">" & Chr(10)
strText = strText & "<" & Chr(33) & "DOCTYPE ead PUBLIC " & Chr(34) & Chr(43) & "//ISBN 1-931666-00-8//DTD ead.dtd "
strText = strText & "(Encoded Archival Description (EAD) Version 2002)//EN" & Chr(34) & " " & Chr(34) & "../dtds/ead.dtd" & Chr(34) & ">" & Chr(10)
strText = strText & "<ead>" & Chr(10) & "<eadheader repositoryencoding=" & Chr(34) & "iso15511"
strText = strText & Chr(34) & " relatedencoding=" & Chr(34) & "MARC21" & Chr(34) & " countryencoding="
strText = strText & Chr(34) & "iso3166-1" & Chr(34) & " scriptencoding=" & Chr(34) & "iso15924" & Chr(34)
strText = strText & " dateencoding=" & Chr(34) & "iso8601" & Chr(34) & " langencoding=" & Chr(34) & "iso639-2b" & Chr(34) & ">" & Chr(10)
strText = strText & "<eadid mainagencycode=" & Chr(34) & "nic" & Chr(34) & " countrycode="
strText = strText & Chr(34) & "us" & Chr(34) & " publicid=" & Chr(34) & "-//Cornell University::"
strText = strText & "Cornell University Library::Kheel Center for Labor-Management Documentation and Archives//"
strText = strText & "TEXT(US::NIC::KCL0" & strCollNum & "::" & strCollTitle & ".)//EN" & Chr(34) & ">"
strText = strText & "KCL0" & strPathNum & ".xml</eadid>" & Chr(10)
strText = strText & "<filedesc>" & Chr(10)
strText = strText & "<titlestmt>" & Chr(10)
strText = strText & "<titleproper>Guide to " & strCollTitle & "<date> " & strDate & "</date></titleproper>" & Chr(10)
strText = strText & "<titleproper type=" & Chr(34) & "sort" & Chr(34) & ">" & strCollTitle & "</titleproper>" & Chr(10)
strText = strText & "<author>Compiled by " & strProcessor & "</author>" & Chr(10)
strText = strText & "</titlestmt>" & Chr(10)
strText = strText & "<publicationstmt>" & Chr(10)
strText = strText & "<publisher>Kheel Center for Labor-Management Documentation and Archives, Cornell University Library</publisher>" & Chr(10)
strText = strText & "<date>" & Format(dteDate, "MMMM dd, yyyy") & "</date>" & Chr(10)
strText = strText & "</publicationstmt>" & Chr(10)
strText = strText & "<notestmt>" & Chr(10)
strText = strText & "<note audience=" & Chr(34) & "internal" & Chr(34) & ">" & Chr(10)
strText = strText & "<p><subject>Labor</subject></p>" & Chr(10)
strText = strText & "</note>" & Chr(10)
strText = strText & "</notestmt>" & Chr(10)
strText = strText & "</filedesc>" & Chr(10)
strText = strText & "<profiledesc>" & Chr(10)
strText = strText & "<creation>Finding aid encoded by KIDB, Ead McTaggart, and " & strEncoder & ", <date>" & Format(Date, "MMMM dd, yyyy") & "</date></creation>" & Chr(10)
strText = strText & "</profiledesc>" & Chr(10)
strText = strText & "</eadheader>" & Chr(10)
strText = strText & "<frontmatter>" & Chr(10) & "<titlepage>"
strText = strText & "<titleproper>Guide to the " & strCollTitle & "<lb/></titleproper>" & Chr(10)
strText = strText & "<num>Collection Number: " & strCollNum & "</num>" & Chr(10)
strText = strText & strAddress
strText = strText & "<defitem>"
strText = strText & "<label>Compiled by:</label>"
strText = strText & "<item>" & strProcessor & "</item>"
strText = strText & "</defitem>"
strText = strText & "<defitem>"
strText = strText & "<label>EAD encoding:</label>"
strText = strText & "<item>" & strEncoder & ", " & Format(Date, "MMMM dd, yyyy") & "</item>"
strText = strText & "</defitem>"
strText = strText & "</list>"
strText = strText & "<date>© " & DatePart("yyyy", Date) & " Kheel Center for Labor-Management Documentation and Archives, Cornell University Library </date>" & Chr(10)
strText = strText & "</titlepage>" & Chr(10) & "</frontmatter>" & Chr(10)
strText = strText & "<archdesc level=" & Chr(34) & "collection" & Chr(34) & ">" & Chr(10) & Chr(9) & Chr(9) & "<did>" & Chr(10)
strText = strText & "<head id=" & Chr(34) & "a1" & Chr(34) & ">DESCRIPTIVE SUMMARY</head>" & Chr(10)
strText = strText & "<unittitle label=" & Chr(34) & "Title:" & Chr(34) & " encodinganalog=" & Chr(34) & "MARC 245$a" & Chr(34) & ">" & strCollTitle & "," & Chr(10)
strText = strText & "<unitdate encodinganalog=" & Chr(34) & "MARC 245$f" & Chr(34) & ">" & strDate & "</unitdate>" & Chr(10)
strText = strText & "</unittitle>" & Chr(10)
strText = strText & "<unitid label=" & Chr(34) & "Collection Number:" & Chr(34) & ">" & strCollNum & "</unitid>" & Chr(10)
strText = strText & "<origination label=" & Chr(34) & "Creator:" & Chr(34) & ">" & Chr(10)
strText = strText & "<persname encodinganalog=" & Chr(34) & "MARC 100" & Chr(34) & " role=" & Chr(34) & "creator" & Chr(34) & ">" & strCollCreator & "</persname>" & Chr(10)
strText = strText & "</origination>" & Chr(10)
strText = strText & "<physdesc label=" & Chr(34) & "Quantity:" & Chr(34) & " encodinganalog=" & Chr(34) & "MARC 300" & Chr(34) & ">" & dblLinear & " linear ft.</physdesc>" & Chr(10)
strText = strText & "<physdesc label=" & Chr(34) & "Forms of Material:" & Chr(34) & ">Articles, reprints, pamphlets, correspondence, photographs.</physdesc>" & Chr(10)
strText = strText & strRepository & Chr(10)
strText = strText & strAbstract
strText = strText & "<langmaterial label=" & Chr(34) & "Language:" & Chr(34) & ">Collection material in <language encodinganalog=" & Chr(34) & "MARC 041" & Chr(34) & " langcode=" & Chr(34) & "eng" & Chr(34) & ">English</language>" & Chr(10)
strText = strText & "</langmaterial>" & Chr(10)
strText = strText & "</did>" & Chr(10)
strText = strText & strTopOrgHist
strText = strText & strBio
strText = strText & strOrgHist
strText = strText & strScope
strText = strText & strSubjects
strText = strText & "<descgrp><head id=" & Chr(34) & "a10" & Chr(34) & ">INFORMATION FOR USERS</head>"
strText = strText & "<accessrestrict><head>Access Restrictions:</head>"
strText = strText & "<p>Access to the collections in the Kheel Center is restricted. Please contact a reference archivist for access to these materials.</p>"
strText = strText & "</accessrestrict><userestrict><head>Restrictions on Use:</head>"
strText = strText & "<p>This collection must be used in keeping with the Kheel Center Information Sheet and Procedures for Document Use.</p>"
strText = strText & "</userestrict><prefercite><head>Cite As:</head>"
strText = strText & "<p>" & strCollTitle & " #" & strCollNum & ". Kheel Center for Labor-Management Documentation and Archives, Cornell University Library.</p>"
strText = strText & "</prefercite></descgrp>"
strText = strText & strRelated
A few notes on some of the variables. The collection number is in two variables: strCollNum has the collection number as it appears in KIDB (i.e. 5169/043 AV), strPathNum has the collection number formatted to work as a valid file name (i.e. 5619-043av). How you choose to store multiple value fields will determine how you populate variables like strCreator, strSubjects, strRelated. I list creators in one field, delimited with a semi-colon. It is the same with related collections. When I pull the data I use the split function to separate out the values, format and tag then for EAD, then reassemble them in a single variable. Subjects are stored with a line break between them, each one beginning with the MARC field code (600: for a person and so on). For subjects I split on the line break (chr(10)) and use the MARC field to set the tags (persname, corpname, etc.) and the attributes.
Subscribe to:
Posts (Atom)