UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> System Resource Exceeded, Access 2010    
 
   
mrpersonality
post May 10 2019, 09:44 PM
Post#1



Posts: 465
Joined: 4-December 02
From: NSW Australia


I am trying to get a backend database ready to migrate to SQL
I have a routine that runs through every table design,checks each field data type for Autonumber,and if its an autonumber data type ,change it to integer
Theres about 40 tables,and I get this error on just one table,the other 39 work just great
I have tried working on this one table - still get the error
if I open the db and go into the design view and change it there - no problem

"error 3035 System Resource Exceeded"
heres the code I am using
tlbname and fname are supplied to the sub
Dim dbBackend As Database
Dim strBackend As String
Dim strDDL As String
Dim Wrk As DAO.Workspace

strBackend = "blah blah"
strDDL = "ALTER TABLE [" & Tlbname & "] ALTER COLUMN " & Fname & " Integer"


Set Wrk = DBEngine.Workspaces(0)
Set dbBackend = Wrk.OpenDatabase(strBackend)

dbBackend.Execute strDDL, dbFailOnError

dbBackend.Close
Set dbBackend = Nothing

anybody have any idea what I should look for in this one particular table.
while I can manually do this,i am trying to automate the process,so testing becomes a lot less tedious
Go to the top of the page
 
theDBguy
post May 10 2019, 10:06 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,598
Joined: 19-June 07
From: SunnySandyEggo


Hi. Is it always the same table? Is it the last one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mrpersonality
post May 10 2019, 10:37 PM
Post#3



Posts: 465
Joined: 4-December 02
From: NSW Australia


always the same one,and no,its in the middle of the loop-which sorts from a > z table names
I have tried just working on this one table - but to no avail
I put a select case in the loop of the table names to let the code finish

If .Attributes And dbAutoIncrField Then
If InStr(1, tdfLoop.Name, "MSys") = 0 Then
Debug.Print tdfLoop.Name & "," & .Name

Select Case tbname


Case "Invoice_Items"
'Call Change_Autonumber_To_Integer(tdfLoop.Name, Field_Name.Name) this gives the error message
Call Change_Autonumber_To_Integer_Required(tdfLoop.Name, Field_Name.Name)
Case Else

Call Change_Autonumber_To_Integer(tdfLoop.Name, Field_Name.Name)
Call Change_Autonumber_To_Integer_Required(tdfLoop.Name, Field_Name.Name)
End Select

End If
End If
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 01:24 AM
Post#4



Posts: 284
Joined: 2-April 18



Could you please post the whole (significant) code?
I would like to see the real sequence of loop through tables and fields. It seems like you first check field attributes and then, only if the table is not a system table, you call the function/sub. Maybe the sequence can be do better... and this could solve something.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Bullschmidt
post May 11 2019, 01:28 AM
Post#5



Posts: 12
Joined: 8-May 19



QUOTE
"error 3035 System Resource Exceeded"
...
anybody have any idea what I should look for in this one particular table.


I'm going to guess that this table is much larger in structure and/or rows of data than the other tables...
Go to the top of the page
 
mrpersonality
post May 11 2019, 01:43 AM
Post#6



Posts: 465
Joined: 4-December 02
From: NSW Australia


it has 265,000 records
it has 24 fields
data types 1 autonumber,12 long,6 text,5 currency
Sub RunThis()


Dim dbs As DAO.Database
Dim Dbname As String
Dim tbname As String
Dim tdfLoop As TableDef
Dim Field_Name As Field
Dim tdf As TableDef
Dim Wrk As DAO.Workspace
Dbname = "C:\Server.accde"
Set Wrk = DBEngine.Workspaces(0)
Set dbs = Wrk.OpenDatabase(Dbname)

With dbs

For Each tdfLoop In .TableDefs
tbname = tdfLoop.Name

For Each Field_Name In tdfLoop.Fields

With Field_Name

If .Attributes And dbAutoIncrField Then
If InStr(1, tdfLoop.Name, "MSys") = 0 Then ' don't touch system tables
'Debug.Print tdfLoop.Name & "," & .Name

Select Case tbname


Case "Invoice_Items"
'Call Change_Autonumber_To_Integer(tdfLoop.Name, Field_Name.Name)
Call Change_Autonumber_To_Integer_Required(tdfLoop.Name, Field_Name.Name)
Case Else

Call Change_Autonumber_To_Integer(tdfLoop.Name, Field_Name.Name)
Call Change_Autonumber_To_Integer_Required(tdfLoop.Name, Field_Name.Name)
End Select

End If
End If




End With

