Prev: Show Billing Period
Next: Cannot right click Navigation Bar to get "View By" option in Access 2007
From: Box666 on 30 Apr 2010 11:49
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
From: Dirk Goldgar on 30 Apr 2010 12:07
"Box666" <bob(a)mystical.demon.co.uk> wrote in message
news:7b549eee-8ad7-41cd-bbdd-72eee8084f15(a)k29g2000yqh.googlegroups.com...
>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?

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

(please reply to the newsgroup)

From: Jerry Whittle on 30 Apr 2010 12:44
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Box666" wrote:

> 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
> .
>
From: Box666 on 1 May 2010 20:00
On 30 Apr, 17:07, "Dirk Goldgar" <d...(a)NOdataSPAMgnostics.com.invalid>
wrote:
> "Box666" <b...(a)mystical.demon.co.uk> wrote in message
>
> news:7b549eee-8ad7-41cd-bbdd-72eee8084f15(a)k29g2000yqh.googlegroups.com...
>
> >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?
>
> --
> 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.
From: Dirk Goldgar on 1 May 2010 22:42
"Box666" <bob(a)mystical.demon.co.uk> wrote in message
news:ba52c9b0-ac72-4831-b7ac-c4eee14a0fc4(a)p2g2000yqh.googlegroups.com...
>
> 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 ------


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

(please reply to the newsgroup)

 |  Next  |  Last
Pages: 1 2
Prev: Show Billing Period
Next: Cannot right click Navigation Bar to get "View By" option in Access 2007