[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Linked table problem between Access 2010 64bit and SQL server 2012

Posted on 2013-11-08
13
Medium Priority
?
1,105 Views
Last Modified: 2013-11-17
I have the same 30 tables on SQL Server 2012 and also on SQL Server 2000. I first setup an ODBC connection to the SQL 2000 and I can open all the tables in Access.
When I change the ODBC connection to the SQL 2012 server I an unable to open 4 of the tables.  The remaining 26 open fine.  When I attempt to open any of these 4 tables I get a popup window stating:
"The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data."

I even created a query and tried to pull just the top 100 records from that linked table and got the same error.  Even tried to query just one column (non-indexed) and got the same error.
Some of my tables have 900,000 records and they work fine.
Both Access 2010 and SQL Server 2012 are on the same Server 2008 R2 box.
0
Comment
Question by:djpierce54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 85
ID: 39635411
I'm curious if something is unique about those 4 tables - for example, do they have BLOB fields, or something of that nature?

Also, why use Access 64 bit? There's no real benefit, and it's definitely still "in the works".
0
 

Author Comment

by:djpierce54
ID: 39635763
There is nothing different about those four tables.
They all just have combinations of nvarchar, float and integer columns.
No memos or blobs.  I already had Excel 64bit so I had no choice but to install 64bit version.
Otherwise I have to uninstall both and install as 32bit.
0
 
LVL 9
ID: 39636265
Check your driver, either in your DSN or in your connection string if you are DSN-less.  If it was working before with SQL 2000, you may still be using the old {SQL Server} driver.  Instead, try {SQL Server Native Client 10.0} or {SQL Server Native Client 11.0} to see if that helps.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:djpierce54
ID: 39637137
I am using DSN connections and was just using the SQL Server standard driver. I created a new DSN using the Native Client 11.0 and I got the same results. I can change to the DSN pointing to my SQL server 2000 server and the table is fine.  That DSN connection also uses the same driver - SQL Standard driver as the sql server 2012 connection .
0
 
LVL 21
ID: 39637297
Have you tried deleting and recreating the linked tables with the DSN using the Native Client 11.0?
0
 

Author Comment

by:djpierce54
ID: 39637692
Yes I have tried that with same results.  I tried with both the V11 and the std SQL server driver - same problem. What I have found is that these tables have two timestamp fields in each table.  Apparently when you copy the table from a 2000 Server to a 2012 SQL server it will not allow two timestamps in the same table. I wonder if that rule apples in the ODBC driver?  You would think the ODBC error message would explain this more clearly.  I do not know.  I know when I put the table on the 2012 server with only one timestamp field then the linked table in Access 2010 works just fine.
Not sure if that is the answer but I guess it is a workaround. As long as you do not need both Timestamps on the copied table.  Probably a workaround somewhere out there but I can live with this restriction.
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 600 total points
ID: 39639039
Since each Timestamp is a meaningless unique value, I can't think of any reason to have two of them.  And the reason that ODBC cares is that it uses the Timestamp field (if present) to check for concurrency, so I imagine that having two of them would confuse it.
0
 

Author Comment

by:djpierce54
ID: 39639102
Well it was a view in the original SQL 2000 database and this view combined two tables, each had it's own timestamp field so the view included both.
I need to determine if either timestamp field is needed in the new SQL 2012 database view as the interface software that connects to the SQL 2000 DB is not going to be used in the SQL 2012 interface.
0
 
LVL 9
ID: 39639152
If you would like a quick overview of whether to use Timestamp (now RowVersion), check out my Best of Both Worlds slide deck at JStreettech.com/downloads.
0
 

Author Comment

by:djpierce54
ID: 39655589
I've requested that this question be deleted for the following reason:

Resolved the problem on my own
0
 
LVL 21
ID: 39655108
djpierce54,

Would you mind sharing your solution so other may benefit?

Thank  you for supporting Experts Exchange by posting your solution.
0
 

Author Comment

by:djpierce54
ID: 39655241
The solution was that the SQL 2000 view was composed of two tables and each table had it's own timestamp column.  For some reason the view was created with both timestamps.
It was determined that you cannot have two timestamps in a table or view in SQL Server 2012 as I was unable to copy that view into a table on the sql 2012.  I removed one of the timestamps from the view definition and then I was able to copy to a table on sql 2012.  It appears that Access 2010 follows the same rules.  I just had to change the view to only accept one of the timestamps and Access had no problem reading the data.  When the view had both timestamps then Access was unable to read the data and put up the error message = "The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data."
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1400 total points
ID: 39655353
Thanks for sharing your solution. I am sure others will benefit.

Access Make Table queries have a similar issue with multiple autonumber columns.

Takaway:
A table can  have only one RowVersion (timestamp) column. If you save a view as a table the resulting table can only have a single RowVersion (timestamp) column.

Solution:
I have had a similar issue with Access and a make table query pulling data from several tables. If you include two or more   autonumber data type columns you will get an error.  The way I have got around this in Access is to use the VAL() function on the additional autonumber columns. This will change to the data type to Long for the additional autonumber columns. This will allow the table to be created since there is only one autonumber data type columns.

If you really need all the data for the multiple RowVersion columns you could convert the additional ones to Binary(8) before saving the table. This would allow the new table to be created because it only has a single column of type RowVersion.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question