SELECT * FROM IN-problem

[Pampers]

Hi everyone,
I'm trying to execute (in vba-code) a query from another database then where the code is running. Can't get it to work. Error message is that it cannot find the object.

strQuery = "SELECT * FROM qryContainerMovesEDI " & _
"IN \\Svr-file01\data\Shared\AAASeaTrade\Seatrade_be.mdb"

Currentdb.Execute(strQuery)

Pampers
Keeping it simple can be complicated

 

[Pampers]

The CurrentDb.Execute(strQuery) is a bit nonsense here. I want to do a transferspreadsheet, like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQuery, strInputFileName, False

Pampers
Keeping it simple can be complicated

 

[Pampers]

Mmmmh,
Strange, if I use the query to create a recordset, there is no problem.... If I use it in the docmd.transferspreadsheet it causes an error.

Pampers
Keeping it simple can be complicated

 

[Pampers]

and I can use a table to refer to, and I can use the query in query-designer...but not the query in transferspreadsheet...



Pampers
Keeping it simple can be complicated

 

[Pampers]

I'm talking to myself here.

Table doesn;t work either.
If I use the query designer it works
if I use vba it doesn;t work
I'm puzzled, or just dumb

Pampers
Keeping it simple can be complicated

 

[MichaelRed]

I have also had some duficulties w / " In ... " statements. I have been able to overcome most / all of these to some degree:

Create a SELECT query actually IN the foregin db.

Test this works in the foregin db.

Copy the SQL to your 'Local' db.

Set a string equal to the sql string.

Add the insert int clause as the predesor to the sql string

Add the In Clause.

So far, so good.





MichaelRed

 

[PWise]

try

strQuery = "SELECT * FROM qryContainerMovesEDI " & _
"IN '\\Svr-file01\data\Shared\AAASeaTrade\Seatrade_be.mdb'"

 

[Pampers]

Hi guys, thanks a lot, I'm gonna give both a try... Keep you posted.

Pampers
Keeping it simple can be complicated