2

Where can I obtain high quality information about ODBC & Access Databases?

I have worked on various projects over the years using ODBC from MFC/C++/Java.

Currently, I have a project in C++/MFC that uses a customized MFC layer to interface dynamically to an ODBC DSN connected to an Access 2000 dbm file.

This generally works.

However, I commonly get warnings such as:

DBMS: ACCESS Version: 04.00.0000 ODBC Driver Manager Version: 03.80.0000 Optional feature not implemented State:S1C00,Native:106,Origin:[Microsoft][ODBC Microsoft Access Driver]

Warning: Driver does not support requested concurrency. Optional feature not implemented State:S1C00,Native:106,Origin:[Microsoft][ODBC Microsoft Access Driver]

and now, I get the following when our software attempts to create a view:

General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for pr ocess 0x18fc Thread 0xc0 DBC 0x8c4cfc Jet'. State:01000,Native:1,Origin:[Microsoft][ODBC Microsoft Access Driver]

DBMS: ACCESS Version: 04.00.0000 ODBC Driver Manager Version: 03.80.0000 Executing SQL: create view SPEC as select CALNAME as TEST, CARDNUMBER, CARTONSTYLE as CARTON, CCAL as FLUTE, CORRDIR, CUSTOMERID, DESCRIPTION as DESCRIPTIO, DESIGNER, DESIGNNUM, JOINTSIZE, JOINTTYPE, PANEL_D as DEPTH, PANEL_L as LENGTH, PANEL_W as WIDTH, RULRULX as DSIZEX, RULRULY as DSIZEY, SHEETUX as SIZEX, SHEETUY as SIZEY, SHEETX as GROSSX, SHEETY as GROSSY from "Designs" where DESIGNNUM is not null
Warning: ODBC Success With Info, Driver's SQLSetConnectAttr failed State:IM006,Native:0,Origin:[Microsoft][ODBC Driver Manager]

General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for pr ocess 0x18fc Thread 0xc0 DBC 0x8c4cfc Jet'. State:01000,Native:1,Origin:[Microsoft][ODBC Microsoft Access Driver]

For the first part, regarding warnings concerning optional feature not implemented, and concurrency state not implemented (perhaps the same underlying issue), I have no idea what concurrency feature my software is even asking for? What concurrency states are appropriate? How does one ask for the "correct" one? Is there a table for versions of Windows vs. appropriate concurrency state to request?

For the second part, regarding the specific failure to create a view due to failure to open a registry key: huh?! Who is failing to open what registry key?

I have found some information concerning the need to give IUSR full permissions on the "windows NT temp folder". This sounds like a horrible issue for a desktop application to need to concern itself with. How should a desktop application, using an ODBC DSN ever need to concern itself with user permissions when manipulating a database file which exists in a user read/write-able path? What more permissions are necessary to allow the user to execute this SQL? And since the user is already able to add and delete columns and rows in the existing tables in the database, what more needs to be done?

This code used to work under XP. So I have to assume that Vista and/or Win 7 added some security that now blocks this functionality.

