A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Is this too many records



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 04:49 PM posted to microsoft.public.access.gettingstarted
Box666
external usenet poster
 
Posts: 60
Default Is this too many records

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
  #2  
Old April 30th, 2010, 05:07 PM posted to microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Is this too many records

"Box666" wrote in message
...
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)

  #3  
Old April 30th, 2010, 05:44 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Is this too many records

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
.

  #4  
Old May 2nd, 2010, 01:00 AM posted to microsoft.public.access.gettingstarted
Box666
external usenet poster
 
Posts: 60
Default Is this too many records

On 30 Apr, 17:07, "Dirk Goldgar"
wrote:
"Box666" wrote in message

...

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.
  #5  
Old May 2nd, 2010, 03:42 AM posted to microsoft.public.access.gettingstarted
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Is this too many records

"Box666" wrote in message
...

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)

  #6  
Old May 2nd, 2010, 06:54 PM posted to microsoft.public.access.gettingstarted
Box666
external usenet poster
 
Posts: 60
Default Is this too many records

On 2 May, 03:42, "Dirk Goldgar"
wrote:
"Box666" wrote in message

...



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)


Great news, thank you very much
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.