Create Table in External DB

[kremesch]

I understand how to use a query to create a table.
What I'd like to know is can I use a Create Table query to create a table in
another database other than the one I'm in?

What would be the corrent layout of the context if this is possible?

 

[Brian]

There may be a way, but I would just write the make-table query, run it from
VBA using DoCmd.OpenQuery, then use the CopyObject method to copy it to the
new database:

Private Sub Button1_Click()
Dim NewFile as String
Dim tblName as String
NewFile = "C:\Path\AccessDB2.mdb"
tblName = "NewTable"
DoCmd.OpenQuery "CreateNewTable"
DoCmd.CopyObject NewFile, tblName, acTable, tblName
End Sub

This copies table tblName from the current db to NewFile as tlbName.

 

[david epsom dot com dot au]

In design view, drop down the Query Menu, and select
Make Table query. On the form, select the radio
button for 'another database'.

You get sql like this:

SELECT INTO fred IN 'freddd.mdb';

(david)

 

[Brian]

I like that much better than my convoluted method. I learn something new
every day.

Thanks.

 

[peregenem]

You get sql like this:

SELECT INTO fred IN 'freddd.mdb';

Perhaps more like this

SELECT *
INTO [MS Access;Database=C:\db99.mdb;].Orders
FROM Orders;

 

[david epsom dot com dot au]

INTO [MS Access;Database=C:\db99.mdb;].Orders

If you have your database set to 'ANSI' mode,
you get may get SQL like this from the QBE window.

You also need the 'MS Access' clause if you are
using an ADO connection instead of CurrentDB.

In either case '[database].

' may be used
instead of '

IN [database]'


(david)

You get sql like this:

SELECT INTO fred IN 'freddd.mdb';

Perhaps more like this

SELECT *
INTO [MS Access;Database=C:\db99.mdb;].Orders
FROM Orders;

 

[peregenem]

INTO [MS Access;Database=C:\db99.mdb;].Orders

If you have your database set to 'ANSI' mode,
you get may get SQL like this from the QBE window.

Perhaps, but the syntax I posted is from at least the Jet 3 era i.e.
not limited to Jet 4 which 'ANSI' mode implies.

You also need the 'MS Access' clause if you are
using an ADO connection instead of CurrentDB.

I use 'MS Access' simply to fully qualify the data source as, erm, Jet

 

[david epsom dot com dot au]

Perhaps, but the syntax I posted is from at least the Jet 3

That is interesting: I have never seen "MS Access;"

If you look at the linked tables, you will see that
the normal connect string is just ";database=c:\db99.mdb",
but that doesn't work in queries.

The SQL I posted is what I get when I drop down the
Query Menu, and select Make Table query.

I was wrong to suggest that your SQL was 'ANSI' SQL.
I don't know what the syntax would be for that, but
I suspect something like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\db.xls; Extended
Properties='Excel 8.0;HDR=Yes;IMEX=1'

(david)

INTO [MS Access;Database=C:\db99.mdb;].Orders

If you have your database set to 'ANSI' mode,
you get may get SQL like this from the QBE window.

Perhaps, but the syntax I posted is from at least the Jet 3 era i.e.
not limited to Jet 4 which 'ANSI' mode implies.

You also need the 'MS Access' clause if you are
using an ADO connection instead of CurrentDB.

I use 'MS Access' simply to fully qualify the data source as, erm, Jet

 

[peregenem]

That is interesting: I have never seen "MS Access;"

If you look at the linked tables, you will see that
the normal connect string is just ";database=c:\db99.mdb",

I not exactly sure what this syntax _is_. I've found very little
documentation on MSDN. I'd like to know how to specify the .mdw path to
use or, more likely, confirmation that it isn't possible to do so.

 

[david epsom dot com dot au]

documentation on MSDN. I'd like to know how to specify the .mdw

use or, more likely, confirmation that it isn't possible to do

sadly correct.

Unless you use a passthrough query, you use the dbengine
which is opening the query, and a dbengine may only use
one .mdw.

And passthrough querys can't connect to jet databases.

The only way to do this would be to use a second platform
(for example, SQL Server) with a passthrough query on
it as well, to open a second dbEngine object, with a
second mdw.


(david)

 

[peregenem]

sadly correct.

Unless you use a passthrough query, you use the dbengine
which is opening the query, and a dbengine may only use
one .mdw.

And passthrough querys can't connect to jet databases.

I have my confirmation! Many thanks.