Database that is independent of path

[Boon]

Hi,

Here is my situation.

I have 3 database files stored in a folder. There are queries that make
table, append between database. (ie. Click make table in database1 to make a
table in database2.)

Currently, these 3 databases are stored in my laptop at path -->
c:\documents\project\Customer\

I would like to be able to move the folder Customer (which contains 3
databases) to other place and still be able to do the work. For instance, I
want to move the folder Customer to D:\Backup\August\Customer. Ultimately, I
want to move this to other PCs as well.

The problem is in the make query, I specify the path as
c:\documents\project\Customer\. Thus, when I move to different path, the
query doesn't work.

How can I make the query independent of the path. Just look in the databases
in the same folder it resides, or in the subfolder in the same folder in
resides?


Thank you,
Boon

 

[Hans Up]

I would like to be able to move the folder Customer (which contains 3
databases) to other place and still be able to do the work. For instance, I
want to move the folder Customer to D:\Backup\August\Customer. Ultimately, I
want to move this to other PCs as well.

The problem is in the make query, I specify the path as
c:\documents\project\Customer\. Thus, when I move to different path, the
query doesn't work.

How can I make the query independent of the path. Just look in the databases
in the same folder it resides, or in the subfolder in the same folder in
resides?

I wonder if the CurrentProject.Path property would be useful to you. It
might help to show us the SQL View of your make table queries. Can you
make something like this work?:

CurrentProject.Path & "\" & "YourDatabaseFileName"

 

[Boon]

The current project.path did not work.

Here is a query

SELECT "CUSTOMER" AS Expr1 INTO NewTable IN
'D:\Data\wg992\Desktop\Database1.mdb';

Thanks,

 

[John Spencer]

You seem to have not identified the table you need to use in the database.

SELECT [CUSTOMER] INTO NewTable
FROM CustomerTable IN
'D:\Data\wg992\Desktop\Database1.mdb';

I rarely use that syntax so it might be wrong.

And if you are going to change the location of the database, you will need to
build the SQL statement on the fly with some VBA. There is (as far as I know)
no method to build a relative (changing) path into a query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

You seem to have not identified the table you need to use in the database.

SELECT [CUSTOMER] INTO NewTable
FROM CustomerTable IN
'D:\Data\wg992\Desktop\Database1.mdb';

I rarely use that syntax so it might be wrong.

And if you are going to change the location of the database, you will
need to build the SQL statement on the fly with some VBA. There is (as
far as I know) no method to build a relative (changing) path into a query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I rarely use that syntax also. I think that I would write:

SELECT [CUSTOMER] INTO NewTable
FROM CustomerTable IN
'D:\Data\wg992\Desktop\Database1.mdb';

as

SELECT [CUSTOMER] INTO NewTable
FROM [D:\Data\wg992\Desktop\Database1.mdb].CustomerTable;

IIRC, the IN 'D:\Data\wg992\Desktop\Database1.mdb' part doesn't deal
well with the situation where a table called CurstomerTable also exists
in the current database and a JOIN is required. The syntax in the
second example adds flexibility (such as specifying an IMEX value
separately) and doesn't suffer from any of the limitations of the IN
keyword. I have been using such queries more often of late, but note
that when queries containing such syntax are embedded within code, a
common practice of testing software in a safe environment by linking
tables to a test backend or by importing test tables with the same name
into the database can still result in the danger of live tables being
changed during the test. I first saw the second syntax in this NG in a
post by Jamie Collins.

James A. Fortune
(e-mail address removed)

Disclaimer: Any programming examples shown are for illustration
purposes only, without warranty either expressed or implied. This
includes, but is not limited to, the implied warranties of
merchantability or fitness for a particular purpose. This post assumes
that you are familiar with the programming language that is being
demonstrated and with the tools that are used to create and to debug
procedures. I might explain the functionality of a particular procedure,
but I am under no obligation to modify these examples to provide added
functionality or to construct procedures to meet your specific
requirements. Any code samples posted contain no known hidden material
defects. However, anyone who uses any code sample posted does so with
the understanding that they are responsible for any testing of any
illustrative code sample for any particular use. Furthermore, anyone
using an illustrative code sample I provide or code derived from it does
so at their own risk.

 

[Hans Up]

The current project.path did not work.

Here is a query

SELECT "CUSTOMER" AS Expr1 INTO NewTable IN
'D:\Data\wg992\Desktop\Database1.mdb';

You can use CurrentProject.Path to build the SQL statement on the fly as
John suggested.

But if your working database and the destination database are both in
the same (Customer) folder, you can use just the destination database
name (without the path) following IN. For example, this query found my
Database1.mdb file, which is in the same folder as the database I had
open, and created tblFoo2 in it:

SELECT f.some_text, f.more_text INTO tblFoo2 IN 'Database1.mdb'
FROM tblFoo AS f;