I am interested in:

  1. Canonical reference material from Microsoft that actually documents their various ODBC Access interfaces, options, versions, host-issues, etc.
  2. Specific information regarding the warnings regarding concurrency.
  3. Specific information regarding the apparent permissions failures when trying to create a view in the access 2000 dbm.
  4. Suggestions for modernizing the above, sans a complete overhaul or technology replacement. i.e. I would happily update the Access file to 2003 or 2007 or whatever, or possibly switch the back-end to SQL Server Lite, but I cannot justify changing from ODBC to some replacement middleware (it's just not worth the time & risk).

Thanks for any ideas or help you may have to offer.

Mordachai
  • 9,412
  • 6
  • 60
  • 112
  • I don't have an answer to the original question, but as for point #4. I've done several conversions from Access to SQL Server. The transition is fairly painless as the overall database schema will transfer will only very minor modifications. Most of the application SQL easily migrated to Stored Procedures as well. – Gerald P. Wright Jan 02 '12 at 17:51
  • Thanks. I do believe that this should be fairly straight forward. The biggest issues are to integrate the SQL Server lite with our installer, double check any legal issues, and implement whatever syntactical changes are required within our app's generated SQL. It's not trivial, and it would be easier to justify if I knew beyond any doubt that all of the above issues would be solved with such a change. Finally, would need a migration mechanic for existing customers with Access databases currently in use, which complicates things significantly. Sigh. :P – Mordachai Jan 02 '12 at 18:38
  • related: https://stackoverflow.com/q/26244425/2144390 – Gord Thompson May 19 '22 at 20:33

2 Answers2

2

we have an application that dynamically make use of ODBC access databases.

  1. Microsoft has good documentation about odbc http://msdn.microsoft.com/en-us/library/s9ds2ktb(v=vs.80).aspx
    You should also take a look at the CRecordset and CDatabase documentation http://msdn.microsoft.com/en-us/library/1hkkwdf0(v=vs.80).aspx
    http://msdn.microsoft.com/en-us/library/2dhc1abk(v=VS.80).aspx

  2. For the concurrency issue, it often occurs when you open a CRecordset with the wrong options. Where I work, we usually open recordsets with the following options

RecordsetToUse.Open(CRecordset::dynaset, "SQL Statement or NULL to use the DefaultSQL", CRecordset::skipDeletedRecords);

If using the recordset for fast readonly access, you can also use

RecordsetToUse.Open(CRecordset::forwardOnly, "SQL Statement or NULL to use the DefaultSQL", CRecordset::readOnly);

3 . For the Create View problem, It seems that ODBC doesn't support the creation of views... I think, on the other hand, that you could use ADODB (http://www.codeproject.com/KB/database/adoclasses.aspx) to do that

Andrew Truckle
  • 17,769
  • 16
  • 66
  • 164
Goldorak84
  • 3,714
  • 3
  • 38
  • 62
  • Yes, I think that the issue has nothing to do with ODBC/Jet despite my earlier concerns, and I now think it's an MFC issue (and possibly directly related to the options we're using to open the database/recordset). Thanks for the above, I'll see what we're doing by comparison. Our code is quite old, probably has some old MFC junk from VC++ 6.0 or something! – Mordachai Jan 03 '12 at 22:19
  • Our software uses dynaset, which is the only one that appears to work correctly. It might be possible to explicitly use forwardOnly when doing read-onoly lookups, but isn't worth the effort for our purposes. All other modes outright fail or end up with a read-only recordset. The warnings appear as part of MFC's iterative approach to figuring out what concurrency it should use with the current driver. It tries one, warns that it failed, and tries another, until it succeeds or exhausts the possibilities. That's where concurrency warnings come from. – Mordachai Jan 04 '12 at 16:02
  • I am not at all clear as to where the Jet complaint regarding registry failure comes from... perhaps the driver itself. Seems likely. But it doesn't seem to interfere... just makes it look like there is an issue when really there's not. This whole thing has consumed many hours to determine that it is functioning fine, and all messages appear to be ordinary. Yuck. :( – Mordachai Jan 04 '12 at 16:04
  • Does the registry error occur on other computers, or just your development machine? – Goldorak84 Jan 04 '12 at 18:31
1

Yeah, the migration of remote clients can be a pain. Part of the reason why I absolutely try to avoid Access at all costs. I've done this one of two ways.

  1. Use WebEx (or some other online meeting program) to grab control of the remote machine to insure the migration goes as needed.

  2. Build into the new code a check to see if database has been migrated.

If the new DB (SQL Server) shows as not found, then the app fires off the migration tool. However, this means building and perfecting a migration utility and scripts to automate the migration as nay number of things can go wrong.

One thing I did start doing a while ago is building an app compatibility table into the DB. That way the code can check to make sure the DB schema version matches. I also include another table (encrypted) with all the necessary SQL to upgrade (migrate) the DB to the new version. That way, the app can determine that the DB needs to be migrated and can run through the scripts to do it.

I really hope this helps.

Andrew Truckle
  • 17,769
  • 16
  • 66
  • 164
Gerald P. Wright
  • 796
  • 4
  • 9
  • I appreciate the feedback and ideas. I'm still on the fence. I don't have any real time allocated in this build-cycle's budget for a migration. This may have to be a paid option... – Mordachai Jan 03 '12 at 14:20