Create Table in Linked Database

[BenWeber]

I have a separate database for my data and my application that i have linked
when the program starts up. I've been doing this so that people can have new
versions of the application without having to delete all of their entered
data.

I am making a new release of the application that requires some alterations
to the database. Altering the tables in the other database shouldn't be a big
deal, however i also need to create a table there...

Is there a way to do a create table statement in the application database
that will put the table in the linked database as opposed to the application
database? After it's created i will have no problem linking it, my problem is
the create table statement automatically puts it in the database the sql is
being run from (the application database).

Thanks,
-Ben

 

[Graham Mandeno]

Hi Ben

If you execute SQL against CurrentDb then that is where it will happen.

You need to open the linked DB as a separate database object and execute the
SQL statement in that context.

Something like this:

Dim dbBackEnd as DAO.Database
Set dbBackEnd = DBEngine(0).OpenDatabase( strBackEndPath )
dbBackEnd.Execute strSQL, dbFailOnError
dbBackEnd.Close

 

[david epsom dot com dot au]

Using a make table query from a table in CurrentDB
into a new table in MyData.mdb,

(air code)

SELECT myTemplateTable.* INTO
[MS Access;DATABASE=C:\MyData.mdb;].MyNewTable
FROM myTemplateTable;

or I would have said

SELECT myTemplateTable.* INTO
[C:\MyData.mdb].MyNewTable
FROM myTemplateTable;



(david)

If you execute SQL against CurrentDb then that is where it will happen.

Something like this:

Dim dbBackEnd as DAO.Database <<snip>>

Alternatively, something more like this:

CREATE TABLE
[MS Access;DATABASE=C:\MyApplicationDB.mdb;].MyNewTable (
data_col INTEGER NOT NULL
UNIQUE);

Jamie.

 

[david epsom dot com dot au]

The OP *twice* mentioned 'create table statement' so I figured


It occurred to me the that the OP may have known
neither want they wanted nor what they were asking
for. I attached to your response because

1) It was an extension of your response, not Grahams,

and

2) My posting is not a complete answer, only an extension
or your answer.

and

3) Anybody who has to ask how to execute a command
against a database probably is not ready for DDL.

I agree that a Make Table query is not a complete
answer to creating a table in a database. On the
other hand, it is a compact method of doing something
that would require a lot of DDL for a large table,
and conveniently includes data transfer as well.

If you want to transfer data from your template into
your target table, a convenient way to source the data
is to put a template table into your source database.
(Yes, you can use massive SQL insert commands, but
that is not a sensible approach if you have an application
database). If you have a source table for your data,
it may be convenient to also use the source table as
a template for your target table, avoiding a lot of
error prone and unfamiliar DDL.

It may well be that "making a new release of the application"
imposes certain requirements, but I wouldn't like to
make any assumptions about what those would be, so I
think that it's a good thing that three different solutions
have been provided.

(david)

Using a make table query from a table in CurrentDB
into a new table in MyData.mdb,

(air code)

SELECT myTemplateTable.* INTO
[MS Access;DATABASE=C:\MyData.mdb;].MyNewTable
FROM myTemplateTable;

or I would have said

SELECT myTemplateTable.* INTO
[C:\MyData.mdb].MyNewTable
FROM myTemplateTable;

The OP *twice* mentioned 'create table statement' so I figured they may
have wanted a SQL CREATE TABLE.

Jet's SELECT..INTO..FROM syntax does not cause the source table's
constraints etc to be retained in the new table, which I though would
be required when the OP is "making a new release of the application".

Jamie.