[AccessD] Remote Queries

For more information on Remote Queries, you can go to:
http://www.vb123.com/Toolshed/02_accvb/remotequeries.htm

What I've done is to create a function that defines the path to the BE db.
Each remote query uses that function.  So if the path to the BE changes, I
only need to change it in one place.  For multiple BE's you could set up
multiple functions.

Jim - The ADO connection appears, for my purposes, to be equivalent.
However this is an example I will keep!

Dan Waters
ProMation Systems

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Monday, November 08, 2004 2:52 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Remote Queries

Just a note:

There might be a better way to handle access to the remote database. It will
not solve the problem if the database location is changed but it will handle
the security issue.

See:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDB
ProviderForMicrosoftJet for ADO connection string

Set variables like:

Dim gstrConnection As String
Dim gconMYRemoteDB As ADODB.Command
Dim strSQL as String
Public rsMyRecordset As ADODB.Recordset

gstrConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\Databases\Process.mdb;" & _
                 "Jet OLEDB:System Database=MySystem.mdw", _
                 "myUsername", "myPassword"

Set gconMYRemoteDB = New ADODB.Command

With gconMYRemoteDB
 .ActiveConnection = gstrConnection
 .CommandText = "Select * from MyTable Where MyFirstField = 1"
 .CommandType = adCmdText
End With

...or... if you are using a query from the remote DB that has a parameter
you would use something like this...

With gconMYRemoteDB
 .ActiveConnection = gstrConnection
 .CommandText = "qryMyQueryName"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("NameofParameterToFill", adChar,
adParamInput, 10, strParameterValueToPass)
End With

'To fill a recordset
Set rsMyRecordset = New ADODB.Recordset

With rsMyRecordset
 .CursorLocation = adUseClient
 ' Note; dynamic and record locking. Mandatory for multi-user
 ' environments as MDBs are not as sophisticated as SQL servers
 ' and that implies code to manage record data changes or conflicts.
 .Open gconMYRemoteDB, , adOpenDynamic, adLockOptimistic
 If .BOF = False Or .EOF = False Then .MoveLast
End With

...or to simply execute a command...

strSQL = "INSERT INTO MyTable ( MyField1, MyField2 ) " & _
       "VALUES(" & Str(Variable1) & ", '" & Variable2 & "')"

gconMYRemoteDB.Execute strSQL

Set gconMYRemoteDB = Nothing

This way is ideal as you can have as many DBs open as you wish (within
system limits of course), all at once. This also manages the issues of
security. Hopefully that DBs do not wander around to frequently so that
their locations have to be continually managed. (Note that previous was done
from memory with limited reliability.)

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Monday, November 08, 2004 7:45 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Remote Queries


There are drawbacks.  I don't believe you can specificy a different .mdw or
username/password  with this type of statement.  Also, what if the Backend
is moved?  Instead of having to relink the tables, you'll have to change the
SQL in everything that uses this path.

Drew

-----Original Message-----
From: Dan Waters [mailto:dwaters at usinternet.com]
Sent: Sunday, November 07, 2004 7:41 AM
To: Database Advisors
Subject: [AccessD] Remote Queries


Has anyone had experience with what is called a Remote Query?



This is where you write the complete path to a table in the Select statement
for a recordset or a form or report recordsource.



An example:



"SELECT * FROM tblProcessMain" _

            " IN 'C:\Databases\Process.mdb'" _

            " WHERE ProcessStage <> 'Closed'"



Using a remote query means that a Front End doesn't need to link to a Back
End.  It also allows a Front End to be distributed without table links and
queries - thus hiding the location of the Back End.



This is new to me!  What might be the pros/cons/problems with using Remote
Queries?



TIA,

Dan Waters

ProMation Systems

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com