External access to a locked Access database

[ksarkies]

Hi

This is a "problem" I've been battling for many days and haven't been able to understand; various postings I've come across on the Internet seem to not quite provide the answer.

We have a commercial (membership database) application written around Access 2002 and we are using Access 2003 to run it on Server 2003 (64 bit), and 32 bit XP Pro. The database file is locked in some way so that it can't be opened within Access, even when using Shift key and F11 - it shows only an About message and a File/Exit menu. However we can create a new database file, link to the tables, and do anything we want except change the structure. This is sufficient for us to extend the application to fit our needs provided we work withing Access.

We are under pressure to break out of the limitations of Access and we would like to rewrite the application extension using more flexible languages and tools. However when we try to connect to this database using ODBC we get messages saying that we don't have read permission. Using OpenOffice.org Base as a test utility, we can open the file directly, connect through ODBC, and connect through ADO using Jet 4 OLE DB and ODBC OLE DB. We've tried this on the original file and also on one with linked tables. The table names are all shown but always the following error occurs when an SQL read is attempted:

SQL Status: 3112
Error code: -2147217911

(not sure if this is specific to OpenOffice).

Does anyone have an idea why we can't open the tables externally, and is there a way around this? I don't have a great deal of database experience apart from a bit of macro and VB coding in Access, so some guidance would be really appreciated.

cheers, Ken Sarkies
Holy Trinity Adelaide

[missinglinq]

As to the first portion of your question, many commercial Access apps are converted from the .mdb type file to the .mde type file,making them behave in just the manner you've described. That's because most the developers of commercial databases (and by that I mean "off-the-rack" software, not custom written for your company) don't want you messing with the structure! The chances are that haven't paid for that priviledge! If you check your EULA and/or documentation, you'll probably see this stated somewhere. You've paid to use the database, you haven't bought it! The data is your property, of course, and you can access it, as you've found out! You could even have someone write you a new Access database, utilizing the data.

Theway to check this isto go into Windows Explorer and check the extension.

Welcome to TheScripts!

Linq ;0)>

[ksarkies]

Thanks for the reply Linq. The database has the extension mdb. We are aware that the authors don't want us to mess with the structure or code, and we are not really interested in doing that - changing the structure would kill any chances of being able to update the software (this is only the database file - the code file is separate). We simply need to be able to make use of the stored data to extend the application to our particular needs. The authors in fact encourage this as they have only provided a core functionality. Nevertheless we have gotten to the point of complexity where we either rebuild what we have done in VB within Access, or break out to freedom.

cheers, Ken

[ksarkies]

Actually I wonder if they have just changed the extension from MDE to MDB? I note that creating an MDE separates out the code, which appears to be what is done with this application.

Anyway the issue still is accessing the database through ODBC or ADO - is this not possible if it were an MDE?

Ken