Access Database Select 1

[DaZZleD]

is there any way to select the MS Access DB at runtime? The best I could do was to change the DSN in ODBC and use that DSN. I'm hoping there would be a more elegant sollution to this. I'm currently using MDBs to transfer old databases from clients to SQL db's, but having to change the DSN for every transfer is pretty time consuming.

Thanks in advance.

 

[Karen99]

To create a combobox with a list of all the aliasses :

try
strAlias := TStringList.Create;
strAlias.clear;
Session.GetAliasNames(StrAlias);
cboAlias.Clear;
for I := 0 to StrAlias.Count - 1 do
cboAlias.Items.Add(StrAlias);
finally
StrAlias.Free;
end;

Then link a database component to the selected alias :

ClientDatabase.Close;
ClientDatabase.DatabaseName :=
cboAlias.items[cboAlias.itemindex];
ClientDatabase.Open;

Don't know if this is what you are looking for .....

 

[DaZZleD]

thanks but what I meant was that I need a method to allow the user to select the desired mdb with the Open Dialog, then modify an alias to point to that db. It works fine with Interbase (using the server_name property for the alias I create) and SQL (using the database_name property) but doesn't with Access. The only way I've come up with this far is to manually modify the DSN (from ODBC manager)to point to the desired MDB file, then restart the application. This isn't much of an interactivity or portability for the app, that's why I need this for.

Thanks,
Alex

 

[cmgaviao]

The info that is stored in the ODBC manager is stored/accessable from within the registry. You could simply make your modification to the ODBC DSN there. This would automate your process and allow you to continue without a restart of the application.

Look in HKEY_Local_Machine\Software\ODBC\ODBC.INI

for your existing DSN's I'll attach below a small code snippet (that I borrowed from someone else) for creating/modifying an Oracle DSN. But in relity, you need only to modify the settings you find in the registry key.

var
Reg: TRegistry;
begin
Reg := TRegistry.Create;
try
try
with Reg do
begin
RootKey := HKEY_CURRENT_USER;
if KeyExists('Software\ODBC\ODBC.INI\NEWDB') then
//presumably it is correct
else if not CreateKey('Software\ODBC\ODBC.INI\NEWDB') then
ShowMessage('Error in Creating ODBC DSN
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\NEWDB')
else if OpenKey('Software\ODBC\ODBC.INI\NEWDB',False) then
begin
WriteString('Driver','C:\ORAWIN95\ODBC250\sqo32_73.dll');
WriteString('AsyncSupport','Disabled');
WriteString('Server','TBSERVER:ORCL');
CloseKey;
if not OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True)
then
ShowMessage('Error in Creating ODBC DSN
Software\ODBC\ODBC.INI\ODBC Data Sources')
else
begin
WriteString('NEWDB','Oracle73 Ver 2.5');
CloseKey;
end;
end;
end;
except
on E: Exception do begin
SHowMessage(E.Message);
end;
end;
finally
Reg.Free;
end;
end;

 

[DaZZleD]

thanks cmgaviao. i customised this for my system (winXP) and the procedure looks something like this in its final form:

procedure TForm1.MSAccessDBSelect(currentdb : string);
var
Reg: TRegistry;
begin
Reg := TRegistry.Create;
try
try
with Reg do
begin
RootKey := HKEY_CURRENT_USER;
if KeyExists('Software\ODBC\ODBC.INI\MYACCESS') then
begin
WriteString('DBQ', currentdb);
end;
else if not CreateKey('Software\ODBC\ODBC.INI\MYACCESS') then
ShowMessage('Error in Creating ODBC DSN HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\MYACCESS')
else if OpenKey('Software\ODBC\ODBC.INI\MYACCESS',False) then
begin
WriteString('DBQ', currentdb);
WriteString('Driver','C:\WINDOWS\System32\odbcjt32.dll');
WriteInteger('DriverID','19');
WriteString('Fil','MS Access;');
WriteInteger('SafeTransactions','0');
WriteString('UID','');
CloseKey;
if not OpenKey('Software\ODBC\ODBC.INI\ODBC Data Sources',True) then
ShowMessage('Error in Creating ODBC DSN Software\ODBC\ODBC.INI\ODBC Data Sources')
else
begin
WriteString('MYACCESS','Microsoft Access Driver (*.mdb)');
CloseKey;
end;
end;
end;
except
on E: Exception do begin
SHowMessage(E.Message);
end;
end;
finally
Reg.Free;
end;
end;