By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

3035 - System resource exceeded in Windows 2000

P: 11
I am getting a "3035 - System resource exceeded" error message when running a DoCmd.TransferText to load a .csv file into a SQL linked table using Access 2003 on Windows 2000. The .csv file contains 34804 rows and is 30 columns wide (19,086 KB).

I have changed the registry setting of MaxLocksPerFile to 1,500,000 but this does not help. I have seen another discussion in thescripts concerning this but the questioner appears to be talking about Windows XP and does not completely answers his/her own discussion.

What do I need to do to resolve this problem?
Jan 10 '07 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I'm not sure how helpful we can be here I'm afraid.
One thing I'd suggest to do first is to try it with a very much smaller .CSV file. This will tell you if the problem is resource related at all, or whether it's an issue with SQL Server (permissions or something).
My next suggestion would be to try to import it into a local Access table (as much of the data as will fit at least if it's too large). This may highlight a problem within the data itself which can often result in obtuse error messages from Access.
Jan 10 '07 #2

P: 11
I'm not sure how helpful we can be here I'm afraid.
One thing I'd suggest to do first is to try it with a very much smaller .CSV file. This will tell you if the problem is resource related at all, or whether it's an issue with SQL Server (permissions or something).
My next suggestion would be to try to import it into a local Access table (as much of the data as will fit at least if it's too large). This may highlight a problem within the data itself which can often result in obtuse error messages from Access.
I limited my import to 300 rows, 10,000 rows, and 20,000 rows with successful results. At 25,000 rows it failed with the 3035 error message. Memory usage on the PC was not affected, nor was anything else I looked at.

Does anyone have a suggestion?
Jan 10 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
So it's not a simple SQL Server permissionning issue.
What about my second suggestion? Where did you get with that?
Jan 10 '07 #4

P: 11
So it's not a simple SQL Server permissionning issue.
What about my second suggestion? Where did you get with that?
I tried your second suggestion and the entire csv file gets imported. It is only when I attempt to use the linked SQL table that I have a problem. I think the problem has something to do with the data types as specified on the SQL table and the data types as specified in the import specification.

The SQL data dictionary (from the vendor) specifies some fields as varchar (770 and 3000), some fields as tinyint (1), and some fields as uniqueidentifier (16). When the SQL table is linked the Access definition for these fields is memo, number (byte), and text (16) respectively. I think the problem has something to do with communicating between Access's definition and SQL definition.

I have played around with changing the SQL data type and Access' input specification data type but have not been able to find the correct combination.

Any suggestions?
Jan 10 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
This does at least tell us that it's a SQL issue rather than an Access one.You could try posting a link to this thread (keep this here as it might pick up more responses) in the SQL Server forum.
Jan 10 '07 #6

P: 11
This does at least tell us that it's a SQL issue rather than an Access one.You could try posting a link to this thread (keep this here as it might pick up more responses) in the SQL Server forum.
Can you tell me how to post a link to this thread?
Jan 10 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
When in the 'create post' window (or Reply window - same really) there is a button like a globe (Insert Hyperlink).
Click on this and enter the text to be displayed, then the http:// link to this thread (Copy the text from the address bar of your browser for this bit).
Jan 11 '07 #8

P: 11
When in the 'create post' window (or Reply window - same really) there is a button like a globe (Insert Hyperlink).
Click on this and enter the text to be displayed, then the http:// link to this thread (Copy the text from the address bar of your browser for this bit).
I have been able to solve my problem by using TransferText to import the csv file to a new Access table. I then use the Upsizing Wizard to create the SQL table. Now Access can communicate with SQL using terminology (Data Types) that Access understands.

In general the only problem with this happens to be with certain data types that Access creates which are different than the data types the SQL data dictionary is calling for. For example, Access creates certain text fields as data type of Text with a length of 255. The Upsizing Wizard creates the SQL table with this field as nvarchar with a length of 255. The SQL data dictionary calls for this fields to be varchar with a length of 770. If I change the SQL table to match the data dictionary and then reattach (link) the table to Access the field, in Access, is defined as Memo.

The shorter length of the SQL field is not a problem right now, but could be in the future. I am still playing with everything to see if there is some happy median.
Jan 11 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
You may still find some help for this in the SQL forum.
One thing I know about SQL Server (not much detailed experience I'm afraid) is that there are many powerful SQL statements for modifying definitions of tables and fields etc.
This could be done before linking to the remote SQL table so that all the field definitions are Access compatible before linking. This should get you past your current issues.
Jan 12 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I have been able to solve my problem by using TransferText to import the csv file to a new Access table. I then use the Upsizing Wizard to create the SQL table. Now Access can communicate with SQL using terminology (Data Types) that Access understands.

In general the only problem with this happens to be with certain data types that Access creates which are different than the data types the SQL data dictionary is calling for. For example, Access creates certain text fields as data type of Text with a length of 255. The Upsizing Wizard creates the SQL table with this field as nvarchar with a length of 255. The SQL data dictionary calls for this fields to be varchar with a length of 770. If I change the SQL table to match the data dictionary and then reattach (link) the table to Access the field, in Access, is defined as Memo.

The shorter length of the SQL field is not a problem right now, but could be in the future. I am still playing with everything to see if there is some happy median.
Access has a text field limit of 255. If you expect data in the future could exceed that limit then the Memo datatype is the only other option in Access.

Mary
Jan 12 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
One other option is to use VBA to create an odbc connection to the SQL Server database and use an imput file to read in the data line by line and run an insert query directly into the SQL Server database.

This would be fairly complex code but we could give you someplace to start and help as you develop it.

Mary
Jan 12 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.