Defining the 'Source Database' Query Property

[Byzantine]

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

 

[David Lloyd]

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")

--
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

 

[Marshall Barton]

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?

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.

 

[David Lloyd]

The filename needs to be enclosed in quotes so the last line should read:

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

--
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")

--
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

 

[Byzantine]

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
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?

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.

 

[Marshall Barton]

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"

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.