MDB Becomes Read-Only??

[SEP800]

Hi! I'm having a problem doing an INSERT INTO using ADOQuery.ExecSQL.

The data gets inserted into a text file after I run the command. However, whenever I open the MDB, it tess me that it's in Read-Only mode and shows me a Save As button.

Can anyone tell me why that is? Maybe I'm doing something wrong?

Thanks!

 

[xwb]

Sounds like the transaction is still open. Check that nothing is still holding on to the file. If it is, then the next app can only open it as readonly.

 

[SEP800]

I'm preety sure nothing else is. Unless I'm doing it wrong.

BTW, I'm on Vista if that makes any difference...

This is the code I'm using:

procedure TDataModule1.ExtractSales;
var
ADOQuery1: TADOQuery;
DSFileName, DSName: String;
begin
if ConnectToDB then
begin
SetStatus(0, 4, 0, 'Connected to ' + StoreDB + '.', '');

DSFileName := FileNameDate + '.txt';

DSName := LeftStr(DSFileName, Length(DSFileName) - 4);

ADOQuery1 := TADOQuery.Create(Self);
ADOQuery1.Connection := ADOConnection1;
ADOQuery1.Active := False;

ADOQuery1.SQL.Add('SELECT * INTO ' + DSName + '#txt IN "' + StoreDBPath + '" ["TEXT;"] FROM SalesDetails WHERE ItemSalesDate = #01/01/2008#');

ADOQuery1.Prepared := True;

try
ADOQuery1.ExecSQL;
except
on E: EADOError do
begin
SetStatus(0, 8, 2, 'Error Number: ' + E.Message + '.', '');
end;
end;

ADOQuery1.Active := False;

ADOQuery1.SQL.Clear;
ADOQuery1.Close;
ADOQuery1.Free;
end;

ADOConnection1.Close;
end;

 

[xwb]

If you just start up the system and try to open the MDB without running your program first, what happens?

Options

1) It says it is readonly - the problem isn't your program if it is this
2) It opens

If it opens I don't really know what is wrong with it.

 

[SEP800]

Hi! Outside of Program Files, it opens normally. Within the Program Files directory, where it will be installed, it opens read-only. Is this a Vista thing? If it is, how can I make it open normally? Is there code that will allow me to qutomatically set my program to run as Administrator so I don't have to turn off UAC?

Thanks for your help!

 

[TonHu]

Hm, where have you been the last few years?
Vista finally imposes the full user-security available since Windows 2000 (and partially in Windows NT), and prohibits writing to (a.o.) Windows and Program Files directories. Only real administrators are allowed to write there. By default the account called 'administrator' or with permissions for the 'administrators' group is not a real administrator, unless the process is started 'elevated', or when UAC (User account control) is turned off. (search MSDN for the specifics)

Applications are supposed to store their data in the system-wide or user-specific 'Application Data' directories (actually, subdirectories thereof), depending on the application requirements. Only static (read-only) data should be stored in the Program Files dir, and be put there by the installer.

A common workaround is to install the application in the c:\users\<username>\Application Data\Local\<Applicationname> directory, but that should be corrected as soon as the application is made Windows User-Security Enabled (often confused with/monnickered as 'Vista capable'), as any user-started process (virus?) is allowed to write to any file(s) in that directory structure!

Only real solution is to improve your app to read/write to datafiles in the CSIDL_COMMON_APPDATA or CSIDL_APPDATA or CSIDL_LOCAL_APPDATA directory. (most of these CSIDL constants are listed here:

http://msdn.microsoft.com/en-us/library/bb762494.aspx)

HTH
TonHu

 

[SEP800]

Haha! Sorry TonHu! I haven't written anything in years. I'm trying to see if I can still have it, otherwise, I'll have to outsource this project.

Anyway, thanks for the update. I guess I'll have to catch up...

So anyway, I can't move the database because the default installation path is C:\Program Files. If I move it, a lot of things will start failing.

Are there any other workarounds?

Thanks!

 

[DjangMan]

I've seen other applications fail because they were installed in c:\program files under Vista. One thing you can try doing is separating your tables from the database. That way your Access database can stay where it is but the data will be in another folder that Windows may stay away from.

 

[SEP800]

DjangMan, I'd love to do that but I can't. I'm building over a pre-existing system. My program simply acts as a middleman between to applications which use the same database.

What my program does is back-up the database tables and extract data on a daily basis. Hence my predicament...

 

[DjangMan]

Right - but if the table data is moved out to another .mdb file and in your 'main' database you link to those tables - Access, and your application, will act like those tables were within the same database - no change to your code or any code within the Access database.

What may be happening is opportunistic locking is taking place and Vista is trying to help prevent the corruption that can occur with Op. Locks turned on.

I've been working with an Access database the last few weeks and I do recall coming across a post where the author mentioned how the database was becoming read-only. You might dig in Google and perhaps the Google Groups to see if you can find that issue.

Best of luck.

 

[SEP800]

DjangMan, I wish I could do that. However, the existing application was not created by me. It's a commercial package that was purchased by the previous person in charge. It lacks a lot of functionality that we require so I decided to just create an arbiter program. I cannot modify the database otherwise I may break the current system...