By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Performanceproblems with action Pass-Through-Query

P: 2
hallo developerfriends,

I have a urgent problem with transfering Data from a DB2-Server to Access97.
Tranfer runs by following steps:
1. creating pass-through-query
2. runing action-query based on pass-through (insert into)

Problem: for about 50.000 Datasets Access97 needs about 1min. To long for the users.
The responce time of the Pass-Through is 5 to 10 sec. Transfering Data about 50 sec.

Question: Is there somebody who can tell me how I can increase the datatransfer performance.

Thechnical Data:
System: Windows 2000 Pro
Access: 97
Net-Link: 100 MBit/s

My Code:

1. Generate Query
Sub PassThroughQuery_generieren(DB2_Tab As String)
Dim qry As QueryDef
Dim F_EA As String
Dim F_Kst As String


'Filter generieren
F_EA = FilterGenerieren("EA")
F_Kst = FilterGenerieren("OEKst")

'PassThrough-Abfrage (Tmp) generieren
strSQL = "SELECT * FROM DB2EP01D.EP01TLZ1" & _
" WHERE (EANR='34018' or EANR='43126' or EANR='88087')" & _
" and (KST='2718' or KST='5719' or KST='6704' or KST='6725' or KST='6727' or KST='6736')" & _
" FOR FETCH ONLY"

If QryExistJN("Tmp") Then DoCmd.DeleteObject acQuery, "Tmp"
Set qry = CurrentDb.CreateQueryDef("Tmp")

With qry
.Connect = "ODBC;DATABASE=jjj;UID=xxx;PWD=fff;" & _
"DSN=db2"
.SQL = strSQL
End With
End Sub

2. Import Data to Acces
Sub PassThoughQueryDaten_importieren(ZTab As String)

strSQL = "SELECT Tmp.* INTO " & ZTab & "" & _
" FROM Tmp;"
ExeSQL (strSQL)

End Sub

Greetings
riki
Jul 19 '06 #1
Share this Question
Share on Google+
1 Reply


P: 2
Sorry my Import Sub was the wrong one. I import by insert into

2. Import Data to Acces
Sub PassThoughQueryDaten_importieren(ZTab As String)

strSQL = "INSERT INTO " & ZTab & "" & _
" SELECT Tmp.*" & _
" FROM Tmp;"
ExeSQL (strSQL)

End Sub

Greetings
riki
Jul 19 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.