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?
11  8864  NeoPa 32,556
Expert Mod 16PB
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'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 32,556
Expert Mod 16PB
So it's not a simple SQL Server permissionning issue.
What about my second suggestion? Where did you get with that?
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?
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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).
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 32,556
Expert Mod 16PB
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.
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Roberto Ortolano |
last post by:
Hello
one of our servers (win2k adv server, IIS5, MDAC 2.8, full patched),
once a day in the afternoon starts giving this error on every site
that uses asp:
Microsoft OLE DB Provider for ODBC...
|
by: mark | r |
last post by:
Microsoft JET Database Engine error '80004005'
System resource exceeded.
/webutils/include_webstats_weeklycount.asp, line 23
Anyone know what this means and how to fix it.
Mark
|
by: Manuel Lopez |
last post by:
I get a run-time error 3035 ("system resources exceeded") running DAO code
in XP x64 that works fine under 32-bit XP. It updates 405,000 records.
Dim dbs as DAO.Database
If dbs=Nothing Then Set...
|
by: Brian Piotrowski |
last post by:
Hi All,
I have an SQL Server 2000 table that contains less than 2000 records. I
would like to select some of these records and group them. The query I
wrote runs fine in SQL Server's Query...
|
by: Peter Afonin |
last post by:
Hello,
I'm using this code to access a network share from an asp.net page:
Dim dir As DirectoryInfo = New DirectoryInfo("\\10.0.0.150\FormLib\")
Dim files() As FileInfo = dir.GetFiles("*.eps")...
|
by: Tim Marshall |
last post by:
Error 3035 is "System Resource Exceeded"
I'm throwing this error in a procedure that creates a pass through
query. The procedure has worked tried and true for many years, first in
A97 and now...
|
by: mattias192 |
last post by:
I cannot make sense of the ODBC error messages my VBA application
throws at me. I connect to an Access database of about 500MB in size.
First, there is the "Not enough space on temporary disk"....
|
by: chengsi |
last post by:
Hi guys,
I get the "System Resource Exceeded" error when I run a report, but the query which it is based on runs ok (take about 1.5 mins to run tho).
Anyone have any suggestions on how to avoid...
|
by: Rogiecrockett |
last post by:
I am new to this site. I have searched and found similar posts but don't think they are answering my question.
I have a basic and simple DB. 4,170,000 records each record being 6 fields. File...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
