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  8832  NeoPa 32,534
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,534
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,534
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,534
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,534
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: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |
