M
Mary Ann
I am using Access 2003
I have a table of nearly 10,000 records which I have
transferred from Excel to Access. The table consists of
contact names, company names, company addresses and sorts
of contact.
I want to split the flat table into 3 related tables -
Contacts, Companies and Sorts and use the Table Analyser
to do this. There will be a one to many relationship
between the Companies and Contacts tables and between the
Sorts and Contacts table. I have previously used the
analyser to split a smaller similar database and it
worked fine. However, when I used the analyser on this
database (and spent hours in the wizard correcting
company address data!!) I was told when I click the
Finish button "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry". I had no
option but to cancel the Wizard at this point.
I do not work in the registry so have no idea how to do
this and am very cautious about going in there! I think
I can get round this by splitting my original flat table
into two, then analyse the two smaller units and then use
a combination of Update and Append queries to get the two
sets of three split tables back into one set of three.
What is the default maximum number of records the table
analyser can cope with?
I am concerned that even if I do manage to satisfactorily
split my table this error message may occur when I am
doing other things in the database e.g. running an
ordinary select query. Is that the case or is the error
message just to do with the table analyser wizard? In
the normal run of things, with its typical default
settings, can Access manage a table of 10,000 records?
Secondly I would greatly appreciate some reassurance that
I am going about this in an OK way. What I intend to do
is as follows: once I have split the two original flat
tables, I plan to append the second split companies table
into the first split companies table - hiding the id
field so the second batch of companies will get a new
ID. I then plan to update the company foreign key in the
second split contacts table to e.g. [CompanyID] + n where
n is the number of company records in the first split
companies table. I then repeat the process for the Sorts
tables and foreign key. Finally I append the contacts
from the second split table into the first split contacts
table - again hiding the id field.
Does that sound about right????
I apologise for the length of this message but thought it
wiser to explain exactly what happened and what I am
planning.
Any help from you MVPs would be fantastic!
Regards
Mary Ann
I have a table of nearly 10,000 records which I have
transferred from Excel to Access. The table consists of
contact names, company names, company addresses and sorts
of contact.
I want to split the flat table into 3 related tables -
Contacts, Companies and Sorts and use the Table Analyser
to do this. There will be a one to many relationship
between the Companies and Contacts tables and between the
Sorts and Contacts table. I have previously used the
analyser to split a smaller similar database and it
worked fine. However, when I used the analyser on this
database (and spent hours in the wizard correcting
company address data!!) I was told when I click the
Finish button "File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry". I had no
option but to cancel the Wizard at this point.
I do not work in the registry so have no idea how to do
this and am very cautious about going in there! I think
I can get round this by splitting my original flat table
into two, then analyse the two smaller units and then use
a combination of Update and Append queries to get the two
sets of three split tables back into one set of three.
What is the default maximum number of records the table
analyser can cope with?
I am concerned that even if I do manage to satisfactorily
split my table this error message may occur when I am
doing other things in the database e.g. running an
ordinary select query. Is that the case or is the error
message just to do with the table analyser wizard? In
the normal run of things, with its typical default
settings, can Access manage a table of 10,000 records?
Secondly I would greatly appreciate some reassurance that
I am going about this in an OK way. What I intend to do
is as follows: once I have split the two original flat
tables, I plan to append the second split companies table
into the first split companies table - hiding the id
field so the second batch of companies will get a new
ID. I then plan to update the company foreign key in the
second split contacts table to e.g. [CompanyID] + n where
n is the number of company records in the first split
companies table. I then repeat the process for the Sorts
tables and foreign key. Finally I append the contacts
from the second split table into the first split contacts
table - again hiding the id field.
Does that sound about right????
I apologise for the length of this message but thought it
wiser to explain exactly what happened and what I am
planning.
Any help from you MVPs would be fantastic!
Regards
Mary Ann