Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Queries / November 2005

Tip: Looking for answers? Try searching our database.

Defining the 'Source Database' Query Property

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Byzantine - 22 Nov 2005 15:02 GMT
Hallo there

I'm trying to run a select query which looks at data in another MS Access
2003 database using the 'Source Database' query property to specify the file
name and path.

If I simply type in the following path and file name of the source database
the query works fine:

C:\Program Files\My_Database.mdb

However when I try to build the file name and path using a function MS
Access returns an error stating that the file name is not recognised. The
file name and path string is stored in a field called 'FileNameAndPath' in a
table called 'tbl Configuration' within the remote database. I am trying to
enter the following function in to the 'Source Database' query property and
it doesn't work:

DFirst("[FileNameAndPath]","tbl Configuration")

Has anyone got any ideas why this doesn't work? Can this be done?

If not, is there any other way of building the source database file name and
path, other than using the linked tables facility?

Any help is much appreciated.

Regards
Reply to this Message
David Lloyd - 23 Nov 2005 03:15 GMT
I am not sure of all the specifics of what you are trying to accomplish, so
I will just make some general comments.  Is building the query dynamically a
possibility?  If so, have you considered using the IN SQL clause to specify
the remote database source?  The IN clause is the SQL equivalent of
specifying the Source Database property.  For example:

Dim sSQL as String

sSQL = "SELECT * FROM MyRemoteTable"

sSQL = sSQL & " IN " & DFirst("[FileNameAndPath]","tbl Configuration")

Signature

David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

Hallo there

I'm trying to run a select query which looks at data in another MS Access
2003 database using the 'Source Database' query property to specify the file
name and path.

If I simply type in the following path and file name of the source database
the query works fine:

C:\Program Files\My_Database.mdb

However when I try to build the file name and path using a function MS
Access returns an error stating that the file name is not recognised. The
file name and path string is stored in a field called 'FileNameAndPath' in a
table called 'tbl Configuration' within the remote database. I am trying to
enter the following function in to the 'Source Database' query property and
it doesn't work:

DFirst("[FileNameAndPath]","tbl Configuration")

Has anyone got any ideas why this doesn't work? Can this be done?

If not, is there any other way of building the source database file name and
path, other than using the linked tables facility?

Any help is much appreciated.

Regards
Reply to this Message
David Lloyd - 23 Nov 2005 03:59 GMT
The filename needs to be enclosed in quotes so the last line should read:

sSQL = sSQL & " IN '" & DFirst("[FileNameAndPath]","tbl Configuration") &
"'"

Signature

David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

I am not sure of all the specifics of what you are trying to accomplish, so
I will just make some general comments.  Is building the query dynamically a
possibility?  If so, have you considered using the IN SQL clause to specify
the remote database source?  The IN clause is the SQL equivalent of
specifying the Source Database property.  For example:

Dim sSQL as String

sSQL = "SELECT * FROM MyRemoteTable"

sSQL = sSQL & " IN " & DFirst("[FileNameAndPath]","tbl Configuration")

Signature

David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

Hallo there

I'm trying to run a select query which looks at data in another MS Access
2003 database using the 'Source Database' query property to specify the file
name and path.

If I simply type in the following path and file name of the source database
the query works fine:

C:\Program Files\My_Database.mdb

However when I try to build the file name and path using a function MS
Access returns an error stating that the file name is not recognised. The
file name and path string is stored in a field called 'FileNameAndPath' in a
table called 'tbl Configuration' within the remote database. I am trying to
enter the following function in to the 'Source Database' query property and
it doesn't work:

DFirst("[FileNameAndPath]","tbl Configuration")

Has anyone got any ideas why this doesn't work? Can this be done?

If not, is there any other way of building the source database file name and
path, other than using the linked tables facility?

Any help is much appreciated.

Regards
Reply to this Message
Marshall Barton - 23 Nov 2005 03:26 GMT
>I'm trying to run a select query which looks at data in another MS Access
>2003 database using the 'Source Database' query property to specify the file
[quoted text clipped - 15 lines]
>
>Has anyone got any ideas why this doesn't work? Can this be done?

No, that can not be done.  The query design window is just a
cute user interface that allows you to specify various parts
of a query.  What they call the query's properties are
really just more pieces of your specification of what you
want the query t do.

Behind the scenes, Access uses all that information to
construct (when the query is saved) the SQL statement to do
the work.  You can see the result by just switching to SQL
view.

To do what you want, you have to construct the SQL statement
using a VBA procedure.  This is a simple sequence of
instructions that concatenates the parts of the SQL
statement as seen in SQL view.

You never said what you want to do with the query so I don't
know where to go from here.

Signature

Marsh
MVP [MS Access]

Reply to this Message
Byzantine - 23 Nov 2005 19:38 GMT
Marshall, David, thankyou for this. I now have the following code, which does
not work:

Dim SQL As String

SQL = "SELECT [tbl Companies].* " & _
         "FROM [tbl Companies]" & " IN '" &  DFirst("[FileNameAndPath]",
"tbl Configuration") & "'" &_
         "WHERE ((([tbl Companies].CompanyID) = [Forms]![frm Create Data
Sets for Customers]![CompanyID]))" & _
         "WITH OWNERACCESS OPTION;"
   
DoCmd.RunSQL SQL

I get the following error:

"Runtime error 2342
A RunSQL action requires an argument consisting of an SQL Statement"

What have I got wrong please.

Many thanks

> >I'm trying to run a select query which looks at data in another MS Access
> >2003 database using the 'Source Database' query property to specify the file
[quoted text clipped - 34 lines]
> You never said what you want to do with the query so I don't
> know where to go from here.
Reply to this Message
Marshall Barton - 23 Nov 2005 22:41 GMT
>Marshall, David, thankyou for this. I now have the following code, which does
>not work:
[quoted text clipped - 14 lines]
>"Runtime error 2342
>A RunSQL action requires an argument consisting of an SQL Statement"

The easiest way to debug the construction of an SQL
statement is to add a Debug.Print  SQL right before the
RunSQL statement.  You can the see the real query in the
debug/immediate window.  If that doesn't ckarify the
problem, then Copy the displayed final SQL statement and
Paste it into the SQL view of a new query.  Try to run it to
get any query analyzer error messages.

In this case, I think your transcription of the error
message is not 100% accurate.  It should say something about
the query is required to be an "Action" query.  If you only
want to take a quick look at the query's data, you can use
OpenQuery, or, for more routine operations, create a form
with the query as its record source.  To manipulate the
query's data in a VBA procedure, open a recordset.

Signature

Marsh
MVP [MS Access]

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.