| Author |
Topic |
|
lkeeba
Starting Member
17 Posts |
 Posted - 2007-05-03 : 07:39:21
|
| When I access SQL2K using ODBC (linked table manager), I am not able to display any data. The columns show #Deleted as their data values. How do I display data in ACCESS from SQL2K tables? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-03 : 08:53:13
|
| Do you have proper permission on sql table? |
 |
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-04 : 02:19:09
|
| Thanks for the response. I think I have as I log on to the server using the owner of the database. I also tried to logon using sa. I tried Windows authentication but get the same result set. [#Deleted for all column values]. Any more assistance? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-04 : 09:24:35
|
| Did you specify sql user in odbc dsn? |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-07 : 06:04:10
|
| Thanks for the response. When creating the ODBC dsn, it requests whether you wish to login using Windows authentication or sql login. At first I created the dsn with Windows authentication and created another one using sql login. Both give the same values (#Deleted). Any assistance? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-07 : 12:48:35
|
| Can't reproduce it on my testing. What's mdac version on your machine? |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-09 : 02:18:34
|
| I should think I'm using MDAC2.8, this is the version that was on WXP. On Vista, I'm inable to verify the version. Thanks. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-05-09 : 03:23:42
|
| When you create your ODBC connection does the test at the end work?steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-09 : 11:23:08
|
| Yes. I am able to connect by supplying a username and password. When I use Import..., instead of of using Link Tables..., I get the desired results. Thanks. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-05-10 : 03:01:43
|
| I saw something on the MS site yesterday that suggested that an Access database with these symptomes may be corrupted. I don't have the link but I suggest you compact and then repair the Access database (having first made sure you have a backup)steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-10 : 08:53:40
|
| Does your SQL Server table have a primary key? That is a requirement for linking tables in Access, and overall of course it is a good database design requirement as well.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-11 : 02:11:20
|
| I compacted the database but still the resultset shows #Deleted values for the columns.Each table in the SQL database has a primary key.Thanks. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-05-11 : 03:11:16
|
| Create a new access database and try to link the table to that using a new ODBC connection. Purely out of interest how many records are there in the SQL table. Does anyone else have access to it?steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-14 : 10:51:15
|
| I have created a new Access database but the results are the same. I also recreated the dsn but to no avail.There are around 50,000 records in the table. |
|
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-05-14 : 10:57:23
|
| Does your table have any varchar/nvarchar fields greater than 255 characters? If so, then none of the field names in your table can have spaces. It took me forever to figure that one out.It has something to do with the ODBC conversion from varchar to an Access Memo data type. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-14 : 11:38:19
|
| Have you changed the column definitions at the source?Peter LarssonHelsingborg, Sweden |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-15 : 02:50:39
|
| Thanks for the help rendered. I have realised it is because of the PK (bigint) in the table I was trying to access. I tried to access a table with PK (varchar) and it worked out fine. Now how do I access the tables with PK (bigint) using dsn? Any ideas? |
|
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-05-15 : 12:29:17
|
| It doesn't look like you can:[url]http://support.microsoft.com/kb/321901[/url] |
|
|
|
lkeeba
Starting Member
17 Posts |
Posted - 2007-05-16 : 07:08:19
|
| End of story. Thanks a lot to all who assisted in one way or another. |
|
|
|
|
Accessing SQL2K using MS ACCESS