Next






Next

End With




End Sub
Sub Change_Autonumber_To_Integer(Tlbname As String, Fname As String)


Dim dbBackend As Database
Dim strBackend As String
Dim strDDL As String
Dim Wrk As DAO.Workspace

strBackend = "C:\Server.accde"
strDDL = "ALTER TABLE [" & Tlbname & "] ALTER COLUMN " & Fname & " integer"


Set Wrk = DBEngine.Workspaces(0)
Set dbBackend = Wrk.OpenDatabase(strBackend)

dbBackend.Execute strDDL, dbFailOnError


dbBackend.Close
Set dbBackend = Nothing



End Sub
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 02:41 AM
Post#7



Posts: 284
Joined: 2-April 18



Why do you have an accdE as BackEnd?

1) You continue opening and closing BE in Change_Autonumber_To_Integer Sub when you could use the database object you already have opened in the calling Sub
You can declare it as public at module level, or pass it as parameter to the Sub. Let's do the secondo way.

2) Let's first check table name, skipping system tables, checking fields for remaining tables.

CODE
Sub RunThis()
  
  Dim dbs As DAO.Database
  Dim Dbname As String
  Dim tbname As String
  Dim tdfLoop As TableDef
  Dim Field_Name As Field
  Dim tdf As TableDef
  Dim Wrk As DAO.Workspace
  
  Dbname = "C:\Server.accde"
  Set Wrk = DBEngine.Workspaces(0)
  Set dbs = Wrk.OpenDatabase(Dbname)
  
  With dbs
      For Each tdfLoop In .TableDefs
          If Left$(4, tdfLoop.Name) = "MSys" Then '<== new line
          tbname = tdfLoop.Name
  
          For Each Field_Name In tdfLoop.Fields
  
              With Field_Name
  
                  If .Attributes And dbAutoIncrField Then
                      'If InStr(1, tdfLoop.Name, "MSys") = 0 Then ' don't touch system tables 'commented line
                      'Debug.Print tdfLoop.Name & "," & .Name
                          Select Case tbname
                              Case "Invoice_Items"
                                  'Call Change_Autonumber_To_Integer(tbname, Field_Name.Name)
                                  Call Change_Autonumber_To_Integer_Required(tbname, Field_Name.Name, dbs) 'modified line
                              Case Else
                                  Call Change_Autonumber_To_Integer(tbname, Field_Name.Name, dbs) 'modified line
                                  Call Change_Autonumber_To_Integer_Required(tbname, Field_Name.Name) 'is it usefull to add dbs as parameter?
                          End Select
  
                      'End If 'commented line
                  End If
              End With
          
          End If 'New line
          Next
      Next
  
  End With
  dbs.Close
  Wrk.Close
  
  Set dbs = Nothing
  Set Wrk = Nothing
  
  End Sub
  
  Sub Change_Autonumber_To_Integer(Tlbname As String, Fname As String, dbBackend as DAO.Database)
  
'Dim dbBackend As Database 'commented line
'Dim strBackend As String 'commented line
Dim strDDL As String
'Dim Wrk As DAO.Workspace 'commented line

'strBackend = "C:\Server.accde" 'commented line
strDDL = "ALTER TABLE [" & Tlbname & "] ALTER COLUMN " & Fname & " integer"


'Set Wrk = DBEngine.Workspaces(0) 'commented line
'Set dbBackend = Wrk.OpenDatabase(strBackend) 'commented line

dbBackend.Execute strDDL, dbFailOnError


'dbBackend.Close 'commented line
'Set dbBackend = Nothing 'commented line
  
  End Sub


I don't know if we can do the same with Change_Autonumber_To_Integer_Required sub, you did not post.
I would suggest to use Transaction too.

Be carefull = this is "flying" code, not tested and not writtend using VBE, so there could be typing mistakes
This post has been edited by Phil_cattivocarattere: May 11 2019, 02:43 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mrpersonality
post May 11 2019, 02:50 AM
Post#8



Posts: 465
Joined: 4-December 02
From: NSW Australia


why accdE back end
would you believe employees started mucking around in the back end - they figured they would create some forms and reports because the data tables were there
so why not use them iconfused.gif
I will try your suggestion and post back
Go to the top of the page
 
mrpersonality
post May 11 2019, 03:04 AM
Post#9



Posts: 465
Joined: 4-December 02
From: NSW Australia


Hi Phil
No different, same error,you would think access could handle 265,000 records without much trouble
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 03:09 AM
Post#10



Posts: 284
Joined: 2-April 18



