System Resource Exceeded

[Cangioli]

During my VB, I'm receiving a debug for "System Resource Exceeded". I use an
import spec to pull in a .txt file as a table, and then use;

CurrentDb.Execute "ALTER TABLE SFOEC ADD COLUMN ID COUNTER PRIMARY KEY"

to add the Primary Key as a counter. I'm receiving the error on this line.
The size of my database is only around 100 Mb, 80,000 records, and it has
been functional for a few weeks. It is currently on a network, but I also
attempted it locally with the same results. Any advise would be greatly
appreciated. Thanks.

 

[John W. Vinson]

During my VB, I'm receiving a debug for "System Resource Exceeded". I use an
import spec to pull in a .txt file as a table, and then use;

CurrentDb.Execute "ALTER TABLE SFOEC ADD COLUMN ID COUNTER PRIMARY KEY"

to add the Primary Key as a counter. I'm receiving the error on this line.
The size of my database is only around 100 Mb, 80,000 records, and it has
been functional for a few weeks. It is currently on a network, but I also
attempted it locally with the same results. Any advise would be greatly
appreciated. Thanks.

The problem is that when you alter a table's design, Access must keep both the
old table AND the new table in memory at the same time. Rather than importing
a 100-mbyte file and then trying to change its design, I'd suggest:

- create a table SFOEC with primary key set, and all the fields correctly
typed and sized
- *link* to the text file rather than importing it
- run an Append query to migrate the data from the linked table into the
permanent table
- Run a Delete query to empty the table when you're done with it, or before
you import again; compact after running the delete query

 

[a a r o n _ k e m p f]

Dude, if Jet isn't reliable enough for you; then you should move to
SQL Server.

SQL Server is reliable.

And Jet isnt' stable enough for a half dozen users or even 25mb of
data

 

[Cangioli]

Thanks for the intel. I can't get SQL (too much red tape), but I'll try that
other method. Thanks again.

 

[BruceM]

You should know that Aaron is a resident troll who has one answer for almost
every question. To be fair, five percent or so of his posts contain
potentially useful suggestions, but mostly he rails against Jet and promotes
SQL Server and Access Data Pages for every situation. He is incorrect about
Jet, which is stable for many users provided the database is split, and can
handle databases much larger than 25 mb if they are set up properly. A
large body of evidence runs contrary to many of his claims in this forum.

 

[GenlAccess]

And Jet isnt' stable enough for a half dozen
users or even 25mb of data

When most people show off, they are trying to show how smart they are.

This guy is different; he shows off how dumb he is.

He thinks he's a Big Shot, and he's only one letter off.

Genl Access