Is this too many records

[Box666]

I have been asked if an access database can import a csv file with
approx 2.5 millon rows (but only 2 columns)

One of the above columns is a 9 digit ref no - only numbers no
characters - we then want to create a simple search form to search
against the ref no.

So can access handle that many records and if so what is the best way
of creating / setting up a / the tables so that the search form will
still work comparitivly quickly.

Alternatively if Access is not suitable what program would you
suggest.

with thanks

Bob

 

[Dirk Goldgar]

I have been asked if an access database can import a csv file with
approx 2.5 millon rows (but only 2 columns)

One of the above columns is a 9 digit ref no - only numbers no
characters - we then want to create a simple search form to search
against the ref no.

So can access handle that many records and if so what is the best way
of creating / setting up a / the tables so that the search form will
still work comparitivly quickly.

Alternatively if Access is not suitable what program would you
suggest.

An Access database isn't limited by the number of records, but by the total
size of the database file needed to hold them (and any other database
objects). A table with 2.5 million small rows can easily be held by an
Access database, even if the rows are indexed on the ref no (which they
should be). If the rows are large, maybe not. What is in the second column
of your import file? If it's text strings, what is the average length of
the strings?

Are the ref nos in this file/table unique -- no duplicates -- or can several
records have the same ref no?

 

[Jerry Whittle]

Access should be able to handle importing this many records.

Is the Ref No column unique or are there duplicates? If there are
duplicates, create an index on that field to speed up searchs.

 

[Box666]

An Access database isn't limited by the number of records, but by the total
size of the database file needed to hold them (and any other database
objects).  A table with 2.5 million small rows can easily be held by an
Access database, even if the rows are indexed on the ref no (which they
should be).  If the rows are large, maybe not.  What is in the secondcolumn
of your import file?  If it's text strings, what is the average length of
the strings?

Are the ref nos in this file/table unique -- no duplicates -- or can several
records have the same ref no?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Each ref no is unique - so no duplicates. The 2nd column is text only
max 20 char.

 

[Dirk Goldgar]

ch ref no is unique - so no duplicates. The 2nd column is text only ax 20
char.

No problem. The RefNo field would be the primary key. You could easily
make a form, bound to the table, in which you key the refno you're looking
for into an unbound text box, and then code in the text box's AfterUpdate
event that locates the record that corresponds to the refno the user entered
in the text box. Code would look something like this:

'------ start of example code ------
Private Sub txtFindRefNo_AfterUpdate()

If IsNull(Me.txtFindRefno) Then

With Me.RecordsetClone
.FindFirst "RefNo = " & Me.txtFindRefNo
If .NoMatch Then
MsgBox "RefNo " & Me.txtRefNo & " was not found."
Else
Me.Bookmark = .Bookmark
End If
End With

End If

End Sub
'------ end of example code ------

 

[Box666]

No problem.  The RefNo field would be the primary key.  You could easily
make a form, bound to the table, in which you key the refno you're looking
for into an unbound text box, and then code in the text box's AfterUpdate
event that locates the record that corresponds to the refno the user entered
in the text box.  Code would look something like this:

'------ start of example code ------
Private Sub txtFindRefNo_AfterUpdate()

    If IsNull(Me.txtFindRefno) Then

        With Me.RecordsetClone
            .FindFirst "RefNo = " & Me.txtFindRefNo
            If .NoMatch Then
                MsgBox "RefNo " & Me.txtRefNo & " was notfound."
            Else
                Me.Bookmark = .Bookmark
            End If
        End With

    End If

End Sub
'------ end of example code ------

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Great news, thank you very much