QUOTE
you would think access could handle 265,000 records without much trouble
265,000 are not so much!I would try another thing: create a new, clean, normal accdb file and import that table only. Then try on that new file.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mrpersonality
post May 11 2019, 03:18 AM
Post#11



Posts: 465
Joined: 4-December 02
From: NSW Australia


No go,same error ,fresh db containing only that 1 table
I may just have to do it manually-maybe use a message box at the end of the sub to remind me

also
If Left$(4, tdfLoop.Name) = "MSys" Then '<== new line raises error type mismatch
I just used If InStr(1, tdfLoop.Name, "MSys") = 0 Then
This post has been edited by mrpersonality: May 11 2019, 03:23 AM
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 03:32 AM
Post#12



Posts: 284
Joined: 2-April 18



QUOTE
If Left$(4, tdfLoop.Name) = "MSys" Then '<== new line raises error type mismatch
My fault (and bad memory). Correct syntax and logic is
CODE
If Left$(tdfLoop.Name, 4) <> "MSys" Then
Your code would skip a table named "tblthemsyst", very strange name but... I think "general". But the error is not there, for sure.
Let's try another way... let me think.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mrpersonality
post May 11 2019, 03:39 AM
Post#13



Posts: 465
Joined: 4-December 02
From: NSW Australia


I will use If Left$(tdfLoop.Name, 4) <> "MSys" Then ,also used dbs for the other sub that I did not post,thanks for the heads up
just lucky theres only 1 table giving me problems (at this stage)

one thing I did notice when I created the new blank db,that table is 60meg of the 225meg database (25% of the database size)
This post has been edited by mrpersonality: May 11 2019, 03:43 AM
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 05:03 AM
Post#14



Posts: 284
Joined: 2-April 18



QUOTE
I did notice when I created the new blank db,that table is 60meg of the 225meg database (25% of the database size)
Could you upload the file with the fautly table only? without sensitive data, it is obvious. Compact-repair and zip, as usual, so we can try to verify size behaviour and the main System Resource Exceeded error.Googling I found this How to use VBA to remove attribute dbAutoIncrField?
where an UtterAccess well-known user, ADezii, posted a function and some "alerts"For semplicity I copy-paste everything but this is not mine, it's ADezii's code
QUOTE
Simply Pass to this Function a Table Name and it will:1. Determine the Name of an AutoNumber Field, if one exists.
2. Convert that AutoNumber Field to a LONG INTEGER Data Type.
3. Function Definition:
CODE
Public Function fModifyFieldType(strTableName As String)
Dim strSql As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
  
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
  
For Each fld In tdf.Fields
   If (fld.Attributes And dbAutoIncrField) <> 0 Then     'Yep, an AutoNumber Field
     strSql = "ALTER TABLE " & strTableName & " ALTER COLUMN " & fld.Name & " LONG;"
       DBEngine(0)(0).Execute strSql, dbFailOnError
         Exit For
   End If
Next
  
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
4. Sample Function Call:
CODE
fModifyFieldType("Table1")

5. Critical Points to remember:
a. The Code WILL work if the AutoNumber Field is also the Primary Key, and NOT involved in any Relationships.
b. The Code WILL NOT work if the AutoNumber Field is also the Primary Key, and IS involved in any Relationship.
6. The Code has been tested, and is fully functional. Any other questions, feel free to ask.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Phil_cattivocara...
post May 11 2019, 07:32 AM
Post#15



Posts: 284
Joined: 2-April 18



Second self-quoting... it is a dangerous warning?In my previous post I referred to ADezii's function but... you do not need it! You are already doing everything with an "ALTER TABLE".The most important thing, instead, is that Microsoft has a solution for this
"System Resource Exceeded" error message when you perform a query in Access 2010I read A2013 may have the same trouble, but not A2016 (I do not know with Office365). If you can verify with another Access version it would be perfect, so you know this is the real solution.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
GroverParkGeorge
post May 11 2019, 08:01 AM
Post#16


UA Admin
Posts: 35,173
Joined: 20-June 02
From: Newcastle, WA


While I think you and Phil are on the right path to make this change work, I have to pause and wonder WHY you even need/want to do it?

