I've only tested to a half million records or so. I think the limitation is
the lesser of the file system limits or Access. Access databases I believe
are limited to ~ 2 gB in size.
FYI;
From the resource kit;
Table 3.10 FAT16 Size Limits
Description Limit
Maximum file size 2^32 - 1 bytes
Maximum volume size 4 GB
Files per volume 2^16
Maximum Sizes on FAT32 Volumes
The FAT32 volume must have at least 65,527 clusters. The maximum number of
clusters on a FAT32 volume is 4,177,918. Windows 2000 creates volumes up to
32 GB, but you can use larger volumes created by other operating systems
such as Windows 98. Table 3.11 lists FAT32 size limits.
Table 3.11 FAT32 Size Limits
Description Limit
Maximum file size 2^32 - 1 bytes
Maximum volume size 32 GB (This is due to the Windows 2000 format utility.
The maximum volume size that Windows 98 can create is 127.53 GB). Files per
volume Approximately 4 million
Important
Windows 2000 can format new FAT32 volumes up to 32 GB in size but can mount
larger volumes (for example, up to 127.53 GB and 4,177,918 clusters from a
volume formatted with the limits of Windows 98). It is possible to mount
volumes that exceed these limits, but doing so has not been tested and is
not recommended.
Maximum Sizes on NTFS Volumes
In theory, the maximum NTFS volume size is 2^32 clusters. However, even if
there were hardware available to supply a logical volume of that capacity,
there are other limitations to the maximum size of a volume.
One of these limitations is partition tables. By industry standards,
partition tables are limited to 2^32 sectors. Sector size, another
limitation, is a function of hardware and industry standards, and is
typically 512 bytes. While sector sizes might increase in the future, the
current size puts a limit on a single volume of 2 terabytes (2^32 * 512
bytes, or 241 bytes).
For now, 2 terabytes should be considered the practical limit for both
physical and logical volumes using NTFS.
The maximum number of files on an NTFS volume is 2^32 - 1. Table 3.12 lists
NTFS size limits.
Table 3.12 NTFS Size Limits
Description Limit
Maximum file size 264 bytes - 1 KB (On disk format)
244 bytes - 64 KB (Implementation)
Maximum volume size 264 allocation units (On disk format)
2^32 allocation units (Implementation)
Files per volume 2^32 - 1
--
Regards,
Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
:
| Hi! Dave/Ed,
|
| Dave, Your method works fine and it suits my needs. Just one question.
|
| Will a .txt file handle 1 million+ records? and what is the limit for
this?
|
| Ed, Your code gives me compile error: "Invalid use of property" in the
line
| "dbOpenForwardOnly" Do you want me to make changes to this code before
| running it?
|
| PS: My table name is Table1
|
| Maxi
|
| "(e-mail address removed)" wrote:
|
| > Hello mac_see,
| > Try the code below. If you don't need to create the target table just
| > comment the first "db.Execute" line.
| > I must credit Ken Getz with the original code for the GetString
| > function. I had to modify his GetPart function to suit my needs.
| >
| > Sub ParseMyNumbers()
| > On Error GoTo ErrorHandler
| > Dim db As DAO.Database
| > Dim rs As DAO.Recordset
| > Dim sTableName As String
| > Dim sSQL As String
| > Set db = CurrentDb
| > sTableName = "Table2"
| > sSQL = "CREATE TABLE " & sTableName & " (Field1 BYTE, Field2 BYTE, " _
| > & "Field3 BYTE, Field4 BYTE, Field5 BYTE, Field6 BYTE,
| > Field7 " _
| > & "BYTE, Field8 BYTE, Field9 BYTE, Field10 BYTE );"
| > 'Debug.Print sSQL
| > db.Execute sSQL
| > 'GoTo ThatsIt
| > sSQL = "SELECT CSNumbers FROM Table1;"
| > 'Debug.Print sSQL
| > Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) 'dbOpenDynaset
| > dbOpenForwardOnly
| > With rs
| > .MoveLast 'force error 3021 if no records
| > .MoveFirst
| > Do Until .EOF
| > sSQL = "INSERT INTO " & sTableName & " (Field1, Field2, Field3,
| > " _
| > & "Field4, Field5, Field6, Field7, Field8, Field9, Field10
| > ) " _
| > & "VALUES (" _
| > & Trim(GetNumber(0, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(1, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(2, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(3, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(4, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(5, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(6, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(7, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(8, .Fields(0), ",")) & ", " _
| > & Trim(GetNumber(9, .Fields(0), ",")) _
| > & ");"
| > db.Execute sSQL
| > .MoveNext
| > Loop
| > End With
| > rs.Close
| > Set rs = Nothing
| > GoTo ThatsIt
| > ErrorHandler:
| > Select Case Err.Number
| > Case 3021 'No Records
| > Case Else
| > MsgBox "Problem with ParseMyNumbers()" & vbCrLf _
| > & "Error: & " & Err.Number & ": " & Err.Description
| > End Select
| > ThatsIt:
| > Set db = Nothing
| > End Sub
| >
| > Function GetString(pPart As Integer, pValue As Variant, _
| > Optional sDivider As String) As String
| > 'pPart is the section of pValue to be extracted
| > 'pValue is the field or expression to be parsed
| > On Error GoTo ErrorHandler
| > Dim I As Integer
| > Dim sPart As String
| > Dim iTildeAt As Integer
| > Dim sNextChar As Variant
| >
| > If Nz(sDivider, "") = "" Then
| > sDivider = "~"
| > Else
| > sDivider = sDivider
| > End If
| >
| > If IsNull(pValue) Then
| > GetString = ""
| > Exit Function
| > End If
| >
| > iTildeAt = 0
| > For I = 1 To pPart
| > iTildeAt = InStr(iTildeAt + 1, pValue, sDivider)
| > Next
| >
| > 'Extract the value
| > For I = iTildeAt + 1 To Len(pValue)
| > sNextChar = Mid$(pValue, I, 1)
| > If sNextChar = sDivider Then
| > Exit For
| > End If
| > sPart = sPart & sNextChar
| > Next
| > ' MsgBox Val(sPart)
| > GetString = CStr(sPart)
| > Exit Function
| > ErrorHandler:
| > GetString = ""
| > MsgBox "Error " & Err.Number & ": " & Err.Description &
| > vbCrLf _
| > & "in GetString()"
| > Exit Function
| > End Function
| >
| > I hope this helps,
| > Ed.
| >
| >
