Create Table in External DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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)
 
I like that much better than my convoluted method. I learn something new
every day.

Thanks.
 
david said:
You get sql like this:

SELECT INTO fred IN 'freddd.mdb';
Click to expand...

Perhaps more like this

SELECT *
INTO [MS Access;Database=C:\db99.mdb;].Orders
FROM Orders;
 
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';
Click to expand...

Perhaps more like this

SELECT *
INTO [MS Access;Database=C:\db99.mdb;].Orders
FROM Orders;
Click to expand...
 
david said:
INTO [MS Access;Database=C:\db99.mdb;].Orders
Click to expand...

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

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.
Click to expand...

I use 'MS Access' simply to fully qualify the data source as, erm, Jet
:)
 
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
Click to expand...

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

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.
Click to expand...

I use 'MS Access' simply to fully qualify the data source as, erm, Jet
:)
Click to expand...
 
david said:
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",
Click to expand...

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.
 
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
Click to expand...

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)
 
david said:
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.
Click to expand...

I have my confirmation! Many thanks.
 
Back
Top