MaxLocksPerFile increase for Access database table with 2.1 m records

[BrittanyA]

I have an Access 2010 database with 2.1 million records. There are several fields in one of my tables that were created as text, but I need to change them to dates. When I attempt to change the data type, I get the error message, "There isn't enough disk space or memory."

I found Microsoft instructions to increase the MaxLocksPerFile (http://support.microso ft.com/kb/286153), but I am not very tech savvy and am having trouble understanding what to do with the code they provide. Could someone please walk me through it? Also, for a table with 2.1 m records, what value would be sufficient for the MaxLocksPerFile ? Any assistance you could provide would be so appreciated--I am a graduate student and need to prepare this data for my thesis.

Thank you!

[NeoPa]

Try exporting the data, changing the design of the table, importing the data back in. You may well need to ensure the text data exported works properly as a date import.

[Mihail]

Another suggestion:
Create more fields (how many you need) in the table and format it as date data type.
Loop (using VBA) thru all records and fill this new fields based on values from the oldest fields.
After that remove the oldest fields and, if you wish, rename the new fields.
Of course (if that will work) you must redesign the relationship between tables if necessary.

Before start take a look here:

http://bytes.com/topic/access/answers/925663-discussion-sql-date-literals-regional-settings

I repeat: I'm not sure that will work (from the view point of memory).
Also is a time consuming procedure. But if you run this only once, even for 2.1 millions of records, I think is not a problem.
And is very easy to implement.

If you will try this approach let me know, please, if it work.

Good luck !

[TheSmileyCoder]

I think if it was me, I would create an extra field dt_NewDate of type date/time(As mihail) then run an update query to update the new field to be equal to the value of the old field.

If the dates are stored as text (And they were also input as text) it is likely that there could be certain values that do not convert nicely. This would also give you the chance to catch, and examine those rows.

[ADezii]

The 'first' thing that I would do is to Run a Query that specifically identifies which Records have Invalid Dates in the Date Field. I have intentionally entered 6 Invalid Dates into the [Order Date] Field of the Orders Table in the Northwind Database. The Query will specifically identify these Records, along with the Primary Key Values. The best part is that the Query is Updatable, in that you can correct this Records right in the Query Grid.

1. Query SQL:
   

   SELECT Orders.OrderID, Orders.OrderDate
   FROM Orders
   WHERE (((IsDate([OrderDate]))=0));

2. OUTPUT:
   

   Order ID	Order Date
   10611	   Mar.16-2011
   10616	   12212002
   10633	   7_16/2008
   10676	   20102312
   10728	   2/30/2007
   11078       NULL

[BrittanyA]

Thank you to each of you for your suggestions! Using ADezii's suggestion, I found there were around 9,000 missing values (99/99/9999) for one of the original text variables. For the remainder, I did as suggested by Smiley and successfully ran an update query to change the data type using a new variable. However, I failed to mention that I am working with Mexican data and the MM/DD fields are reversed. When I ran the update query, records obviously reversed (e.g., 25/06/1980) were recognized by Access, but those that appeared to be correct were not (e.g., 06/07/1980). Another issue was that it appeared to update only ~1.6 m records in the first pass. Not sure why it couldn't handle the full 2.1 m.

I wish that I had even a little understanding of VBA, but I don't, so I think I will export to SAS and clean the data from there. Thank you all once again for your help! This has been quite the learning experience!

[TheSmileyCoder]

Dates written as text is always baaaad news, unless it’s an automated output from a system, so that the dates are consistently written using the same format. Just a few examples of various date formats in text representation:
25/06/1980
25-06-1980
1980-06-25
19800625
25-06-80

My approach now would be to start by automatically converting the dates that are convertible, and see how much is left. For that purpose I have written 2 custom functions. In Access, you create a custom module and copy paste the code into there. The purpose of 1 of the functions is to ensure that we only convert those dates that are convertible, and the other is to actually convert the date. For more info on custom function use in Access see:
How to use a custom function in MS Access
Both codes are shown below:

Public Function validForConversion(strMexicanDate As String) As Boolean
  'We only want to allow automatic conversion for dates matching a certain critery
  'The formatdatetosql assumes that the text date is written as 25/06/1980
  'Criteria 1. Length must be 10
  If Len(strMexicanDate) <> 10 Then
    validForConversion = False
    Exit Function
  End If
  
  'Criteria 2. Check that access even recognizes it as a date
  If Not IsDate(strMexicanDate) Then
    validForConversion = False
    Exit Function
  End If
End Function

Public Function formatDateToSQL(strMexicanDate As String) As String
  Dim strDay As String
  Dim strMonth As String
  Dim strYear As String
  strDay = Mid(strMexicanDate, 1, 2)
  strMonth = Mid(strMexicanDate, 4, 2)
  strYear = Mid(strMexicanDate, 7, 4)
  formatDateToSQL = "#" & strMonth & "/" & strDay & "/" & strYear & "#"
End Function

Now to add a query that will convert the “valid” dates, leaving the invalid behind. In my example below I assume the fields in question are simply named “dt_NewDate” and “txt_OldDate”, and the table in my example is simply named tbl_Example.

Update tbl_Example SET [dt_NewDate]=formatDateToSQL([txt_OldDate]),[txt_OldDate]=Null WHERE validForConversion ([txt_OldDate])

Im not 100% sure if that is correct SQL syntax. Im still not all that great at writing SQL directly.


Now, of course if you are more comfortable working in SAS that is absolutely fine. I just wanted to add the info.