How are you planning to do the migration? If you are using a tool like SSMA (Microsoft's SQL Server Migration Assistant) for Access, that tool recognizes and handles AutoNumbers properly, so it's a waste of time to convert them. In fact, to regain the same functionality, won't you have to restore Identity Specification on them once the migration to SQL Server is done?

Attached File  IdentitySpec.jpg ( 28.59K )Number of downloads: 3


Unless I'm totally forgetting how it usually works, I can't think of a situation where you'd need to do this to AutoNumbers. They are actually Long Integers anyway, and the equivalent of the Int datatype in SQL Server. Both have the "Identity Specification" property available. The main difference is that SQL Server allows you to modify an existing field back and forth, whereas an AutoNumber doesn't have that flexibility.

So, again, wondering what the extra work will accomplish. Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
gemmathehusky
post May 11 2019, 10:10 AM
Post#17


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


Is this a new PC?

There can be "resources exceeded" issues with multiple core processors.

Can't find the solution link at the moment.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
mrpersonality
post May 11 2019, 05:12 PM
Post#18



Posts: 465
Joined: 4-December 02
From: NSW Australia


Where to start
it seems its entirely the amount of records in the table-i emptied the table of its 265,00 rows,run the routine and no error
Phil- This is not code I will be running forever,once I have migrated to SQL it will no longer be required,so as long as it works that's my main objective
I am running a routine that is trying to automate a clean up of existing tables prior to migrating to SQL
my routine does this
1.copys the backend and dumps it into a test folder - now all work is carried out on the backend db that's in the test folder
2.loop through all the relationships and adds the relevant data to a local table to allow the relationships to be re-applied
3.all relationships are then deleted
4.then run the autonumber routine
5.i have a few tables that I rename-such as Job's to Jobnumbers,and some table names have spaces which I get rid of (when I first started access,i had more bad habits than I do now)
6.Some tables have become redundant - they have names such as TopSectionsOLD,the OLD being the marker for redundant,and I delete them
7.i then put the relationships back to the way they were

other than this one table this whole routine takes 20 seconds
i will need its speed when i am ready to do it on the actual production copy,as the changeover will be on a sunday when the office is closed,plus the fact i wont forget to do something
if i have to- i will do the one table manually,but of course would prefer not to

-----------------------------------------
George
I am totally new to SQL SERVER,so I certainly am open to guidance on that subject,as my existing posts in the SQL forum would suggest (and i am sure there will be more)
there is only 1 reason I am converting the autonumbers
I have quite a few places in my frontend that use the Autonumber field value-which is available as soon as you start to add a record
which is not the case in SQL SERVER ?
maybe I have some bad practices going on in my front end ? if so nows the time for me to fix them,as long as its not to complex,so suggestions are welcome
i realise my frontend will need to have changes to account for no autonumber,i was going to have a function to retrieve the last id,add 1 to it,then commit the record (not using DMAX + 1)
I intend to use Microsoft's SQL Server Migration Assistant for access on this backend
----------------------------------------
Gem
the computer is a couple of yrs old
----------------------------------------------
Just a bit of background
I am a carpenter by trade,picked up asbestos somewhere in my working life,and had to find an alternative job description
I was given an office job and did not know what a computer was supposed to do
after just a couple of weeks of finger typing,i had to find better ways of doing menial tasks,so I taught myself access (not sure why as excel seems to be the first go to for most people)
I am aware i don't have the perfect programming skills-but what I did worked
I am supposed to be retired.During my working life I had developed this application which has grown beyond its original purpose
you could say I should of just said no to every new request to add to this application- but that's history and cant be changed
this application has worked for over 10yrs with no problems,until Microsoft in their wisdom broke access fe to access be with a windows update
I get daily phone calls for help-which are becoming a PITA
I need to get this application to an Acess FE with SQL backend ASAP with minimal changes for the time being,to at least relieve the pressure I feel(deserved or not)
I realise the front end will need changes,but they can come over time-once the initial bedding down is done
which brings me to this point
I hope I haven't bored you thanks.gif










Go to the top of the page
 
isladogs
post May 12 2019, 12:52 AM
Post#19


UtterAccess VIP
Posts: 1,419
Joined: 4-June 18
From: Somerset, UK


Just to confirm George's point. There is no need to convert autonumber fields. SQL Server handles them perfectly using IDENTITY.

Does your problem table have any datatypes that SS cannot handle?

--------------------
Go to the top of the page
 
mrpersonality
post May 12 2019, 02:02 AM
Post#20



Posts: 465
Joined: 4-December 02
From: NSW Australia


as per my previous post's
it has 265,000 records
it has 24 fields
data types 1 autonumber,12 long,6 text,5 currency all SQL compatible as far as I know
its not the table-its the amount of records causing the problem,i tested it with 2000 rows and it did not produce the error
the other issue is not whether SQL handles autonumber - its how I have used it in the front end and the implications that raises with the SQL back end if left as identity
I am not inflexible as to what I need to do-as per my previous posts
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 08:59 AM