Solved

ADO connection

Posted on 2002-03-12
7
443 Views
Last Modified: 2013-11-23
I want to connect to an Access database at runtime using ADO. I would like to specify the path of the database, say in a text box and then try to connect to it. If the path specified is invalid,I want to show the message in a message dialog box instead of having the system display the error. What I am having is that if I specify a wrong database path, my application just hangs just after displaying the error. How can I trap the error and give the user a meaningfull message.
0
Comment
Question by:gi7mhl
7 Comments
 
LVL 1

Expert Comment

by:drnadeem
ID: 6856171
Here is a way you can run ADO connection dialog box the way delphi does.

Login.ConnectionString := adodb.PromptDataSource(Handle, '');

after this you can store the string in the registry so that subsequent connections access the registry and locate the connection string. This will avoid errors and the need to trap them.

hope this helps
0
 
LVL 1

Expert Comment

by:cobramania
ID: 6856927
why don't you try using opendialog instead of edit box ?

hope you got the idea :)
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 6859794
A variant of drnadeem

Create as much UDL-Files as needed (Design time).


From within your application call :
  ADOConnection1.ConnectionString := adodb.PromptDataLinkFile(Handle, 'X:\somedir\somefile.udl');


With this function you can open an existing UDL-File.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:mgesiot
ID: 6860003
Hi.
The point is if you must choose the position of the Access files once or different times.
If it's only once use UDL files.
they are simple to use (even your client can check them)
and they work.

But if you want to change different times the database to use then the simplest thing to do is :

1) An open file dialog box.
2) A Recent Open Files Menu (use a string list.. simple as that)
3) A RunTime built Connection String.

Numoer 3 could look a bit tricky, but all you have to change is the name of the file, so copy a pre-prepared string and change the nema of the file you recive from
the dialog.
Just remember to set the dialog to open only exsisting files and with the right extension and use a TRY...FINALLY block when opening in case the file is corrupted or is in the wrong format.

Hope it helps
Marco

0
 
LVL 44

Expert Comment

by:CrazyOne
ID: 6862294
Try varifying the file is there

if not FileExists(text1.text) then
  ShowMessage('Nope')
else
  OpenYourDataBase;
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6863085
For reasons stated below, proposed answer rejected.

Thank you
Computer101
Community Support Moderator


Comments
Comments are intended to be used as a collaboration tool. Many Experts choose to post their solutions as comments only.

Answers
An answer is a specific solution to a question and should be submitted if it will solve the questioner's problem and doesn't duplicate a previous comment.

Comment Vs. Answer
If you are unsure of your solution, post it as a comment. Members can accept comments as solutions and award you Expert Points for them.

For more tips on comments and answers, click here.

0
 
LVL 2

Accepted Solution

by:
gallaghe earned 100 total points
ID: 6864676
Here is another idea (beating the dog to death).

Create a constant for your connection string as shown here
const
  ConnectStr:WideString =
  'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Password="";' +
  'User ID=Admin;' +
  'Data Source=%s;' +
  'Mode=Share Deny None;' +
  'Extended Properties="";' +
  'Jet OLEDB:System database="";' +
  'Jet OLEDB:Registry Path="";' +
  'Jet OLEDB:Database Password="";' +
  'Jet OLEDB:Engine Type=4;' +
  'Jet OLEDB:Database Locking Mode=0;' +
  'Jet OLEDB:Global Partial Bulk Ops=2;' +
  'Jet OLEDB:Global Bulk Transactions=1;' +
  'Jet OLEDB:New Database Password="";' +
  'Jet OLEDB:Create System Database=False;' +
  'Jet OLEDB:Encrypt Database=False;' +
  'Jet OLEDB:Don''t Copy Locale on Compact=False;' +
  'Jet OLEDB:Compact Without Replica Repair=False;' +
  'Jet OLEDB:SFP=False' ;

notice that there is a '%s' for the "Data Source". You would replace that with the path + name of the database at runtime i.e.

ADOConnection1.ConnectionString :=
  Format(ConnectStr,[Edit1.Text]) ;

{ Interesting note on the following code, if you cancel
  operation in WillConnect event ADO still rears it's
  ugly error message. So to suppress it we mus wrap it
  in the Try/Except. In Visual Basic 6 ADO does not
  impose on you. I am to lazy to read the Delphi source
  to see why this is so, just assume there is a
  "Raise" error if we set the [EventStatus] }
try
  ADOConnection1.Connected := True ;
except
  { See OnWillConnect event }
  exit;
end ;

procedure TForm1.ADOConnection1WillConnect(....
begin
  if not FileExists(Edit1.Text) then
  begin
    ShowMessage('Failed to locate the file' + #13 +
      Edit1.Text + #13 + 'Try later') ;
    EventStatus := esCancel ;
  end ;
end;

Yep this could also be done w/o the WillConnect;

if FileExists(Edit1.Text) then
  { Do the connection }
else
  { Error message and recovery goes here }
...


To me it makes perfect sense to (if there are more then one database choices) programmically code methods to display only valid databases to select from. For instance as someone else mentioned an open dialog component or populating a ComboBox or ListBox with valid selections.

I would steer clear of using <PromptDataSource> for novice users, only use for seasoned PC users. Remember it is our job to make life easy for the users.


Hope this is of some use to you.
Kevin S. Gallagher
Project JEDI http://www.delphi-jedi.org/
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi 10 Seattle Dataset Actions 5 79
Delphi with SQL Natvie Client 15 87
Wininet read php file from internet issue 6 83
DBGrid or StringGrid ? 6 72
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now