SQL code to create table of external data

[mcunkelman]

I used the Microsoft Query Wizard in Excel to create a query of an external SQL database. I then modified the SQL code to get everything working(rename columns, applied unit conversion factors and set relationships between the 7 separate tables I was pulling from), is it possible to reuse that SQL code from excel(Query Wizard) in Access to recreate the same table from the external database?

[ItsMe]

Maybe you can post the SQL statement here. I understand you edited some Excel tool generated code. Beyond that, I am having difficulty understanding what the code is supposed to accomplish.

[mcunkelman]

SELECT table1.column1 AS 'Column_1’, table2data.column2/.3048 AS 'Column_2', table2.column3/.3048 AS 'Column_3'
FROM database1.dbo.table2 table2, database1.dbo.table2data table2data, database1.dbo.table1 table1
WHERE table2.idrec = table2data.idrecparent AND table2data.idcol = table1.idcol


This is a real simple one that I made in excel that pulls data from an external source and displays a table in excel that I can sort and filter. I want to know how to recreate and modify this table in access. I know it is possible to manually add and link all the tables in access, but like I said this is a simple version, there are others querys that I would like to transfer over also that are more complex. Basically I want to know if there is a way to take the sql code I already have from excel(what I have pasted above is an example) and copy and paste it into access to just have the tables in access that I already have in excel without having to rebuild them from scratch.

[ItsMe]

If your Access file has linked tables, that SQL should work. However, I have not tried using the fully qualified name of a table. Rather, I would use table2 or table1. Your SQL statement is using both, eg database1.dbo.table2

Alternatively, if you want to use your SQL as a pass-through, you would need to include a connection string with your saved query object. You can do this from within the Query Designer and selecting Pass Through. The connection string would be assigned to the Query Object's properties.