Is this too many records

  • Thread starter Thread starter Box666
  • Start date Start date
B

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
 
Box666 said:
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.
Click to expand...


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?
 
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.
 
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)
Click to expand...

Each ref no is unique - so no duplicates. The 2nd column is text only
max 20 char.
 
Box666 said:
ch ref no is unique - so no duplicates. The 2nd column is text only ax 20
char.
Click to expand...

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 ------
 
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)
Click to expand...

Great news, thank you very much
 
Back
Top