pathname for sql

  • Thread starter Thread starter alice
  • Start date Start date
A

alice

Hi,

I'm trying to write a program in Access that opens up a
tables from another access database file and then create
a query.

I have no problem doing this if the pathname contains no
spaces. However, the pathname is dynamic, and it might
contain spaces.
ie) X:\CROP Intermediate DB\Databases\CIDB_TEST3.mdb

Is there a way to work around it?

Thanks

alice
 
Let say Revenue is a table.

so how will it look like?
"X:\CROP Intermediate DB\Databases\CIDB_TEST3.mdb.Revenue"
or
"X:\CROP Intermediate DB\Databases\CIDB_TEST3.mdb".Revenue

??
thanks
 
I tried the quotes and still did not work.

Let me explain my situation more clearly.

I have 4 tables called Revenue, DispatchEnergy, UnitCosts
and TotalCapacity in the file X:\CROP Intermediate
DB\Databases\CIDB_TEST3.mdb (pathname containing spaces)

The following statement is written in another access
file, trying to retrieve those 4 tables and perform the
sql statement. The following statement will result in an
error because i did not include the pathname of where the
tables are located, which probably will have a runtime
error because the tables do not exist in the current
access file.

However, when i tried to add the pathname in there, it
will generate a syntax error at JOIN. I believe that it
has to do with the pathname having spaces in there,
because i tried with a pathname that doesn't contain
spaces, it works.

Basically, i would like to know how do i write the sql
statement so that it will work with a pathname that
contains spaces (I believe that spaces in sql means
separation between clauses)

----SQL STATEMENT-----
SELECT DispatchEnergy.UnitName, DispatchEnergy.WeekID, Avg
(DispatchEnergy.OnPeak) AS AvgOfOnPeak
FROM ((DispatchEnergy INNER JOIN Revenue ON
(DispatchEnergy.ScenarioID = Revenue.ScenarioID) AND
(DispatchEnergy.WeekID = Revenue.WeekID) AND
(DispatchEnergy.UnitName = Revenue.UnitName)) INNER JOIN
UnitCosts ON (Revenue.ScenarioID = UnitCosts.ScenarioID)
AND (Revenue.WeekID = UnitCosts.WeekID) AND
(Revenue.UnitName = UnitCosts.UnitName)) INNER JOIN
TotalCapacity ON (UnitCosts.ScenarioID =
TotalCapacity.ScenarioID) AND (UnitCosts.WeekID =
TotalCapacity.WeekID) AND (UnitCosts.UnitName =
TotalCapacity.UnitName)

Thanks~
 
The simplest approach would be to create linked tables pointing to the
CIDB_TEST3.mdb in your other database. Then you wouldn't have to worry about
any of this.

You can create linked tables dynamically using the TransferDatabase method.
 
Back
Top