3035 - System resource exceeded in Windows 2000

[rharding]

I am getting a "3035 - System resource exceeded" error message when running a DoCmd.TransferT ext 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?

[NeoPa]

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.

[rharding]

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?

[NeoPa]

So it's not a simple SQL Server permissionning issue.
What about my second suggestion? Where did you get with that?

[rharding]

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 uniqueidentifie r (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?

[NeoPa]

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.

[rharding]

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?

[NeoPa]

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).

[rharding]

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.

[NeoPa]

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.