Pass-through and table-make queries

[borg2002]

Hello,

Could somebody please tell me whether it's possible to create a query that uses pass through SQL to extract his data and saves it into a new table in the current database? I know you can do this by creating two different queries. But I would like to do it in just one so I don't have to use macro's....

Thanks in advance
Borg

 

[cmmrfrds]

What is the back end database (product name) that you are passing the query?

 

[borg2002]

An Oracle database....

 

[cmmrfrds]

I don't use Oracle, so I am not sure about the options on the Oracle side. SQL Server has at least a couple of ways through linked server or openrowset. Perhaps Oracle has something similiar, otherwise, the best option from Access is to link the Oracle table and do an append query. You could do it using a couple of recordsets but that involves a bunch of vba coding and would be less efficient.

 

[borg2002]

Ok, I'll keep looking then... If I find a solution, I'll post it here.
Thanks for your efforts.

Regards
Borg

 

[nabiS2K]

borg2002,

I don't understand why two queries results in the use of a macro. You need to create a SELECT pass-through query to pull the data from the source. Then create a make-table query using the pass-through query as it's source.

Is there a piece of information that I am misunderstanding? God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[borg2002]

That's exactly what I am doing. I use a SELECT pass-thru statement to extract the data I need. So far so good.... But now I want to save this data into a table on my PC, using a make-table query. That's the part I can't seem to find a solution for.

Regards
Borg

 

[nabiS2K]

Borg,

Could you copy and paste the SQL for your two queries? I want to be sure I understand your problem. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[borg2002]

SELECT
a.DELIVERY_ID ,
a.DELIVERY_ITEM_NO ,
a.SAP_SRCE_SYS_CODE ,
a.CLIENT_ID ,
a.ROW_INSERT_DATE ,
a.ROW_INSERT_USER_ID,
a.ROW_INSERT_TASK_ID,
a.ROW_UPDATE_DATE ,
a.ROW_UPDATE_USER_ID,
a.ROW_UPDATE_TASK_ID,
a.SUBJECT_AREA_RUN_ID,
a.ACTION_TYPE ,
a.PRODUCT_GRP_CODE ,
a.PRODUCT_HIERARCHY ,
a.BUSINESS_AREA_CODE ,
a.SALES_UOM ,
a.CREATED_BY ,
a.BASE_UOM ,
a.SALES_OFFICE_CODE ,
a.WEIGHT_UOM ,
a.VOLUME_UOM ,
a.NET_AMOUNT ,
a.DISTR_CHANNEL_CODE,
a.MATERIAL_ID ,
a.MATERIAL_SHIPPED_ID,
a.STORAGE_LOC_CODE ,
a.DIVISION_CODE ,
a.SHIPPED_QUANTITY ,
a.GROSS_WEIGHT ,
a.CREATE_DATE ,
a.SALES_GRP_CODE ,
a.NET_WEIGHT ,
a.PLANT_ID ,
a.CURRENCY_CODE ,
a.CREATE_TIME ,
a.VOLUME ,
a.GOODS_MOVEMENT_STATUS_CODE,
a.CHANGE_DATE ,
a.COST_CENTER_ID ,
a.ITEM_CATEGORY_CODE ,
a.ORIG_MATL_GRP_1_CODE ,
a.ORIG_MATL_GRP_2_CODE ,
a.ORIG_MATL_GRP_3_CODE ,
a.ORIG_MATL_GRP_4_CODE ,
a.ORIG_MATL_GRP_5_CODE ,
a.REF_DOC_CATEGORY_CODE ,
a.STORAGE_BIN ,
a.REF_DOC_ID ,
a.REF_ITEM_NO ,
a.LOADING_GRP_CODE ,
a.WAREHOUSE_CODE ,
a.STORAGE_TYPE_CODE ,
a.PRODUCT_GRP_UOM ,
a.ORIG_CUST_GRP_1_CODE,
a.ORIG_CUST_GRP_2_CODE,
a.ORIG_CUST_GRP_3_CODE,
a.BILLING_STATUS ,
a.ORIG_CUST_GRP_4_CODE ,
a.PROCESSING_STATUS ,
a.ORIG_CUST_GRP_5_CODE ,
a.CONTROLLING_AREA_ID ,
a.UNIVERSAL_PRODUCT_CODE ,
a.SHIPPED_QTY_IN_BASE_UNIT,
a.PICKING_STATUS ,
a.CUMULATIVE_BATCH_QTY ,
a.CUMULATIVE_NET_WEIGHT ,
a.CUMULATIVE_GROSS_WEIGHT,
a.CUMULATIVE_WEIGHT_UOM ,
a.HIGHER_LEVEL_BATCH_ITEM,
a.BATCH_SPLIT_ALLOWED ,
a.MULTI_BATCH_INDICATOR ,
a.LOT_NO ,
a.INTERCOMPANY_BILLING_STATUS ,
a.CUSTOMER_MATERIAL_ID ,
a.MOVEMENT_TYPE ,
b.CUSTOM_NUMBER_PREFIX,
b.CUSTOMER_NUMBER_SEQUENCE
FROM ADWINA.DELIVERY_ITEM a,
ADWINA.DOCUMENT_CUSTOMS_NUMBER_LOOKUP b
WHERE a.DELIVERY_ID = b.DELIVERY_ID
And a.DELIVERY_ITEM_NO = b.DELIVERY_ITEM_NO
And a.SAP_SRCE_SYS_CODE = b.SAP_SRCE_SYS_CODE
And a.CLIENT_ID = b.CLIENT_ID
And a.SAP_SRCE_SYS_CODE = 'XXXX'
And a.CLIENT_ID = '430'
This is the query to extract the data needed from the datawarehouse.
The results from this data should be inserted into a table on my local PC.

Regards
Borg

 

[nabiS2K]

I think you are missing how simple this is. You might be missing some small detail. You just need to save this query, and create another query as a make-table type with this pass-through query as the source.

Then, you should only need to execute the make-table query and it would pull the data from the pass-through query at that point.

God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[borg2002]

That's how I am doing it now. I was wondering if there was no way to bundle this into one query instead of creating two queries. I mean one SQL statement that extracts the data and saves it into a table. Because a make-table query in access takes quite some time...

Thanks
Borg

 

[nabiS2K]

Sorry. I must have misunderstood you. I was under the impression that you could not get it to work.

I do not think you will find a quicker method in Access. The only other option would be an append query if you setup the table manually. You could use a delete query then to clean it instead of deleting the table each time.

Hope you find what you are looking for. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[borg2002]

Ok Mike,

Thanks for your help.

Cheers
Borg