Remote databse access

Can someone please tell me how do I connect to a remote Access database on the server http://username.brinkster.net/db/database.mdb?

Please take a look at the sample with SQL Server:

http://entwickler-forum.de/archive/index.php/t-12227.html

But I need a sample with Access database.

Can someone help me?
cyberman05Asked:
Who is Participating?
 
OrcbighterCommented:
Here is the code to get the connection string and open a connection to the database.

connStr - contains connection string, either held in the Registry or in a config file. Retrieved at program start

procedure myfunction( tablename: string, connStr: string )
var
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;
  
begin
  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.LoginPrompt := False;
  ADOConnection.ConnectionString := connStr;
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'Select * from [' + tablename + ']';
  ADOQuery.Open;
  
  try
     //
     // put your query processing here
     //
  finally
    ADOQuery.Close;
    ADOConnection.Close;

    ADOQuery.Free;
    ADOConnection.Free;
  end;
end

Open in new window

0
 
OrcbighterCommented:
connect using what?
0
 
cyberman05Author Commented:
ADO, Remote dataset, anything that works....
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
OrcbighterCommented:
From the example you gave in the sample link, the field cCONNSTR contains the connection string that will allow you to access the database.
I could not see its declaration in the example, but it is probably a string constant.
The best way to handle this is to have the string in a config file that the program reads at startup. In that way you can have a number of connection strings defined depending on your environment and can change them without changing any code.

The connection string will probably be of the form:

myStr := "Remote Server=http://<ip  address or server name>Remote  Provider=Microsoft.Jet.OLEDB.4.0;Data  Source=c:\inetpub\wwwroot\file1.mdb;"

For more possible syntaxes, depending on the driver you use Jet, OLE, ODBC, etc, have a look at  http://www.connectionstrings.com/access

cheers

0
 
cyberman05Author Commented:
ah.... database is on the remote server http://username.brinkster.net not local c:

Perhaps ISAPI solution anyone....
0
 
OrcbighterCommented:
The C drive is on the server
0
 
cyberman05Author Commented:
yes but I don't know where does brinkster have their path physically located if it's c, b or z.
0
 
senadCommented:
You must know database path in order to connect.
So just use ADO Connection - jet 4 Ole db provider -
and path to database.
0
 
senadCommented:
0
 
OrcbighterCommented:
Hi cyberman05
The simple truth is that if you are using MS Access ratrher than a more serious database product, such as SQL Server, then remote access is going to be tricky.

1. IIS must be running on the server
2. Probably some configuration for ADO must be set up on the server as well.
3. The connection string I gave you earlier becomes:

myStr := "Provider=MS Remote;Remote Server=http://<ip  address or server  name>;Data   Source=<TheRemoteDatabaseIWant>;Persist Security Info=False"
cheers

0
 
senadCommented:
Access is a very good database.
More 'serious' usually results in an unnecessary 'overkill'.
Mid sized company can run easily on Access.
0
 
cyberman05Author Commented:
As I said in the 1st post:

Database is located here:

http://username.brinkster.net/db/database.mdb

IIS is running, ASP is supported. So can I solve this by using ISAPI? Orcbighter please provide full code.
0
 
OrcbighterCommented:
Which version of Delphi are you using?
Are you using the client-server version of Delphi?
0
 
senadCommented:
Are you having trouble with this  :
'Open the Control Panel/Administrative Tools/Data Sources(ODBC). Now "Add" a User DSN,
during the setup of the User DSN is where you need to enter the Remote Server ( Name or IP
), the default database name that you want to connect to and the UserID/Password to be
used to test the connection.
Once you have this working you can go back into the Delphi IDE and setup a TADOConnection
or TDatabase ( BDE ). In the object inspector for the TADOConnection click on
ConnectionString and then the Build button. Now select the data access type of "Microsoft
OLE DB provider for Sql Server" then click Next. In the Connection dialog enter the Sql
server name ( or IP address ), login UserID/Password, and database name to connect to.
Click OK until you are back to the main edit window. Your Connection object should be
ready to use by connection it to some data aware component. All you have to do is set its
Connected property to True at runtime and it should connect and provide a means of
executing a query to the Sql server to retrieve data.
All of the "hard coded" parameters you entered during the initial setup of the ODBC User
DSN and the Delphi TADOConnection can be overriden at runtime ( and usually are ) so they
can be reconfigured to match the individual UserID/Password/Database requirements.'

