MakeTable query & split database

[Guest]

With a MakeTable query the resulting table is located on the Front End....

Is there a method to have it on BackEnd?

 

[Marshall Barton]

With a MakeTable query the resulting table is located on the Front End....

Is there a method to have it on BackEnd?

Use the IN phrase in the SELECT INTO clause.

SELECT INTO table IN "path to backend mdb"

 

[John W. Vinson]

With a MakeTable query the resulting table is located on the Front End....

If you're routinely doing MakeTable queries you really should
investigate whether they are needed! Why do these tables need to be
created? If they're temporary tables (created as intermediates for
really complex queries, or for DOCUMENTED performance reasons) I'd
suggest putting them - not in *either* the frontend or backend - but
in a newly-created temporary .mdb file just for that purpose.

John W. Vinson [MVP]

 

[Guest]

That is an interesting concept, John. I will have to consider that; however,
IMHO, make table queries are inventions of the devil. I prefer to create a
table defined as I need it and empty it before using it.

 

[Guest]

much thanks both;

the IN method raises question in my mind on the relative path depending on
location of each FE - - although I believe if one has UNC correct it is the
same for all....although I struggled with this once before for coding an
import and could never seem to get the path to work but that job went away
before I had the real need to resolve so was left with uncertainty overall -
- will revisit....

yes; definitely it does raise the question of why?...there is a chain of
queries and it makes troubleshooting/design alot easier having a table made
that is static (for lack of a better explanation) - and I have some doubt on
my method here...but taking it to an entirely separate mdb seems even further
complicated - and don't quite follow the logic on this approach...

this is an issue I don't find discussed in reference books and would welcome
general advice....thanks in advance...

 

[Guest]

Using UNC paths will resolve the issue for the front end, but if the back end
is moved and you relink, you code is broken.

 

[Marshall Barton]

much thanks both;

the IN method raises question in my mind on the relative path depending on
location of each FE - - although I believe if one has UNC correct it is the
same for all....although I struggled with this once before for coding an
import and could never seem to get the path to work but that job went away
before I had the real need to resolve so was left with uncertainty overall -
- will revisit....

yes; definitely it does raise the question of why?...there is a chain of
queries and it makes troubleshooting/design alot easier having a table made
that is static (for lack of a better explanation) - and I have some doubt on
my method here...but taking it to an entirely separate mdb seems even further
complicated - and don't quite follow the logic on this approach...

this is an issue I don't find discussed in reference books and would welcome
general advice....thanks in advance...

You can get the path of the front end by using:
CurrentProject.Path
then concatenate the name of the temporary mdb file to that.

I strongly recommend using a temporary mdb file to hold the
temporary tables. Compact/Repair is not an infalible
process and, because it's such a heavy duty operation, it
might fail catestrophically.

Creating a temporary mdb is simple:
Set tmpDb = CreateDatabase(path\file)

How you create the table can be a little more complicated,
so I usually precreate a mdb file with an empty table aready
in it. Then, when I want to use it, just FileCopy the
precreated mdb. The frontend can even have precreated
linked tables to the temp table.