Access 2013 Linked table not showing all fields

Have progress backend database using ODBC 10 Driver.  After a recent upgrade, when relinking the table in Access 2013 I am not seeing any of the new fields that were added to the schema.  I verified there are only 241 fields total in the database and cannot figure out why it is not showing.  

When i pull using same ODBC driver using Crystal report or SQL query I can see the field but not in access 2013.

Any ideas?
kelsanitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Quick guess, ...
Is that the most recent ODBC driver?
0
kelsanitAuthor Commented:
sorry - it is 11.1
not sure if latest but is what is required as part of our ERP
0
Jeffrey CoachmanMIS LiasonCommented:
To be sure, did you try deleting all the links, ...then re-linking them?

Lets wait for another expert then, who may know more about progress ...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gustav BrockCIOCommented:
What if you create a new empty database and attach the table?

If success, then delete the table link from your application and either recreate it or import it from you test database.

/gustav
0
kelsanitAuthor Commented:
i have tried both of those already and same results.
0
Jeffrey CoachmanMIS LiasonCommented:
1. Are the new fields any of the "Access Only" fields?
Look Up
Multivalue
Hyperlink
OLE
Attachment
...these may not translate to any other RDBMS applications...

2. Are the field names valid?
No Spaces
No Special characters

3. Lets be clear:
I verified there are only 241 fields total in the database
...only 241 fields in the "Table", ...or in the entire "Database" (for all tables total)
I have never had o link to a table with more than 60 or so fields, ...so I am not sure if there is s field count limit somewhere...

Lets see what other comments come up with...

JeffCoachman
0
kelsanitAuthor Commented:
they are not access only fields  - as mentioned earlier, I can update and read from other applications such as SQL query or crystal reports.

There are 241 total fields in that one linked table.  Reason I brought that up is becuase i read somewhere that access is limited to only pull 255 total fields.
0
Gustav BrockCIOCommented:
What if you create a new test table, add an Id and fields to this identical to those missing, and link this table. Can you see this?

What if you create a view on the server with some of the offending fields - can you link to this?

And just to rule out the obvious - which is so stupid that I once struggled with that myself:
You don't by any chance have two instances of the database running where you modifiy the one while linking to the other?

/gustav
0
kelsanitAuthor Commented:
i need to pull the entire linked table as i am doing updates to the raw data.  There is on duplicate database on only one instance
0
Gustav BrockCIOCommented:
> i need to pull the entire linked table as i am doing updates to the raw data.

I understand that. But if you won't try those options that possibly could lead to track down or just indicate the cause of error, you leave us with nothing but guessing.

/gustav
0
kelsanitAuthor Commented:
testing link view now
0
kelsanitAuthor Commented:
cannot create view on server - is there anythign else you think i can try
0
Gustav BrockCIOCommented:
Nope. Out of ideas, sorry, other than asking the dba to do this, but that you probably already have considered.
This is very weird.

/gustav
0
Dale FyeOwner, Developing Solutions LLCCommented:
I've never used Progress SQL before, but in SQL Server, if you do not refresh the table definition, Access will not be able to see the fields.  And of course you have to refresh the link in Access:

Currentdb.Tabledefs("LinkedTableName").Refreshlinks

Additionally, if you have added a field of a datatype that Access doesn't recognize, you wont be able to see those either.  a Good example of that would be BigInt.
0
BitsqueezerCommented:
Hi,

@Dale: I don't know what you mean with "refresh the table definition" in SQL Server? After you have saved the table definition you can immediately relink the table in Access (using ACCDB/MDB) with the new definition.

@eenookami: Are you sure that you're working with the right driver version regarding 32/64 bit?
And, what Dale said above, the datatypes must be supported, other example (of SQL Server) is "date" where Access (depending on version) often only accepts "smalldatetime".
The problem is: You're working with DAO in Access and that means that any field of the DB server will be mapped to a datatype of JET/ACE database engine where DAO guesses that's the right one (and not seldom the result is wrong).

Try to create a pass-through-query in Access which opens all fields of the DB server: In this case you're working with the SQL of the DB server and you can explicitly SELECT some of the fields which are not in your linked table to see if that works. Please list the names and the datatypes on the DB server which are not in your linked table anymore.

You can also try to create an ADO recordset with VBA and see if you get all fields, if yes, you can assign that recordset to a form which fits (if you need it in a form) or work with the recordset data directly.

Next try could be to create a DAO recordset with VBA which uses a SELECT to the fields of the DB server table directly using a connection string in the SELECT like described here:
https://www.linkedin.com/groups/alternative-passthrough-queries-retrieving-records-4843954.S.5969186550011953156

In this case DAO opens the table directly using the connection string without a linked table so maybe this works for you. If you create a recordset from it you can stop the code and look into the VBA "Locals" window to look into the fields collection of the recordset where you can see which datatype it returned or if there are any errors displayed.

The real question is why you need 241 (!) fields... if this is not the result of a pivot query but a real table I would strongly consider to check the table design...

Cheers,

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill BachPresident and Btrieve GuruCommented:
I concur with Bitsqueezer:  Try an ODBC Pass-through query.  Sometimes, the JET engine gets hung up on processing certain fields, and utilizing a pass-through query can be the solution.
0
PatHartmanCommented:
Try deleting the link, compacting the database, and creating a new link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.