Importing table using a query ?????

[saxonomy]

Hello

I have 4 databases, in the same directory. If I want to work in only one of them, is there a way to create a query to import a table, that's common amongst the 4 dbs? I'd like to import the tables, and obviously rename them.

 

[AlexCuse]

Yes, or you can link tables, or use the import wizard.

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[saxonomy]

Lol,

My question was how do I setup a query in the db I'm working out of, to perform the imports/rename for me?

Thanks

 

[AlexCuse]

It works like this:

SELECT RemoteTable.* INTO NewTable
FROM RemoteTable IN 'X:\Remote\Directory\SomeDB.mdb'

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[saxonomy]

Great!

One more thing....how do I automatically put in the password for the db of the imported table?

It's protected.

 

[saxonomy]

Also,

Can I combine these commands e.g.

SELECT [Accountability Layout].* INTO DACAccLay
FROM [Accountability Layout] IN 'DAC.mdb';

SELECT [Accountability Layout].* INTO OHAccLay
FROM [Accountability Layout] IN 'OH.mdb';

SELECT [Accountability Layout].* INTO NDACAccLay
FROM [Accountability Layout] IN 'NDAC.mdb';

SELECT [Accountability Maintenance Layout].* INTO MainAccLay
FROM [Accountability Maintenance Layout] IN 'Main.mdb';


I tried but to no avail. Sry, not the best at these!

Thanks

 

[AlexCuse]

I am not certain of this, but I think you can probably add password and username somewhere in here:

'X:\Remote\Directory\SomeDB.mdb'

If you are concerned about needing to enter a password though, you really should just use linked tables.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[saxonomy]

Ok, now I have successfully linked the four tables as well as the union query to combine them.

Now, I would like to add a field to the end of all tables titled "Bucket" where it'll specify what bucket (DAC, Main, NDAC, or OH) each line entry of the combined query belongs to.

I would like it to read something like ...

Field1 Field2 .................Bucket
XXX YYY NDAC
XXY YYZ OH

Thanks

 

[AlexCuse]

As long asthe 'bucket' and table names are the same thing, I think this is what you want,I am not sure really how you are defining bucket:

select field1, field2, 'NDAC' as Bucket from NDAC
union all select field1, field2, 'OH' as Bucket from OH
etc...

Is this the sort of thing you are after?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom