Syntax error in From clause

[GraeagleBill]

I can't find the error.............hopefully someone else can spot it.

Two tables are involved, Addrlst and Salutations. The Addrlst table is to be found in an external DB named HornetsNet-Data.mdb located in the Addrlst-II folder on "C". The Salutations table is located in the current DB.

SELECT [RecordID], [LastName], [FirstName], [Salutations.Salutation], [SpouseName], [MaidenName], [Address], [CityState], [ZIP], [HomePhone], [CellPhone], [WorkPhone], [Company], [EmailHome], [EmailWork], [FaxPhone], [Notes],
[ListingTag], [ImageID], [Group], [Invite], [GAP], [Medical], [Business], [XMasCard]
FROM Addrlst IN 'C:\Addrlst-II\HornetsNet-Data.mdb' INNER JOIN Salutations ON Addrlst.SalID = Salutations.SalID


ORDER BY Addrlst.LastName, Addrlst.FirstName;

[June7]

I can manipulate table in another db using the IN parameter but never tried to do a join between remote and local tables this way.

Is this Access query object or constructed in VBA?

Try opening recordset for Addrlst without join to Salutations. If that works then the issue is with the join. Might not be possible in this structure.

Can you set a link to Addrlst?

[GraeagleBill]

Query works perfectly without the JOIN.

The arrangement here is one front-end mde and two back-end mdb's, one of which is rarely used and it's use is triggered by a option selection in a combo box. What would be a new trick for an old dog would be to dynamically re-Link to the version of the Addrlst table that I was otherwise trying do with a query. I'm used to setting up back-end links using the "Link Manager". Can you give me an example of linking to a table from VBA code? I can easily change the VBA event code associated with the combo.

[GraeagleBill]

If you don't have a ready example, I'll try to figure out a simple link refresh from what can be found online. Most of what I've found just look at the tabledef collection and refresh existing links.

[June7]

I have code but haven't run it in a long time. Why would you need to re-link tables? Once link is created, shouldn't it always be there?

Dim tdf As TableDef
'set table link
Set tdf = CurrentDb.CreateTableDef("tablename")
tdf.SourceTableName = "tablename"
tdf.Connect = "; DATABASE=path\databasename.mdb"
CurrentDb.TableDefs.Append tdf

[GraeagleBill]

I have two versions of the same table, "ADDRLST", that are stored in necessarily two separate back-end mdb's. The user has the option of switching between the two tables.

Since the JOIN option won't work when tables within the query are in different mdb files, I was turning to your suggestion that I accomplish the switch using a Link option. I'm going to work on that now (Saturday AM).

I might run into a problem with where and when the "re-linking" is done. As it is, the option to switch to a different version of ADDRLST is handled in the event code for a combo box that is located in the footing of a continuous form currently displaying the form's RecordSource, a query based on ADDRLST. I'll have to wait to see if Access gets excited when I attempt to re-link.

[GraeagleBill]

To test your code, I inserted the code in the open event for the form, the idea being to see if I could switch to a different back-end within the form's code module. Here's a screenshot of the error:



Is the question now how to "re-link" a table?

[June7]

I think you have to first delete the existing link then reset link to other table.

Bing: Access VBA delete table link
http://answers.microsoft.com/en-us/o...c-68b599b31bf5

[GraeagleBill]

I think that will work. With Albert issuing a warning about potential loss of a "non-linked" table, I'll follow Dirk's suggestion and verify the table of interest is in fact linked before deleteing. I try putting all of that together Sunday afternoon.
Bill

[GraeagleBill]

I'm closing this thread because 1) it is not possible to JOIN tables in a query where the tables ARE NOT contained in the same back-end mdb and 2) the follow-on approach following June7's URL suggestion above, where any issues found there would be for the "Programming" forum.