DDL-Adding Autonumber limit

[mcescher]

Hi All,

I'm trying to add a PK to a newly imported text file.

DoCmd.TransferText acImportFixed, strSpec, strFile, strPath & strFile
& ".txt", False
strSql = "ALTER TABLE " & strFile & " ADD ID COUNTER CONSTRAINT
PrimaryKey PRIMARY KEY"
CurrentDb.Execute strSql, dbFailOnError

When I run this code, it returns "System Resources Exceeded". If I
create a DD query with that SQL statement, I get this error.
"File sharing lock count exceeded. Increase MaxLocksPerFile registry
entry."

HKLM>Software>Microsoft>Jet>4.0>Engines>Jet 4.0>MaxLocksPerFile is set
to 9500

Table has just over 360k records.

Is there a way to force Access to go forward anyway? The user I'm
creating this database for doesn't have admin rights, and wouldn't be
able to change the registry setting.

HELP!!!

Chris M.

 

[Arvin Meyer [MVP]]

He'll need to get an admin to change it for him then. Most admins have no
clue about anything database related, so they'll either just do it, or
refuse. You deal with refusals by going to an executive and telling them
that they need to get work done.

 

['69 Camaro]

Hi, Chris.

I'm trying to add a PK to a newly imported text file. .. . .
Is there a way to force Access to go forward anyway?

Yes. You already have the import specification, so you know exactly what
the column names are, the data types, and the column sizes. Therefore,
import the text file into a pre-existing, empty table where you've already
added the ID column as the primary key. Each new record imported into the
existing table will have the ID column automatically incremented, so there's
no need to lock records while the Autonumber column and primary key
constraint are being added to the table later.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[mcescher]

Hi, Chris.




Yes. You already have the import specification, so you know exactly what
the column names are, the data types, and the column sizes. Therefore,
import the text file into a pre-existing, empty table where you've already
added the ID column as the primary key. Each new record imported into the
existing table will have the ID column automatically incremented, so there's
no need to lock records while the Autonumber column and primary key
constraint are being added to the table later.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:www.DataDevilDog.BlogSpot.com,www.D....QBuilt.com/html/expert_contributors2.htmlfor contact
info.

Sorry, I didn't say thank you sooner. This was a top notch solution.
Thanks!!!

Chris M.

 

['69 Camaro]

Sorry, I didn't say thank you sooner. This was a top notch solution.

Thanks!!!

Chris M.

You're very welcome! Glad it helped.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.