Alias question for a table in an external db

[teach314]

hi - what is the proper syntax for using a Table Alias ( AS T1 ) in this FROM statement?

SELECT...
FROM ExternalDatabaseTableName IN 'drive:\Path\dbnametest.mdb'
WHERE...

 

[jedraw]

Did you try??
SELECT...
FROM ExternalDatabaseTableName AS T1 IN 'drive:\Path\dbnametest.mdb'
WHERE...

If so,did you get an error?

 

[PHV]

I'd try this:
FROM ExternalDatabaseTableName IN 'drive:\Path\dbnametest.mdb' AS T1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[teach314]

thanks jedraw and PHV. I kept getting a syntax error that I'd assumed was an Alias problem. I now realize that the problem was in the JOIN.
The code below shows a 'fleshed out; version of my code. The error message is now "Syntax Error in Join". Does this mean that I can't JOIN a table in an external db with a table in my Current db? I seem to be missing something here.

SELECT 
	'X12' AS Model, J.ID, J.SR, J.SR_Next, J.SL, J.SL_Next 
FROM 
	(
	tbl_TEST_External IN 'D:\ARCHIVE_X12\SOURCE_Data.mdb' AS J    
   LEFT JOIN 
	tRef__S_SNext AS DR        
   ON 
	(J.SR = DR.S) AND (J.SR_Next = DR.SNext)
	)    
   LEFT JOIN 
        tRef__S_SNext AS DL        
   ON 
        (J.SL = DL.S) AND (J.SL_Next = DL.SNext) 
ORDER BY J.ID, J.Ranking;

thanks in advance for any help

 

[teach314]

as further insight into the question I just asked above, I get the error message "Syntax Error in Join" when I try to create the QueryDef using...
Set MyQueryDef1 = db.CreateQueryDef(strQryOutput1, strSQL)

I don't even get to the point of actually running the query.

 

[teach314]

hi - I kept trying different combinations of the FROM statement, and got lucky! The following syntax allows me to JOIN an EXTERNAL TABLE to a LOCAL TABLE, and it works perfectly.

SELECT 
	'X12' AS Model, J.ID, J.SR, J.SR_Next, J.SL, J.SL_Next 
FROM 
	(
	[D:\ARCHIVE_X12\SOURCE_Data.mdb].tbl_TEST_External  AS J    
   LEFT JOIN 
	tRef__S_SNext AS DR        
   ON 
	(J.SR = DR.S) AND (J.SR_Next = DR.SNext)
	)    
   LEFT JOIN 
        tRef__S_SNext AS DL        
   ON 
        (J.SL = DL.S) AND (J.SL_Next = DL.SNext) 
ORDER BY J.ID, J.Ranking;

By the way, my VBA is being run by master VBScript code. One thing that has been giving me grief is that when I step through VBScript code which calls up a VBA function in Access, I'd like to continue stepping through the VBA. I can set a Breakpoint in the VBA, but when the VBScript opens the Access application, the Breakpoint is not there. Any thoughts?