???
0
 
cyberman05Author Commented:
Very good. I'll check it in 2 days. If it works points are yours.
0
 
cyberman05Author Commented:
Orcbighter we are getting closer I think. Find my code below. I get the error "Internet server error: Object/module not found". I think the problem is connection string in the code below.

This is what works in ASP:

Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/test.mdb") & ";" & "Persist Security Info=False"
oConn.Open(sConnection)

Can you please modify the connection string so that it will work?
 
procedure TForm1.GetRemoteData(tablename: string; connStr: string);
var
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;
  i:integer;
begin
  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.LoginPrompt := False;
  ADOConnection.ConnectionString := connStr;
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'Select * from [' + tablename + ']';
  ADOQuery.Open;
  
  try
     for i:=0 to adoquery.RecordCount-1 do
     begin
       adoquery.First;
       memo1.Lines.Add(adoquery.FieldByName('ime').AsString);
       memo1.Lines.Add(adoquery.FieldByName('priimek').AsString);
       adoquery.Next;
     end;
  finally
    ADOQuery.Close;
    ADOConnection.Close;

    ADOQuery.Free;
    ADOConnection.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var cnStr:string;
begin
  cnStr:='Provider=MS Remote;Remote Server=http://myusername.brinkster.net;Data Source=db/test.mdb;Persist Security Info=False';
  GetRemoteData('podatki',cnStr);
end;

Open in new window

0
 
cyberman05Author Commented:
This doesn't work either

  cnStr:='Provider=MS Remote;Remote Server=http://myusername.brinkster.net;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=http://myusername.brinkster.net/db/test.mdb;Persist Security Info=False';

I'll try to ask Brinkster directly.
0
 
cyberman05Author Commented:
Ok they told me the absolute path: c:\sites\content\f\r\i\myusername

So what now? It still doesn't work.
0
 
cyberman05Author Commented:
ok this is a bit better:

  cnStr:='Provider=MS Remote;Remote Server=65.182.100.185;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\sites\content\f\r\i\myusername\db\test.mdb;Persist Security Info=False';

no error, but the program freezes.
0
 
cyberman05Author Commented:
It freezes on

ADOQuery.SQL.Text := 'Select * from [' + tablename + ']';
0
 
OrcbighterCommented:
When you say it freezes, what was the actual SQL statement you assigned to the text field?
0
 
cyberman05Author Commented:
Like it is written in my code above:

ADOQuery.SQL.Text := 'Select * from [' + tablename + ']';
  ADOQuery.Open;

but it doesn't get to the open statement. Sql statement is select * from podatki where podatki is the tablename.

fetching records goes like this:

     for i:=0 to adoquery.RecordCount-1 do
     begin
       adoquery.First;
       memo1.Lines.Add(adoquery.FieldByName('ime').AsString);
       memo1.Lines.Add(adoquery.FieldByName('priimek').AsString);
       adoquery.Next;
     end;
0
 
OrcbighterCommented:
So your code freezes on the line
       ADOQuery.SQL.Text := 'Select * from podatki'; ?

try breaking the line up to see if you can isolate the problem a little better. For example:

    with ADOQuery do begin
             with SQL do begin
                       Clear;
                    Add('SELECT <field1>, <field2>, <field3>,...,<fieldn>');
                    Add('FROM Podatki');
            end;
 
            Open;
    end;

also, don't forget to test if ADOQuery is null.
0
 
cyberman05Author Commented:
It only has 2 fields, how much can I break this? I'm giving you the points anyway
0
 
cyberman05Author Commented:
The code freezes at the SQL statement but it could be an issue at ISP.
0
 
OrcbighterCommented:
Well, thanks for the points, but did you find the problem?
0
 
cyberman05Author Commented:
no. still not. if you want you can try. it's a simple 2 field access database at brinkster.net server. You already have Delphi code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.