Hi Amelia,
There's no set limit on the number of records in Access, though an
Access 97 mdb file can't be bigger than 1 (or is it 2) GB. So that
shouldn't be a problem.
However there are all sorts of potential problems with importing from
Excel sheets. Most of them result from the fact that Access requires
data in every field to conform to the field type, while Excel does'nt
have a concept of "field type" and allows the user to type just about
anything anywhere. It's also possible to use column headings in Excel
that won't translate into legal Access fieldnames; so it's best to make
sure your column headings only contain letters, numbers and underscores.
Also, the Access routine that imports Excel data doesn't allow direct
control over the types of the fields it creates.
You can work round this in any of the following ways:
1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.
2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field).
Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in thecells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access.
(Just to make things more confusing, Access applies different rules when
you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)
3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.
trying to import a large spreadsheet from excel 97 to
access 97. Its got 4 sheets on it. 3 sheets will import
but the last one will start, then access said it had a
problem importing the sheet and it was not done.
Do you know if there is a limit of the number of data
entries in access? we have 119,999 at the moment.
Any other ideas?
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
